Microsoft Defender for Cloud Apps - Shadow IT Reporting
MCAS Shadow Reporting
Query
McasShadowItReporting
| where TimeGenerated > ago (90d)
| where StreamName == "Defender-managed endpoints"
| summarize Totalbytes = sum(TotalBytes), UploadBytes = sum( UploadedBytes), DownloadBytes = sum(DownloadedBytes), Users = make_set(EnrichedUserName), Devices = make_set(MachineName), IPAddresses = make_set(IpAddress) by AppName, AppScore
| extend TotalDevices = array_length(Devices)
| extend TotalIPAddresses = array_length(IPAddresses)
| extend Totalusers = array_length(Users)
| extend UploadMB = format_bytes(UploadBytes,0,"MB")
| extend TotalTraffic = format_bytes(Totalbytes,0,"MB")
| extend DownloadMB = format_bytes(DownloadBytes,0,"MB")
| project AppName,AppScore, TotalDevices, TotalIPAddresses, Totalusers, TotalTraffic, UploadMB, DownloadMB, IPAddresses, Devices, UsersAbout this query
Explanation
This KQL script is designed to analyze shadow IT activities using data from Microsoft Defender for Cloud Apps. It consists of two queries that provide insights into application usage and user activity over the past 90 days. Here's a simple breakdown:
-
Query 1: Application-Centric View
- Purpose: To summarize shadow IT activities by application.
- Process:
- Filters data from the last 90 days for "Defender-managed endpoints."
- Aggregates data to calculate total bytes, upload bytes, and download bytes for each application.
- Collects unique users, devices, and IP addresses associated with each application.
- Calculates the number of devices, IP addresses, and users per application.
- Converts byte data into megabytes for easier interpretation.
- Displays results including application name, score, traffic details, and associated users, devices, and IP addresses.
-
Query 2: User-Centric View
- Purpose: To summarize shadow IT activities by user.
- Process:
- Similar to the first query, it filters data from the last 90 days for "Defender-managed endpoints."
- Aggregates data to calculate total bytes, upload bytes, and download bytes per user.
- Collects unique devices, IP addresses, and applications used by each user.
- Calculates the number of devices, IP addresses, users, and applications per user.
- Converts byte data into megabytes for easier interpretation.
- Displays results including user name, traffic details, and associated devices, IP addresses, and applications.
These queries help organizations monitor and manage unauthorized or unmanaged application usage, providing insights into potential security risks associated with shadow IT.
Details
Alex Verboon
Released: April 16, 2026
Tables
McasShadowItReporting
Keywords
McasShadowItReportingTimeGeneratedStreamNameTotalBytesUploadedBytesDownloadedBytesEnrichedUserNameMachineNameIpAddressAppNameAppScore
Operators
|where>ago==summarizesummake_setbyextendarray_lengthformat_bytesproject