Query to get the job function change time stamp for users

[Sharada]

Hi Team,

Need help in query for getting the timestamp for user's job function change. My requirement is to fetch the list of users whose job function was changed in the previous month. Let me know if any other info is required.

Regards,

Sharada 

[rushikeshvartak]

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

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 30 DAY)
and changelog like '%job_function%' 

	

[Sharada]

Thanks for the response @Rushikesh. I tried the above query, but the output is not as expected. The change log column display some random values.

Rgard,
Sharada

[rushikeshvartak]

select 
	u.username, 
	u.userkey as userkey, 
	uh.changelog 
from 
	users u inner join usershistory uh on u.userkey = uh.userkey 
where 
	username ='Rushi'
	and uh.UPDATEDATE >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

Validate with one user, share output and accordingly change your query

[Sharada]

Hi @rushikeshvartak : The above query also gave the same random output for changelog column. PFB the output for one specific user. Let me know if any changes need to be made.

[PremMahadikar]

Hi @Sharada ,

Run it twice!

The first time when you run, yes, I also see it in encrypted value.

The second time, its show you right values:

Note: This is only one-time activity, from the second run, it will generate right values

If this helps, please consider selecting Accept As Solution and give Kudos

[Sharada]

@PremMahadikar : Thank you for the reply. I did try running it twice but got the same output (encrypted Values) both times.

Regards,

Sharada

[rushikeshvartak · Accepted solution]

This is known issue in 24.4 and fixed in v24.5

Refer https://forums.saviynt.com/t5/identity-governance/usershistory-table-changelog-blob-data-is-not-read...