03-09-2023 11:23 AM - last edited on 05-24-2023 12:48 PM by JackLacava
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
Solved! Go to Solution.
03-11-2023 08:46 PM
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:
Am I missing anything...?
03-10-2023 04:16 AM
Welcome back!
In Standard Application Reports (solution available in Marketplace, that typically is installed in every app), I see we have a bunch of reports related to auditing changes:
When I dig a bit more in the "XFS Application Reports (RPTA)" Dashboard Maintenance Unit that produces those reports, I can see that there are a bunch of Data Adapters running rules:
You could grab those rules and adapters and customize them, or just call them from your own Dashboard DataSet rule with BRApi.Dashboards.Process.GetAdoDataSetForAdapter and then filter or customize the output further.
Hope that helps!
03-10-2023 06:23 AM
Hi Jack,
Thanks for your response. We are looking for data audit, not component audit.
Could fetch which entity, which account, etc have changed from DataEntryAuditCell and DataEntryAuditSource. Not sure how to pull user information from here not able to trace where this UserID maps to in the framework tables.
If we pull details from TaskActivity alone, we get the user details but not the data unit details.
Getting both in one view has been a blocker.
Thanks,
NIdhi Mangtani
03-10-2023 10:30 AM
The user IDs in audit tables are from AuditSecUser. I got that by examining the adapters associated with the Data Entry Detail report, which I found by using the dashboard in Design Mode 😀
03-10-2023 11:35 AM
Hi Jack,
Thanks for this suggestion. This definitely improved the view I was pulling, but I am still stuck with the userID. This "Data Entry Detail" report shows all the cells edited by me if I open it, I am building this data audit report as an admin to check which all other users have made changes to the current planning scenario for say a particular quarter in last week or so.
I could get all other bits but userID is not traceable in any other tables.
03-11-2023 08:46 PM
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:
Am I missing anything...?
03-13-2023 05:01 AM
Thanks a lot Jack. This worked. I had already tried this, but for query 2, I was manually checking for the records in AuditSecUser for specific IDs and those didn't show up. On running a framework query through data adapter, the user details show up. Thanks a lot for putting your time on this and guiding me through.
Cheers.