MCAS Shadow Reporting

# Microsoft Defender for Cloud Apps - Shadow IT Reporting ## Query Information ### Description Use the below queries to reretrieve Defender for Cloud Apps - shadow discovery information. The first query represents the information as shown in the portal. The second query shows the information per user. > Note that the data in the online portal is refreshed periodically, meaning that you might get different results when running the below queries. #### References #### Credits Kim Oppalfens - @TheWMIGuy ### Microsoft Sentinel MCAS Shadown Reporting details by Application ```kql McasShadowItReporting | where TimeGenerated > ago (90d) | where StreamName == "Win10 Endpoint Users" | 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, Users ``` MCAS Shadown Reporting details by User ```kql McasShadowItReporting | where TimeGenerated > ago (90d) | where StreamName == "Win10 Endpoint Users" | summarize Totalbytes = sum(TotalBytes), UploadBytes = sum( UploadedBytes), DownloadBytes = sum(DownloadedBytes), Users = make_set(EnrichedUserName), Devices = make_set(MachineName), IPAddresses = make_set(IpAddress) , Apps = make_set(AppName) by EnrichedUserName | extend TotalDevices = array_length(Devices) | extend TotalIPAddresses = array_length(IPAddresses) | extend Totalusers = array_length(Users) | extend TotalApps = array_length(Apps) | extend UploadMB = format_bytes(UploadBytes,0,"MB") | extend TotalTraffic = format_bytes(Totalbytes,0,"MB") | extend DownloadMB = format_bytes(DownloadBytes,0,"MB") | project EnrichedUserName, TotalDevices, TotalIPAddresses, Totalusers,TotalApps, TotalTraffic, UploadMB, DownloadMB, IPAddresses, Devices, Users, Apps ```

Explanation

The first query retrieves shadow discovery information for Microsoft Defender for Cloud Apps, showing the total bytes, upload bytes, download bytes, users, devices, and IP addresses for each application. It also calculates the total devices, total IP addresses, total users, upload in MB, total traffic in MB, and download in MB for each application.

The second query retrieves shadow discovery information for Microsoft Defender for Cloud Apps, showing the total bytes, upload bytes, download bytes, devices, IP addresses, and applications for each user. It also calculates the total devices, total IP addresses, total users, total applications, upload in MB, total traffic in MB, and download in MB for each user.

Details

Alex Verboon

Released: June 4, 2023

Tables

McasShadowItReporting

Keywords

McasShadowItReporting,TimeGenerated,StreamName,Win10EndpointUsers,TotalBytes,UploadedBytes,DownloadedBytes,EnrichedUserName,MachineName,IpAddress,AppName,AppScore,TotalDevices,TotalIPAddresses,Totalusers,UploadMB,TotalTraffic,DownloadMB,Apps

Operators

whereago==summarizesummake_setbyextendarray_lengthformat_bytesproject

KQL Search