Cloud App Security data in Azure Sentinel logs

%3CLINGO-SUB%20id%3D%22lingo-sub-2870506%22%20slang%3D%22en-US%22%3ECloud%20App%20Security%20data%20in%20Azure%20Sentinel%20logs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2870506%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20pull%20AppTags%20from%20Cloud%20App%20Security%20from%20the%20Azure%20Sentinel%20logs%20using%20the%26nbsp%3BMcasShadowItReporting%20table%20to%20create%20a%20Power%20BI%20report%20so%20that%20these%20metrics%20can%20be%20available%20to%20people%20when%20they%20want%20it.%20I'm%20trying%20to%20pull%20the%20data%20of%20apps%20that%20are%20considered%20as%20sanctioned%2C%20unsanctioned%2C%20unknown%2C%20and%20a%20few%20custom%20tags%20that%20has%20bee%20created.%20I'm%20having%20an%20issue%20with%20the%20kusto%20language%20of%20trying%20to%20pull%20all%20that%20data%20with%20one%20query.%20I'm%20able%20to%20get%20the%20information%20for%20one%2C%20but%20when%20I%20try%20to%20pull%20the%20other%20app%20tags%2C%20that%20is%20where%20I%20run%20into%20problems%20and%20the%20numbers%20get%20combined%20instead%20of%20individual%20count%20of%20the%20applications%20for%20the%20app%20tag.%20The%20code%20I%20have%20below%20is%20what%20works%20for%20looking%20for%20Sanctioned%2C%20but%20when%20I%20try%20to%20get%20sanctioned%20and%20unsanctioned%2C%20it%20combines%20them%20together.%20Any%20help%20would%20be%20greatly%20appreciated%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EMcasShadowItReporting%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(30d)%0A%7C%20where%20StreamName%20has%20%22Global%20view%22%0A%7C%20where%20AppTags%20has%20%22sanctioned%22%0A%7C%20project%20App_Tag%20%3D%20column_ifexists(%22AppTags%22%2C%20%22%22)%2C%20App_Name%20%3D%20column_ifexists(%22AppName%22%2C%20%22%22)%0A%7C%20summarize%20Count%20%3D%20toint(dcount(App_Name))%20by%20tostring(App_Tag%20%3D%22Sanctioned%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2873012%22%20slang%3D%22en-US%22%3ERe%3A%20Cloud%20App%20Security%20data%20in%20Azure%20Sentinel%20logs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2873012%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F608134%22%20target%3D%22_blank%22%3E%40Metzinger35%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EMcasShadowItReporting%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(30d)%0A%7C%20where%20StreamName%20has%20%22Global%20view%22%0A%2F%2F%7C%20where%20AppTags%20has%20%22sanctioned%22%0A%7C%20project%20App_Tag%20%3D%20column_ifexists(%22AppTags%22%2C%20%22%22)%2C%20App_Name%20%3D%20column_ifexists(%22AppName%22%2C%20%22%22)%0A%7C%20summarize%20Count%20%3D%20toint(dcount(App_Name))%20by%20App_Tag%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3BI%20don'f%20have%20this%20Table%20but%20wouldn't%20you%20just%20let%20the%20summarize%20by%20use%20the%20App-Tag%20or%20App_name%20to%20get%20a%20count%26nbsp%3B%20(are%20you%20sure%20you%20need%20dcount()%20rather%20than%20count()%20%3F).%26nbsp%3B%20%3CBR%20%2F%3EMy%20test%20is%20this%20-%26nbsp%3B%20where%20the%20count%20is%20all%20occurrences%20of%20the%20app%20name%20BY%20the%20app_tag.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3EUsage%0A%7C%20project%20App_Tag%20%3D%20column_ifexists(%22DataType%22%2C%20%22%22)%2C%20App_Name%20%3D%20column_ifexists(%22Solution%22%2C%20%22%22)%0A%7C%20summarize%20Count_%20%3D%20toint(count(App_Name))%20by%20App_Tag%0A%7C%20top%205%20by%20Count_%20desc%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EApp_Tag%3C%2FTH%3E%0A%3CTH%3ECount_%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EOperation%3C%2FTD%3E%0A%3CTD%3E46%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3ESecurityEvent%3C%2FTD%3E%0A%3CTD%3E37%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3ESyslog%3C%2FTD%3E%0A%3CTD%3E37%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EStorageBlobLogs%3C%2FTD%3E%0A%3CTD%3E37%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EInsightsMetrics%3C%2FTD%3E%0A%3CTD%3E37%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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")

 

2 Replies

@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

@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.

Metzinger35_0-1634908633969.png