Analytics query for disabled account for specific time interval

[ARNAB86]

Hi Team,

Can someone please help me to get the analytics query to disable the AD account for some specific time interval (based on starttime and endtime).

Thanks

Arnab Pal

[pmahalle]

@ARNAB86 , 

When you are saying for specific time then what date you are talking about? If you want to create for during specific period then you schedule the actionable analytics for that period. You can use below query

select distinct u.username, a.accountkey as acctKey, 'disableAccount' AS Default_Action_For_Analytics, a.name as accName, u.userkey as userKey, sysdate() as date from users u, accounts a, user_accounts ua, endpoints e where u.userkey=ua.userkey and ua.accountkey=a.accountkey and a.endpointkey=e.endpointkey and a.status in (1,'Manually Provisioned') and e.endpointname='<Provide endpoint name here>'

[SumathiSomala]

@ARNAB86 Create one actionable analytic report.Adjust the below query as per your requirement.

select a.name,a.accountkey as acctKey, endpointkey,'disableAccount' as Default_Action_For_Analytics from accounts a where accountkey in (select accountkey from user_accounts) and DATEDIFF(sysdate(),customproperty11) =0

https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter17-EIC-Analytics/Managing-An...

[rushikeshvartak]

Refer https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter17-EIC-Analytics/Managing-An...

select a.name,a.accountkey as acctKey, endpointkey, 'disableAccount' as Default_Action_For_Analytics from accounts a

 WHERE a.lastlogondate BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59' and endpointkey=10 limit 1;

[ARNAB86]

Hi Rushikesh/ALL

Thanks

Actually i want to disable the user account for AD where End Date is passing as 14-07-2024 and storing it in user customproperty14.

So when the EndDate is matching with Current date the analytics will run and create the disable account task for that account of an user.

Please let us know the query.

Thanks in advance

[rushikeshvartak]

SELECT
a.name,
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
a.lastlogondate BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59'
AND a.endpointkey = 10
AND u.customproperty14 = DATE_FORMAT(NOW(), '%d-%m-%Y')
LIMIT 1;

[ARNAB86]

Hi Rushikesh,

Thanks

If i want to enable the account based on user custompropery15 means if we are passing start date as 15-07-2024 in custompropery15 field.So when the currentdate matches with customproperty15 then analytics should run and based on that enable account task will get created.

Please let me know the analytic query to enable the account.

Thanks

Arnab Pal

[rushikeshvartak]

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

[ARNAB86]

Hi Rushikesh

I have opened new thread.Can you please help on enable account

link

https://forums.saviynt.com/t5/identity-governance/analytics-query-for-enable-account-for-specific-ti...

Thanks

Arnab Pal

[ARNAB86]

Hi Rushikesh,

I have small clarification i need to fetch both Active and Manually Provisioned account to be fetched in Analytics query and also while i run current query it is showing only one recored but we have 2 records which met the criteria.Please suggest

[rushikeshvartak · Accepted solution]

SELECT
a.name,
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
a.lastlogondate BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59'
AND a.endpointkey = 10
AND u.customproperty14 = DATE_FORMAT(NOW(), '%d-%m-%Y')
AND a.status IN (1, 'Manually Provisioned')

[ARNAB86]

Hi Rushi,

I have small change in passing date format

Instead of passing 17-07-2024 need to pass as 17-07-2024 14:00:00

remaining logic is same.

customproperty14if i will be passing 17-07-2024 14:00:00 and it will check for current date and time and then analytics will run and create disable account task

thanks for your support

[ARNAB86]

Hi Rushi,

I am passing the customproperty14 as string in dynamic attribute form, so the query which you have shared doesn't have any impact right or again i need to convert custom property 14 value in date format.

Please help

Thanks

Arnab Pal

[rushikeshvartak]

Yes

[ARNAB86]

So you mean no changes require in the existing query right

[rushikeshvartak]

Yes as long as format is same

[ARNAB86]

Hi Rushikesh,

There is a bit changes in the logic

customproperty53 passing  as enddate(2024-07-18 12:14:00) and customproperty54 passing start date as (2024-07-18 16:00:00) so basically analytics will check for this duration and create the disable account task using analytics within this time period.if the enddate >=current time and <=startdate

Could you please help me. Using below query seems not working

SELECT distinct u.username as 'UserName',
now() as 'Now',
STR_TO_DATE(u.customproperty53,'%Y-%m-%d %h:%i:%s') as 'CP53',
STR_TO_DATE(u.customproperty54,'%Y-%m-%d %h:%i:%s') as 'CP54',
DATEDIFF (STR_TO_DATE(u.customproperty53,'%Y-%m-%d %h:%i:%s') ,STR_TO_DATE(u.customproperty54,'%Y-%m-%d %h:%i:%s')) as 'DATEDIFF',
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') >= now()
AND STR_TO_DATE(u.customproperty53,'%Y-%m-%d %h:%i:%s') <= STR_TO_DATE(u.customproperty54,'%Y-%m-%d %h:%i:%s')
AND a.status IN (1, 'Manually Provisioned')

Query is fetching wrong value

CP54 is already past date and time but still it is fetching.

Please help if i am missing anything

Thanks

Arnab Pal

[rushikeshvartak]

SELECT DISTINCT
    u.username AS 'UserName',
    NOW() AS 'Now',
    STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s') AS 'CP53',
    STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') AS 'CP54',
    DATEDIFF(STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s'), STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s')) AS 'DATEDIFF',
    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 = 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') >= NOW()
    AND STR_TO_DATE(u.customproperty54, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE(u.customproperty53, '%Y-%m-%d %H:%i:%s')
    AND a.status IN (1, 'Manually Provisioned');