Forum Discussion
Sentinel Assitance - KQL Query
Hey! Looking for assistance with creating a KQL query that can look at members of approx. 15 dynamic security groups and identify if they have any SharePoint site permissions across a tenant. My ass...
Ideally, I would just like to know if they have any permission to a particular SPO site. Because of an incorrect rule syntax, they may have been granted access to a SPO site they should not. So, this would be to audit that activity to identify if ANY from those dynamic groups have ANY access to those SPO sites (tenant wide). Big net I know 😞
So now you can join to OfficeActivity and see which sites they accessed, the [Operation] column can give you an idea on permissions e.g. if they have created or modified they wont be read only etc...
To tune this you could add in a LET statement with a list of the specific SPO sites you want to monitor. You may also want to play with the final line, as you may need to show different columns to the ones I choose?
let myGroups = dynamic(["Group 1","Group2","Add the next 13 groups here"]);
SigninLogs
// did they logon successfully and also have Sharepoint acticity
| where ResultType == "0"
| summarize SharePointactivity=countif(AppDisplayName == 'Office 365 SharePoint Online') by UserPrincipalName, AppDisplayName
| join
(
IdentityInfo
// check these groups for membership
//| where GroupMembership has_any(myGroups)
| summarize listGroups=make_set(array_sort_asc(GroupMembership)) by UserPrincipalName=AccountUPN
) on UserPrincipalName
| extend UserPrincipalName= tolower(UserPrincipalName)
// only show when we have seen sharepoint usage
| where SharePointactivity > 0
| project-away UserPrincipalName1
| join
(
OfficeActivity
| where OfficeObjectId has "sharepoint"
| extend UserPrincipalName = UserId
) on UserPrincipalName
| summarize by UserPrincipalName, RecordType, Operation, Site_Url, AppDisplayName