Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry. Saviynt Copilot Icon
This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Analytics for AEST and AEDT

ARNAB86
Regular Contributor
Regular Contributor

‎07/31/2024 09:09 PM

Hi Team,

I have the following query which i will calculate AEST and AEDT time based on that run the disabled account task. As we know every year the AEST and AEDT time is different.I have calculate this for this year how can i configure for next 3 years

Here is my sql code

SELECT DISTINCT
a.accountkey AS acctKey,
a.endpointkey,
'disableAccount' AS Default_Action_For_Analytics
FROM
accounts a
JOIN
user_accounts ua ON a.accountkey = ua.accountkey
JOIN
users u ON ua.userkey = u.userkey
WHERE
u.statuskey IN (1)
AND a.endpointkey = 3
AND (
DATEDIFF(
STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s'),
CONVERT_TZ(
current_timestamp,
'+00:00',
IF(
current_timestamp BETWEEN
STR_TO_DATE(CONCAT(YEAR(current_timestamp), '-04-01'), '%Y-%m-%d') AND
STR_TO_DATE(CONCAT(YEAR(current_timestamp), '-09-30'), '%Y-%m-%d'),
'+10:00',
'+11:00'
)
)
) = 0
)
AND u.customproperty53 IS NOT NULL
AND u.customproperty54 IS NOT NULL
AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= DATE_FORMAT(
CONVERT_TZ(
current_timestamp,
'+00:00',
IF(
current_timestamp BETWEEN
STR_TO_DATE(CONCAT(YEAR(current_timestamp), '-04-01'), '%Y-%m-%d') AND
STR_TO_DATE(CONCAT(YEAR(current_timestamp), '-09-30'), '%Y-%m-%d'),
'+10:00',
'+11:00'
)
),
'%Y-%m-%d %H:%i:%s'
)
AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(
CONVERT_TZ(
current_timestamp,
'+00:00',
IF(
current_timestamp BETWEEN
STR_TO_DATE(CONCAT(YEAR(current_timestamp), '-04-01'), '%Y-%m-%d') AND
STR_TO_DATE(CONCAT(YEAR(current_timestamp), '-09-30'), '%Y-%m-%d'),
'+10:00',
'+11:00'
)
),
'%Y-%m-%d %H:%i:%s'
)
AND a.status IN (1, 'Manually Provisioned', 'Active');

 

Thanks

Arnab Pal

Labels:
0 Kudos
5 REPLIES 5

rushikeshvartak
All-Star
All-Star

‎07/31/2024 09:32 PM 

SELECT DISTINCT
    a.accountkey AS acctKey,
    a.endpointkey,
    'disableAccount' AS Default_Action_For_Analytics
FROM
    accounts a
JOIN
    user_accounts ua ON a.accountkey = ua.accountkey
JOIN
    users u ON ua.userkey = u.userkey
WHERE
    u.statuskey IN (1)
    AND a.endpointkey = 3
    AND (
        DATEDIFF(
            STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s'),
            IF(
                CURRENT_TIMESTAMP BETWEEN '2024-04-01 00:00:00' AND '2024-09-30 23:59:59',
                TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
                TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
            )
        ) = 0
    )
    AND u.customproperty53 IS NOT NULL
    AND u.customproperty54 IS NOT NULL
    AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2024-04-01 00:00:00' AND '2024-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2024-04-01 00:00:00' AND '2024-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND a.status IN (1, 'Manually Provisioned', 'Active')

UNION

SELECT DISTINCT
    a.accountkey AS acctKey,
    a.endpointkey,
    'disableAccount' AS Default_Action_For_Analytics
FROM
    accounts a
JOIN
    user_accounts ua ON a.accountkey = ua.accountkey
JOIN
    users u ON ua.userkey = u.userkey
WHERE
    u.statuskey IN (1)
    AND a.endpointkey = 3
    AND (
        DATEDIFF(
            STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s'),
            IF(
                CURRENT_TIMESTAMP BETWEEN '2025-04-01 00:00:00' AND '2025-09-30 23:59:59',
                TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
                TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
            )
        ) = 0
    )
    AND u.customproperty53 IS NOT NULL
    AND u.customproperty54 IS NOT NULL
    AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2025-04-01 00:00:00' AND '2025-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2025-04-01 00:00:00' AND '2025-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND a.status IN (1, 'Manually Provisioned', 'Active')

