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?
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.
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?
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.
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?
Happy to close this one off
OK, thanks for letting us know.
Comments have been locked on this page!