KQL Search

Query Details

HomeAI ToolsDevice Query

Office Activity Visualize Guests Redeemedvs Addedto Teams

Query

//Visualize total guests redeemed in Azure AD vs guests that have been added to a Team

//Data connector required for this query - Office 365

let guestsredeemed=
    AuditLogs
    | where TimeGenerated > ago (90d)
    | where OperationName == "Redeem external user invite"
    | extend GuestRedeemed = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend Activity = strcat("Guest Invite Redeemed")
    | project TimeGenerated, GuestRedeemed, Activity;
let guestsaddedtoteams=
    OfficeActivity
    | where TimeGenerated > ago(90d)
    | where Operation == "MemberAdded"
    | mv-expand Members
    | extend GuestAdded = tostring(Members.UPN)
    | where GuestAdded contains "#EXT#"
    | extend Activity = strcat("Guest Added to Team")
    | project TimeGenerated, GuestAdded, Activity;
union guestsredeemed, guestsaddedtoteams
| summarize ['Total Count']=count() by Activity, bin(TimeGenerated, 1d)
| render columnchart with (kind=unstacked, title="Total Guests Redeemed vs Guests Added to Teams")

Explanation

This query visualizes the total number of guests redeemed in Azure AD (external users who have accepted an invitation) compared to the number of guests that have been added to a Team. It uses the Office 365 data connector. The query retrieves the relevant data from the AuditLogs and OfficeActivity tables, filters it based on specific criteria, and then combines the results. Finally, it summarizes the data by activity and day, and renders a column chart to display the comparison.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

AuditLogsOfficeActivity

Keywords

Devices,Intune,User,AuditLogs,OfficeActivity,TimeGenerated,OperationName,Redeemexternaluserinvite,InitiatedBy.user,userPrincipalName,Activity,GuestRedeemed,Project,Office365,GuestAdded,MemberAdded,Members,UPN,EXT,Union,Summarize,TotalCount,bin,Render,columnchart,kind,unstacked,title

Operators

| whereago==extendtostringparse_jsonstrcatproject|mv-expandcontainsunionsummarizecountbybinrender

Actions