Exclude IN Azure Sentinel

%3CLINGO-SUB%20id%3D%22lingo-sub-2164076%22%20slang%3D%22en-US%22%3EExclude%20IN%20Azure%20Sentinel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2164076%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20this%20query%20for%20segregation%20of%20all%20Azure%20services%20coming%20into%20the%20sentinel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3ESecurityIncident%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EProduct%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etodynamic%3C%2FSPAN%3E%3CSPAN%3E((%3C%2FSPAN%3E%3CSPAN%3Eparse%3C%2FSPAN%3E%3CSPAN%3E_js%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EAdditi%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3EalData%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3EalertProductname%3C%2FSPAN%3E%3CSPAN%3E))%3C%2FSPAN%3E%3CSPAN%3E%5B0%5D%3C%2FSPAN%3E%3CSPAN%3E))%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EAdditi%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3EalData%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3EalertProductNames%3C%2FSPAN%3E%3CSPAN%3E))%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esort%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E_%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EBackgoround%20-%20In%20our%20incident%20dashboard%20all%20different%20teams%20incident%20shows%26nbsp%3B%20into%20centrally.%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EQuestion%20-%26nbsp%3B%20Some%20incident%20we%20short%20by%20the%20title%20name.%20if%20we%20are%20using%20same%20query%20to%20exclude%20some%20title%20names%20won't%20shows%20me%20rest%20summerize%20services%20count.%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Ei%20hope%20you%20understoood.%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3EAny%20idea%20%3F%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2165626%22%20slang%3D%22en-US%22%3ERe%3A%20Exclude%20IN%20Azure%20Sentinel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2165626%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F786266%22%20target%3D%22_blank%22%3E%40Vshah335%3C%2FA%3E%26nbsp%3BIf%20I%20understand%20what%20you%20are%20asking%20the%20following%20code%20will%20work.%26nbsp%3B%20It%20will%20only%20show%20those%20items%20that%20are%20NOT%20in%20the%20listing%20of%20products.%20In%20this%20case%20%22Microsoft%20Cloud%20App%20Security%22%20will%20not%20show%20up.%26nbsp%3B%20I%20removed%20the%20extend%20line%20since%20it%20was%20not%20being%20used.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecurityIncident%3CBR%20%2F%3E%7C%20where%20tostring(AdditionalData.alertProductNames%5B0%5D)%20!in%20(%22Microsoft%20Cloud%20App%20Security%22)%3CBR%20%2F%3E%7C%20summarize%20count()%20by%20(tostring(AdditionalData.alertProductNames))%3CBR%20%2F%3E%7C%20sort%20by%20count_%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2165912%22%20slang%3D%22en-US%22%3ERe%3A%20Exclude%20IN%20Azure%20Sentinel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2165912%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20quick%20responses.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46875%22%20target%3D%22_blank%22%3E%40Gary%20Bushey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20run%20this%20it%20provide%20all%20product%20names.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESecurityIncident%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20tostring(AdditionalData.alertProductNames%5B0%5D)%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20i%20need%20all%20product%20name%20but%20in%20Azure%20Sentien%20i%20seprate%20some%20incidnet%20by%20Tittle.%20I%20don't%20want%20count%20in%20Summerize%20function.%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EEnd%20Results%20will%20be%20All%20produt%20count%20show%20up%20as%20it%20is.%20but%20in%20Azure%20Sentienl%20product%20total%20count%20by%20seprated%20by%20Title%20.%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20when%20i%20run%20this%20query%20won't%20get%20results%20what%20i%20am%20loooking%20for%20.%26nbsp%3B%20It%20only%20Showing%20me%20Azure%20Sentinel%20not%20other%20product%20count.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

 

I am using this query for segregation of all Azure services coming into the sentinel. 

 

SecurityIncident
extend Product = todynamic((parse_json(tostring(AdditionalData.alertProductname))[0]))
summarize count() by (tostring(AdditionalData.alertProductNames))
|  sort by count_
 
Backgoround - In our incident dashboard all different teams incident shows  into centrally. 
Question -  Some incident we short by the title name. if we are using same query to exclude some title names won't shows me rest summerize services count. 
 
i hope you understoood. 
Any idea ? 

 

5 Replies

@Vshah335 If I understand what you are asking the following code will work.  It will only show those items that are NOT in the listing of products. In this case "Microsoft Cloud App Security" will not show up.  I removed the extend line since it was not being used.

 

SecurityIncident
| where tostring(AdditionalData.alertProductNames[0]) !in ("Microsoft Cloud App Security")
| summarize count() by (tostring(AdditionalData.alertProductNames))
| sort by count_

Thanks for the quick responses. @Gary Bushey 

 

 

If I run this it provide all product names. 

SecurityIncident
| where tostring(AdditionalData.alertProductNames[0]) 

 

So i need all product name but in Azure Sentien i seprate some incidnet by Tittle. I don't want count in Summerize function.  


End Results will be All produt count show up as it is. but in Azure Sentienl product total count by seprated by Title . 

but when i run this query won't get results what i am loooking for .  It only Showing me Azure Sentinel not other product count. 

 

 

@Vshah335 OK, I think I know what you want now.  You want to see all the incidents, but if they were not generated from Azure Sentinel you just want to get a total number of those incidents rather than seeing each one.  Is that correct?

 

IF that is correct, I don't think you can do it with one query.  You can write a query to show all the incidents and then inside a Workbook display them as a grid and then group by the ProviderName field.  This will show the count and then you can expand the Azure Sentinel one to see all the incidents.

How about this?

SecurityIncident
| extend product = tostring(AdditionalData.alertProductNames[0])
| where product !in ("Microsoft Cloud App Security")
| extend summarizeby = iff(product == "Azure Sentinel", Title, product)
| summarize count() by summarizeby
| sort by count_
@Ofer_Shezaf Great piece of KQL code!