UNION

SELECT DISTINCT
    a.accountkey AS acctKey,
    a.endpointkey,
    'disableAccount' AS Default_Action_For_Analytics
FROM
    accounts a
JOIN
    user_accounts ua ON a.accountkey = ua.accountkey
JOIN
    users u ON ua.userkey = u.userkey
WHERE
    u.statuskey IN (1)
    AND a.endpointkey = 3
    AND (
        DATEDIFF(
            STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s'),
            IF(
                CURRENT_TIMESTAMP BETWEEN '2026-04-01 00:00:00' AND '2026-09-30 23:59:59',
                TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
                TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
            )
        ) = 0
    )
    AND u.customproperty53 IS NOT NULL
    AND u.customproperty54 IS NOT NULL
    AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2026-04-01 00:00:00' AND '2026-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2026-04-01 00:00:00' AND '2026-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND a.status IN (1, 'Manually Provisioned', 'Active');

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.
0 Kudos

ARNAB86
Regular Contributor
Regular Contributor

‎07/31/2024 10:12 PM

Hi Rushikesh,

While i run this query even though the i am giving customproperty53 as future time still its fetching the date it should be if customproperty53 less or equal to current time then only it will fetch and also satisfied other condition.

 

Thanks

Arnab Pal

0 Kudos

rushikeshvartak
All-Star
All-Star
In response to ARNAB86

‎07/31/2024 10:24 PM 

SELECT DISTINCT
    a.accountkey AS acctKey,
    a.endpointkey,
    'disableAccount' AS Default_Action_For_Analytics
FROM
    accounts a
JOIN
    user_accounts ua ON a.accountkey = ua.accountkey
JOIN
    users u ON ua.userkey = u.userkey
WHERE
    u.statuskey IN (1)
    AND a.endpointkey = 3
    AND u.customproperty53 IS NOT NULL
    AND u.customproperty54 IS NOT NULL
    AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= CURRENT_TIMESTAMP
    AND (
        (
            CURRENT_TIMESTAMP BETWEEN '2024-04-01 00:00:00' AND '2024-09-30 23:59:59'
            AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP)
            AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP)
        )
        OR
        (
            CURRENT_TIMESTAMP < '2024-04-01 00:00:00'
            OR CURRENT_TIMESTAMP > '2024-09-30 23:59:59'
            AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
            AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        )
    )
    AND a.status IN (1, 'Manually Provisioned', 'Active')

UNION
SELECT DISTINCT
    a.accountkey AS acctKey,
    a.endpointkey,
    'disableAccount' AS Default_Action_For_Analytics
FROM
    accounts a
JOIN
    user_accounts ua ON a.accountkey = ua.accountkey
JOIN
    users u ON ua.userkey = u.userkey
WHERE
    u.statuskey IN (1)
    AND a.endpointkey = 3
    AND u.customproperty53 IS NOT NULL
    AND u.customproperty54 IS NOT NULL
    AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= CURRENT_TIMESTAMP
    AND (
        (
            CURRENT_TIMESTAMP BETWEEN '2025-04-01 00:00:00' AND '2025-09-30 23:59:59'
            AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP)
            AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP)
        )
        OR
        (
            CURRENT_TIMESTAMP < '2025-04-01 00:00:00'
            OR CURRENT_TIMESTAMP > '2025-09-30 23:59:59'
            AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
            AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        )
    )
    AND a.status IN (1, 'Manually Provisioned', 'Active')

UNION
SELECT DISTINCT
    a.accountkey AS acctKey,
    a.endpointkey,
    'disableAccount' AS Default_Action_For_Analytics
FROM
    accounts a
JOIN
    user_accounts ua ON a.accountkey = ua.accountkey
JOIN
    users u ON ua.userkey = u.userkey
