Microsoft Teams Emoji Reactions for each Department

Teams Emoji Reactions By Department

Query

CloudAppEvents
| where Application == "Microsoft Teams"
| where ActionType == "ReactedToMessage"
| extend Emoji = tostring(RawEventData.MessageReactionType)
| where isnotempty(Emoji)
| project Emoji, AccountObjectId
| join kind=inner (IdentityInfo
    | where Timestamp > ago(30d)
    | summarize arg_max(Timestamp, *) by AccountObjectId
    | project AccountObjectId, Department)
    on $left.AccountObjectId == $right.AccountObjectId
| project Department, Emoji
| evaluate pivot(Department) // If you want to have the Departments on the y axis use | evaluate pivot(Emoji)

About this query

Microsoft Teams Emoji Reactions for each Department

Query Information

Description

This query lists the statistics of the Emoji reactions that have been send via Microsoft Teams for each Department.

Defender XDR

Sentinel

CloudAppEvents
| where Application == "Microsoft Teams"
| where ActionType == "ReactedToMessage"
| extend Emoji = tostring(RawEventData.MessageReactionType)
| where isnotempty(Emoji)
| project Emoji, AccountObjectId
| join kind=inner (IdentityInfo
    | where TimeGenerated > ago(30d)
    | summarize arg_max(TimeGenerated, *) by AccountObjectId
    | project AccountObjectId, Department)
    on $left.AccountObjectId == $right.AccountObjectId
| project Department, Emoji
| evaluate pivot(Department) // If you want to have the Departments on the y axis use | evaluate pivot(Emoji)

Explanation

This query is designed to analyze and display the usage of emoji reactions in Microsoft Teams, broken down by department. Here's a simple breakdown of what the query does:

Data Source: It starts by looking at events from Microsoft Teams, specifically focusing on actions where users have reacted to messages with emojis.
Extract Emoji Information: The query extracts the type of emoji used in each reaction.
Filter Non-Empty Emojis: It ensures that only reactions with actual emojis are considered by filtering out any empty entries.
User and Department Information: The query then joins this emoji data with user identity information to associate each emoji reaction with the user's department. It uses the most recent identity information from the past 30 days to ensure accuracy.
Pivot Table: Finally, it organizes the data into a pivot table format, where each department is listed along with the count of each type of emoji reaction used by that department. This allows for easy comparison of emoji usage across different departments.

In summary, this query provides a detailed view of how different departments within an organization use emoji reactions in Microsoft Teams, helping to understand communication patterns and engagement levels.

Details

Bert-Jan Pals

Released: October 20, 2024

Tables

CloudAppEventsIdentityInfo

Keywords

CloudAppEventsMicrosoftTeamsReactedToMessageEmojiAccountObjectIdIdentityInfoTimestampDepartmentTimeGenerated

Operators

whereextendtostring()isnotempty()projectjoin kind=innersummarizearg_max()onevaluatepivot()ago()

Actions

GitHub

KQL Search