Forum Discussion

Metzinger35's avatar
Metzinger35
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's avatar
CliveWatson
Silver Contributor
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's avatar
    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.

     

     

Resources