Cube Data Audit Report
Hi All,
Has anyone built a custom audit data report for their OneStream planning applications which shows which users changed which entities/accounts/scenarios, etc on specific days?
Not sure if we have a marketplace solution for this.
Any leads will be helpful.
Thanks,
Nidhi Mangtani
That report is backed by the WF_DataEntry_Detail_RPTA adapter, which is one big sql query. That query is user-agnostic (I guess the report does some extra filtering later, restricting results to your own changes), it will give you all changes; you can have a look at the WHERE clause and modify it to point to the records you want.
You can then retrieve the IDs of users referenced in an audit from the table AuditSecUser, in the Framework database, like the adapter WF_DataEntryUserInfo adapter does:
SELECT UniqueID, Name From AuditSecUser GROUP BY Name, AuditSecUser.UniqueID
The UniqueID values listed there are the ones that appear in audit tables. (Tbh, at a glance, they seem to be the same IDs used in the SecUser table in Framework, but maybe things change with time, so I'd stick to AuditSecUser.)
Of course you won't be able to join these IDs in one big SQL, since audit tables are in the Application database and user IDs are in the Framework one. The logic should probably be:
- retrieve all changes from audit tables that you're interested in, regardless of user. I'd copy the query in WF_DataEntry_Detail_RPTA for that and tweak it, but you already have something.
- from those records, extract the user IDs and query AuditSecUser to get readable details, with a query like SELECT UniqueID, Name, Email From AuditSecUser where UniqueId in ('myId1', 'myId2', 'myId3')
- blend the two datasets together. This looks to me like a job for Dashboard DataSets, very similar to a recent blog post...
Am I missing anything...?