Audit reports

Bradley Dixon shared this question 2 years ago
Answered

Hi,I need to figure out which users are running what reports in which tenant. In the event table I could not figure out what tenant the event came from. Are you able to show me SQL which might provide this sight?

Brad

Comments (6)

photo
1

Hi Brad,


yes you're right, the Event table doesn't record that information. I've been scrounging around the other tables and come up with the following:


SELECT DISTINCT RI.ReportId, O.OrgName

FROM ReportHeader RH INNER JOIN ReportInstance RI ON RH.ReportId = RI.Reportid

INNER JOIN ORGANISATION O ON RI.IpRequestor = O.IpOrg

WHERE RH.ReportName = '<insert report name here>'


hope that's something like what you're looking for.


regards,

David

photo
1

Hi David,


Thanks for the information however I dont think this gives us the ability to determine who ran what report and from what tenant they ran it from?


Regards,

Brad

photo
1

Hi Brad,


I guess I was rushing to try and complete the task for you before home-time and thought it was finished, but when looking at it again today I can see it wasn't.

In fact, looking at the problem now, I'm not even sure it can be done because the ReportInstance table (as well as the Event table) doesn't record the tenant.


So all I can suggest is that if most of your users only belong to one tenant then you can look up which tenant via the following query:


SELECT O.orgname, P.FullName

FROM IpRltshp IR

INNER JOIN Person P ON IR.IpChild = P.IpPerson

INNER JOIN Organisation O on IR.IpParent = O.IpOrg

WHERE IR.IpChild = <insert Event.IpSource here> AND IR.IpParent != 1


But other than that, all I can suggest is to raise an Idea to add the tenant ID into the Event data.


Regards,

David

photo
1

Hi Brad,


I'm just checking if you'd like to close this question or keep it open because the question isn't answered to your satisfaction?


regards,

David

photo
1

Happy to close this one off

photo
1

OK, thanks for letting us know.