Forum Discussion

BrandonConn007's avatar
BrandonConn007
Copper Contributor
Jan 10, 2024

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

Analytics
detection
KQL
UEBA
  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Jan 10, 2024

    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

     

    • BrandonConn007's avatar
      BrandonConn007
      Copper Contributor
      Jan 10, 2024
      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 😞
      • Clive_Watson's avatar
        Clive_Watson
        Bronze Contributor
        Jan 10, 2024

        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

         

Resources