Get User Update History via API / Database Schema

[tony334u]

I am trying to see if I can pull User update history programmatically either from the API or from the database schema into an analytics report.  Can this be done?

[DaanishJawed · Accepted solution]

Hi @tony334u ,

Thanks for reaching out.

Below query in the analytics will give all the updates for the user in the CHANGELOG column.

Run this query from the analytics tab in order to get the information.

SELECT 
    u.username, u.firstname, u.lastname, uh.changelog
FROM
    users u
        INNER JOIN
    usershistory uh ON u.userkey = uh.userkey;

[tony334u]

Thank you @DaanishJawed, if I run this directly from the analytics side, the data does show as expected.

[tony334u]

Thanks for the quick reply @DaanishJawed.  I get an error trying to query against the usershistory table, and I am also not seeing this table in the schema.

[DaanishJawed]

Hi @tony334u ,

You will not be able to run this query in Data Analyzer since the table is not exposed.

As mentioned in my previous report, please create a report using the Intelligence tab to run this query.

Navigate to Applications > Intelligence > Analytics Configurations  List > Create New Analytics

[tony334u]

@DaanishJawed , do you have a schema breakout for the usershistory table?  I am trying to pull the dates for when the changes took place for each change log.

[DaanishJawed · Accepted solution]

Hi @tony334u ,

Please try the below query to get the update dates of each change log.

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;

[tony334u]

This worked, thank you @DaanishJawed !!