WHERE
    u.statuskey IN (1)
    AND a.endpointkey = 3
    AND u.customproperty53 IS NOT NULL
    AND u.customproperty54 IS NOT NULL
    AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= CURRENT_TIMESTAMP
    AND (
        (
            CURRENT_TIMESTAMP BETWEEN '2026-04-01 00:00:00' AND '2026-09-30 23:59:59'
            AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP)
            AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP)
        )
        OR
        (
            CURRENT_TIMESTAMP < '2026-04-01 00:00:00'
            OR CURRENT_TIMESTAMP > '2026-09-30 23:59:59'
            AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
            AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        )
    )
    AND a.status IN (1, 'Manually Provisioned', 'Active');

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.
0 Kudos

ARNAB86
Regular Contributor
Regular Contributor

‎08/01/2024 01:24 AM

Hi Rushikesh,

This code is working which you share

SELECT DISTINCT
    a.accountkey AS acctKey,
    a.endpointkey,
    'disableAccount' AS Default_Action_For_Analytics
FROM
    accounts a
JOIN
    user_accounts ua ON a.accountkey = ua.accountkey
JOIN
    users u ON ua.userkey = u.userkey
WHERE
    u.statuskey IN (1)
    AND a.endpointkey = 3
    AND (
        DATEDIFF(
            STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s'),
            IF(
                CURRENT_TIMESTAMP BETWEEN '2024-04-01 00:00:00' AND '2024-09-30 23:59:59',
                TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
                TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
            )
        ) = 0
    )
    AND u.customproperty53 IS NOT NULL
    AND u.customproperty54 IS NOT NULL
    AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2024-04-01 00:00:00' AND '2024-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2024-04-01 00:00:00' AND '2024-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND a.status IN (1, 'Manually Provisioned', 'Active')

UNION

SELECT DISTINCT
    a.accountkey AS acctKey,
    a.endpointkey,
    'disableAccount' AS Default_Action_For_Analytics
FROM
    accounts a
JOIN
    user_accounts ua ON a.accountkey = ua.accountkey
JOIN
    users u ON ua.userkey = u.userkey
WHERE
    u.statuskey IN (1)
    AND a.endpointkey = 3
    AND (
        DATEDIFF(
            STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s'),
            IF(
                CURRENT_TIMESTAMP BETWEEN '2025-04-01 00:00:00' AND '2025-09-30 23:59:59',
                TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
                TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
            )
        ) = 0
    )
    AND u.customproperty53 IS NOT NULL
    AND u.customproperty54 IS NOT NULL
    AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2025-04-01 00:00:00' AND '2025-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2025-04-01 00:00:00' AND '2025-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND a.status IN (1, 'Manually Provisioned', 'Active')

UNION

SELECT DISTINCT
    a.accountkey AS acctKey,
    a.endpointkey,
    'disableAccount' AS Default_Action_For_Analytics
FROM
    accounts a
JOIN
    user_accounts ua ON a.accountkey = ua.accountkey
JOIN
    users u ON ua.userkey = u.userkey
WHERE
    u.statuskey IN (1)
    AND a.endpointkey = 3
    AND (
        DATEDIFF(
            STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s'),
            IF(
                CURRENT_TIMESTAMP BETWEEN '2026-04-01 00:00:00' AND '2026-09-30 23:59:59',
                TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
                TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
            )
        ) = 0
    )
    AND u.customproperty53 IS NOT NULL
    AND u.customproperty54 IS NOT NULL
    AND STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') <= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2026-04-01 00:00:00' AND '2026-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(
        IF(
            CURRENT_TIMESTAMP BETWEEN '2026-04-01 00:00:00' AND '2026-09-30 23:59:59',
            TIMESTAMPADD(HOUR, 10, CURRENT_TIMESTAMP),
            TIMESTAMPADD(HOUR, 11, CURRENT_TIMESTAMP)
        ),
        '%Y-%m-%d %H:%i:%s'
    )
    AND a.status IN (1, 'Manually Provisioned', 'Active');
0 Kudos

rushikeshvartak
All-Star
All-Star
In response to ARNAB86

‎08/01/2024 09:49 PM

✅Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question and hit 'Kudos' button 👍.


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.
0 Kudos
Copyright © 2024 Khoros, LLC