Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/22/2024 07:46 AM
Hi All,
We have requirement to generate the rehire report, Our Auth source is DB which does not store any value of Term date or End date of user, except status as A or T or LOA which is stored in customproperty12.
So we want to generate a report which fetches the data of the rehired users whose status are changed T to A leaving other status.
can anyone help with the query to know the updated user history details(from T to A) for a customproperty12.
07/22/2024 08:38 AM
select u.username, u.userkey, uh.changelog from users u inner join usershistory uh on u.userkey = uh.userkey where username = '137658'
and uh.UPDATEDATE >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) and changelog like '%customproperty12%'
07/22/2024 09:50 AM
Hi @rushikeshvartak
Thanks for the response.
In the query provided we can view the whole change log but for rehire report we want only change log where T is changed to A.
Any suggestion on that will be appreciated.
07/22/2024 09:58 AM
Share output of query to suggest further edit
Note : As i dont have sample data for your use case 😄
07/23/2024 02:31 AM
Below is the output screenshot for the query
select u.username, u.userkey, uh.changelog from users u inner join usershistory uh on u.userkey = uh.userkey where username is NOT NULL and uh.UPDATEDATE >= DATE_SUB(CURDATE(), INTERVAL 3 DAY) and changelog like '%customproperty11%'
If you see the output the change log associated with CP11 is displayed but we have a requirement to view records whose CP11 is changed from T to A
07/23/2024 02:45 AM
select u.username, u.userkey, uh.changelog from users u inner join usershistory uh on u.userkey = uh.userkey where username is NOT NULL and uh.UPDATEDATE >= DATE_SUB(CURDATE(), INTERVAL 3 DAY) and changelog like '%customproperty11[A, T]%'
07/24/2024 12:27 AM
Tried the above query it says 0 Records.
07/24/2024 08:11 PM
select u.username, u.userkey, uh.changelog from users u inner join usershistory uh on u.userkey = uh.userkey where username is NOT NULL and uh.UPDATEDATE >= DATE_SUB(CURDATE(), INTERVAL 3 DAY) and changelog like '%customproperty11:[A, T]%'
07/26/2024 08:49 AM
I have tried below
select u.username, u.userkey, uh.changelog from users u inner join usershistory uh on u.userkey = uh.userkey where username is NOT NULL and uh.UPDATEDATE >= DATE_SUB(CURDATE(), INTERVAL 3 DAY) and changelog like '%customproperty11:[A, T]%'
select u.username, u.userkey, uh.changelog from users u inner join usershistory uh on u.userkey = uh.userkey where username is NOT NULL and uh.UPDATEDATE >= DATE_SUB(CURDATE(), INTERVAL 3 DAY) and changelog like '%statuskey:[0, 1]%'
both logics are not returning any record.
If I remove :[A, T] or :[0, 1] then only records are generated and whole log is generated.
07/27/2024 07:03 AM
Can you paste sample change log
07/26/2024 01:42 PM
Hi @sairamya15 ,
Try this way: In your analytics, can you only run this query below,
*Make sure to hardcode/add one username of that user who is getting rehire
Query:
select
u.username,
u.userkey as userkey,
uh.changelog
from
users u inner join usershistory uh on u.userkey = uh.userkey
where
username is NOT NULL
and uh.UPDATEDATE >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
and u.username='<username of the user who is getting rehire>'
Then check the result in analytics, if you can see the change something like below:
If you don't see the above result and still see blob data, run the analytics twice. You should be able to see the result like above.
Now, based on you user change i.e. customproperty12, look at the result (changelog) how it's been displayed, accordingly extend/update the analytics query and run it without hardcoding it.
and changelog like '%customproperty12%'
If this helps, please consider selecting Accept As Solution and hit Kudos
07/31/2024 06:58 AM
Hi @PremMahadikar @rushikeshvartak
Thanks for the Response.
We are looking for output something as below(Customproperty11 changed from T to A or statuskey changed from 0 to 1)
But we are getting output as below.
Which all the update history of Customproperty11.
07/31/2024 07:00 AM
Can you share sample line as text
07/31/2024 07:01 AM
Use case when then end logic
08/19/2024 07:51 AM
Hi Sairamya15,
Didi u manage to make this query work ?
I have similar requirements, and I wanted to read and filter changelog where the value is '[customproperty39:[null, Yes]]' only.
I am trying it as "where uh.changelog = '%customproperty39:[null, Yes]%", but it is showing records. Query is showing records in where uh.changelog = '%customproperty39% condition, but this contains both [null, Yes] & [Yes, null], so trying to filter it to requirement.
Any help is much appreciated.
Thanks
08/19/2024 08:00 AM
Share your output here in text format
08/19/2024 08:11 AM
Here is fix for query in BLOB: special characters are stored in codes; hence, use below query
Validation
Data without Filter
Data with Filter
SELECT u.username,
u.userkey,
uh.changelog
FROM users u
inner join usershistory uh
ON u.userkey = uh.userkey
WHERE username IS NOT NULL
AND uh.updatedate >= Date_sub(Curdate(), interval 3 day)
AND changelog LIKE '%customproperty11%A%T%'
AND u.userkey = 689261
08/19/2024 08:21 AM
I have tried to used as where uh.changelog = '%customproperty39%null%Yes%', but it is showing below warning of NO data while updating analytics
Thanks,
08/19/2024 08:22 AM
Please create new thread for issue. and share actual data without filter and user history screenshot