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 people when they want it. I'm trying to pull the data of apps that are considered as sanctioned, unsanctioned, unknown, and a few custom tags that has bee created. I'm having an issue with the kusto language of trying to pull all that data with one query. I'm able to get the information for one, but when I try to pull the other app tags, that is where I run into problems and the numbers get combined instead of individual count of the applications for the app tag. The code I have below is what works for looking for Sanctioned, but when I try to get sanctioned and unsanctioned, it combines them together. Any help would be greatly appreciated

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 tostring(App_Tag ="Sanctioned")

 

azure monitor

2 Replies

  • 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