Report - Attribute Changes

[sabthamis]

Hello Everyone,

We have a requirement to create a report which lists the user for whom the attribute changed (say  when job title and city)on user import. When any one of this attribute change we need the user to be listed in the report. 

Tried the below options:

1.Created user update rule with action as notify email which triggers email but this would be triggering each mail for one user. Will get a lot of email when there are many updates

2. Tried using the Custom SQL query we don't have table / column which list the attribute updated. In users table i got the update date but filter further when attribute change is done for particular 2 attributes

Any help is much appreciated.

Thanks,

Sabthami Subramanian

[armaanzahir]

Hi @sabthamis ,

Can you try referring to the below documentation links and check. This should work:

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

Creating a report to fetch the User's History (saviyntcloud.com)

The user history changelog is stored in the usershistory  table and can be referenced when you create an analytic on IGA. 

[AmitM]

Hi @sabthamis ,

userhistory is the table and column name is changelog. you can find more details in below docs :

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

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

Thanks,

Amit

[sabthamis]

Hi @AmitM @armaanzahir - I tried already with the below query and i don't  see the changelog as readable.

select b.username, b.firstname,b.lastname,a.UPDATEDATE,a.changelog AS 'ChangedDate'

from usershistory a,users b

where DATE_FORMAT(a.updatedate,'%Y-%m-%d')=CURDATE()

AND a.USERKEY = b.USERKEY

[armaanzahir · Accepted solution]

Hi @sabthamis 

This is working fine with alias changes to the query.

select b.username, b.firstname,b.lastname,a.UPDATEDATE as updatedte,a.changelog from usershistory a,users b where DATE_FORMAT(a.updatedate,'%Y-%m-%d')=CURDATE() AND a.USERKEY = b.USERKEY

You don't have to define the alias for changelog column.