How to fetch user update data

[smithamg]

Hi Team,

We have a requirement to retrieve below data related to users customproperty2 and customproperty43 changes.

For the months of March, April and May:

• Number of users with only Customproperty2 change.
• Number of users with only customproperty43 change.
• Number of users with both Customproperty2 and Customproperty43 change.

How can we fetch this data as we don't have access to USERUPDATEHISTORY table? Kindly assist. 

Thanks,

Smitha

[rushikeshvartak]

This can't be fetched in v2021.x version but its supported in higher version v2022.x onwards

https://ideas.saviynt.com/ideas/EIC-I-3336

https://docs.saviyntcloud.com/bundle/Release-Notes/page/Content/v2022.0/Release-Notes-v2022-0-1.htm

[PhilR]

In 2021 and below, only if you have (or create) a rule which executes on the updates, you can query for those changes like this, using customproperty50 as an example:

select username, customproperty50 from users where username in
(
select u.username from users U, USERSHISTORY UH, USERRULERUNDATA_ARCHIVE URR
WHERE
U.USERKEY = UH.USERKEY
AND UH.USERSHISTORYKEY = URR.USERHISTORYKEY
AND urr.changemap like '%customproperty50":[null%'
)
and customproperty50 is not null

[smithamg]

Hi Phil,

We used the query you provided in analytics, just by changing the CP50 to CP43  as given below.

select username, customproperty43 from users where username in
(
select u.username from users U, USERSHISTORY UH, USERRULERUNDATA_ARCHIVE URR
WHERE
U.USERKEY = UH.USERKEY
AND UH.USERSHISTORYKEY = URR.USERHISTORYKEY
AND urr.changemap like '%customproperty43%'
)
and customproperty43 is not null

But it's saying 'Total 0 records saved' even though there are users whose CP43  is changed.

Please assist with it.

Thank you.

[rushikeshvartak]

https://docs.saviyntcloud.com/bundle/EIC-Admin-v2022x/page/Content/Chapter17-EIC-Analytics/Generatin...

[PhilR]

"only if you have (or create) a rule which executes on the updates, you can query for those changes"

-Phil