Analytics report to show when User record has been updated(user data has been updated)

[Community_User]

Originally posted on August 28 2020 at 10:17 UTC

Hi!

I am trying to create report that shows when user record has been updated in Saviynt.

I am using following query, but it only shows 4 user records and none of them is updated

from ongoing date - 30 days when i check Update History?

Am i missing something?

select b.USERKEY from usershistory a,users b where DATEDIFF(a.UPDATEDATE, now()) < 30

AND a.USERKEY = b.USERKEY

AND b.STATUSKEY =1

AND b.EMPLOYEEID IS NOT NULL

AND b.SYSTEMUSERNAME IS NOT NULL;

Best Regards, Päivi

[Community_User]

Originally posted on September 3 2020 at 10:50 UTC

Hi Päivi,

You need to use DATEDIFF(now(),a.UPDATEDATE) and not DATEDIFF(a.UPDATEDATE,now()).

Sample query -

select b.USERKEY, b.username, a.UPDATEDATE

from usershistory a,users b where DATEDIFF(now(),a.UPDATEDATE) < 30

AND a.USERKEY = b.USERKEY

AND b.STATUSKEY =1

AND b.EMPLOYEEID IS NOT NULL

AND b.SYSTEMUSERNAME IS NOT NULL order by userkey;

Regards,

Saparja

[Community_User]

Originally posted on September 4 2020 at 11:47 UTC

Hi!

This is restricted from use: a.UPDATEDATE,

Column UPDATEDATE are restricted from use. Please specify columns other than these. Also, use of symbols <,>,& in column name is restricted.

best Regards, Päivi

[Community_User · Accepted solution]

Originally posted on September 4 2020 at 18:05 UTC

Hi,

Please change the alias and try

select b.USERKEY, b.username, a.UPDATEDATE as 'User Update Date'

from usershistory a,users b where DATEDIFF(now(),a.UPDATEDATE) < 30

AND a.USERKEY = b.USERKEY

AND b.STATUSKEY =1

AND b.EMPLOYEEID IS NOT NULL

AND b.SYSTEMUSERNAME IS NOT NULL order by userkey;

Regards,

Saparja