Any Saviynt query to parse the updaate history data?

[MM]

Hi All,

In our implementation, saviynt generates few attribute values. HR wrongly sent us the bad data and now the values are updated and those values cant be reused by logic again.

I need to build a query to get the data from "update history" for those 2 attributes. Anyone has any query to do this?

[rushikeshvartak]

Sample 

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

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

[MM]

I tried this but I still see data in blob. We are on 24.5 version

[rushikeshvartak]

Run report 2 times 

[MM]

sorry we are 24.4

[MM]

Thanks - Running twice works....How can I filter with multiple attributes and only the last update?

[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 '%departmentname%' or changelog like '%customproperty1%')

[MM]

Thanks @rushikeshvartak : Do you know if there is a way I can convert the blob data into a json?

[rushikeshvartak]

https://codebeautify.org/online-json-editor/cbaea050

[MM]

Thanks mate, not sure what I am missing but I get this error

[MM]

and my query is

select changelog, userkey, updatedby from usershistory where UPDATEDATE >= '2024-09-16' and UPDATEDATE <= '2024-09-19' and changelog like '%customproperty11%'

[rushikeshvartak]

Its not Blob data its array data what is expected output you need here ?