I’d like to create an Azure Workbook to display all PIM activations within last x days and after going crazy and a lot of shed tears, now i’m stuck.
I don’t get how to combine the request event with the approval event.
As far as I know (or rather, as far as I concluded from data in my Log Analytics Workspace) : during the process of activating a Role within PIM there are 2 or 4 events logged:
1: Add member to role requested (PIM activation),
2: Add member to role approval requested (PIM activation),
3: Add member to role request approved (PIM activation),
4: Add member to role completed (PIM activation)
1 & 4 are logged during every activation and 2 + 3 are logged for approvals. So far, so easy. But how do these events correlate with each other so that I can display them automatically with KQL within 1 Line? I don’t see any correlating ID (because, funfact, the “CorrelationID” changes between event 2 and 3).
I’ve built a KQL query which is probably totally overengineered (because I had no clue of Kusto 3 days ago and my SQL Knowledge was used 11 years ago…)
A few words about the following code: I had the idea of creating the 2 temporary tables “Requests” and “Approvals” and join them together – preferred via an correlating ID, but I can’t find any – via the UserObjectID from the requesting User in combination with the RoleID and the TimeGenerated (as close after the requesting event, as possible). But I have no clue how to do this 😀
My Vision for the result is 1 activation per line and the events without any needed approval have an empty field in this column like this: