Query User historical data in EIC

[ejeong]

I was told before in EIC I can query user historical data which is available under "Update History" in each user. 

can anyone share sample query about how to query historical data? 

thanks!

[armaanzahir · Accepted solution]

Hi @ejeong ,

SELECT 
    u.username, u.firstname, u.lastname, uh.updatedate as 'Update Date', uh.changelog
FROM
    users u
        INNER JOIN
    usershistory uh ON u.userkey = uh.userkey where u.username='armaan.zahir';

Solved: Get User Update History via API / Database Schema - Saviynt Forums - 29569

[ejeong]

@armaanzahir  This is awesome! can we make old value and new value as sperate column?

[armaanzahir]

@ejeong 

The whole changelog is stored in that column field changelog in the usershistory table. I do not believe we can do that. There is also no specific pattern of the string value stored in that field in order for us to split the old value and the new value. 

Also, even if we'd be able to establish a pattern, splitting string values in MySQL into different columns is a pain as MySQL does not have any function to split string values. We'd have to use substring function to achieve it. Again, all this being said, there is no uniform pattern of the string value of this changelog field. 😊

split - MySQL substring extraction using delimiter - Stack Overflow

Generating Reports With Binary Large Object Data (saviyntcloud.com)