Need information to generate report for rehire using User History Table

[sairamya15]

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.

[rushikeshvartak]

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%'

Refer https://forums.saviynt.com/t5/identity-governance/users-whose-department-numbers-have-changed-within... 

[sairamya15]

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. 

[rushikeshvartak]

Share output of query to suggest further edit

Note : As i dont have sample data for your use case 😄

[sairamya15]

Hi @rushikeshvartak 

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

[rushikeshvartak]

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]%' 

[sairamya15]

Hi @rushikeshvartak 

Tried the above query it says 0 Records. 

[rushikeshvartak]

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]%' 

[sairamya15]

Hi @rushikeshvartak 

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.

[rushikeshvartak]

Can you paste sample change log

[PremMahadikar]

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

[sairamya15]

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.

[rushikeshvartak]

Can you share sample line as text

[rushikeshvartak]

Use case when then end logic 

[Abhinav]

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

[rushikeshvartak]

Share your output here in text format

[rushikeshvartak]

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 

[Abhinav]

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,

[rushikeshvartak]

Please create new thread for issue. and share actual data  without filter and user history screenshot