Forum Discussion

Copper Contributor

Oct 21, 2021

Cloud App Security data in Azure Sentinel logs

Hello, I'm trying to pull AppTags from Cloud App Security from the Azure Sentinel logs using the McasShadowItReporting table to create a Power BI report so that these metrics can be available to peo...

azure monitor

CliveWatson

Former Employee

Oct 22, 2021

Metzinger35

McasShadowItReporting
| where TimeGenerated > ago(30d)
| where StreamName has "Global view"
//| where AppTags has "sanctioned"
| project App_Tag = column_ifexists("AppTags", ""), App_Name = column_ifexists("AppName", "")
| summarize Count = toint(dcount(App_Name)) by App_Tag

I don'f have this Table but wouldn't you just let the summarize by use the App-Tag or App_name to get a count (are you sure you need dcount() rather than count() ?).
My test is this - where the count is all occurrences of the app name BY the app_tag.

Usage
| project App_Tag = column_ifexists("DataType", ""), App_Name = column_ifexists("Solution", "")
| summarize Count_ = toint(count(App_Name)) by App_Tag
| top 5 by Count_ desc

App_Tag	Count_
Operation	46
SecurityEvent	37
Syslog	37
StorageBlobLogs	37
InsightsMetrics	37

Metzinger35

Copper Contributor

Oct 22, 2021

CliveWatson

Thanks for the help. I did end up using dcount() rather than count() to get the correct count. Since the AppTags is dynamic, the AppName can be in multiple AppTags. Example: Sanctioned and Microsoft or Sanctioned and Vendor, I would like to know the unique app that would be sanctioned.

Current Code:

McasShadowItReporting
| where TimeGenerated > ago(30d)
| where StreamName has "Global view"
| project App_Tag = column_ifexists("AppTags", ""), App_Name = column_ifexists("AppName", "")
| summarize Count = toint(dcount(App_Name)) by tostring(App_Tag)
| top 6 by Count desc

Result:

App_Tag	Count
["Unknown Use"]	4,333
[]	2,596
["sanctioned","Vendor"]	107
["unsanctioned"]	102
["sanctioned"]	50
["sanctioned","Microsoft"]	49

Within the Cloud App Security GUI, I'm able to get the number of apps that have the App Tag of Sanctioned even if the App is tagged as something else.