User History Table Columns

[bbuchler]

I am working on creating a user history report. Though I am not able to find documentation on which columns the table has. Is there a link to this documentation. 

[rushikeshvartak]

Query : select u.username, u.userkey, uh.changelog from users u inner join usershistory uh on u.userkey = uh.userkey where username = ${username}

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

[Manu269]

Hello @bbuchler ,

Adding further points to what @rushikeshvartak added :

1. If you are looking to see what changed for the users, this table could help - userrulerundata_archive. 

This table has userkey, changemap, updatedate columns. changemap column gives information on attributes changes for a user & at what time it happened.

2. 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

3.  For 2022.x :

Analytics and Intelligence

The EIC database stores data such as user history, change log, and job details in the Binary Large Object (BLOB) format. From this release, you can view the BLOB data in the Analytics History Details page and generate reports containing this data for SQL Query, Runtime, and Data Query-based analytics controls.

[bbuchler]

HI @rushikeshvartak  and @Manu269  

Thank you for the information provided. I was looking more for the coulumns that could be retireved as part of the userhistory table. The report I am trying to make is users who status went from 1->0 and the change date of that is 31 days. The above documenation link has the basics but was looking for the schema of the table. 

[rushikeshvartak · Accepted solution]

You can't use more columns than listed above those are not exposed on UI

TABLE_NAME COLUMN_NAME IS_NULLABLE DATA_TYPE COLUMN_TYPE

usershistory	USERSHISTORYKEY	NO	bigint	bigint(20)
usershistory	CHANGELOG	NO	longblob	longblob
usershistory	RULERUNS	YES	varchar	varchar(255)
usershistory	UPDATEDATE	NO	datetime	datetime
usershistory	UPDATEDBY	NO	varchar	varchar(255)
usershistory	USERKEY	NO	bigint	bigint(20)
usershistory	REFIREACTIONTRAIL	YES	longtext	longtext
usershistory	RULEACTIONFAILED	YES	longtext	longtext
usershistory	RULEACTIONSUCCESSFUL	YES	longtext	longtext
usershistory	RULEACTIONTOBEFIRED	YES	longtext	longtext

[bbuchler]

Hi @rushikeshvartak , 

This is what I was looking for thank you!

Thanks,

Brandon