Sentinel Assitance - KQL Query

[BrandonConn007]

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 assumption is that the query will include a join between IdentityInfo and OfficeActivity but I'm not even sure the information I'm looking for will be in the OfficeActivity table. 

Thanks, 
Brandon

[#2]

@BrandonConn007 

Do you need to know which permission or the fact they have some?

This will look at usage of SharePoint (with any permission) and enables you to add your 15 groups.
You firstly need to edit line #1 for your groups. 
 

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 has 'SharePoint') 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
// only show when we have seen sharepoint usage
| where SharePointactivity > 0
| project-away UserPrincipalName1

[#3]

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 :(

[#4]

@BrandonConn007 

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