Visualization Workbooks

%3CLINGO-SUB%20id%3D%22lingo-sub-1735370%22%20slang%3D%22en-US%22%3EVisualization%20Workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735370%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20Community%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20our%20Cloud%20Sentinel%20env%2C%26nbsp%3B%20i%20am%20trying%20to%20build%20workbooks%2C%20Under%20Visualization%20workbooks%20create%20two%20separate%20Incidents%20Column%20and%20build%20on%20Chart%20with%20that%20Incident.%26nbsp%3B%20I%20would%20like%20to%20display%20incident%20from%20separate%20work-spaces%20in%20Separate%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuery%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3ESecurityIncident%3C%2FP%3E%3CP%3E%7C%20take%2020%26nbsp%3B%3C%2FP%3E%3CP%3EUnder%20Visualization%20DEMO%26nbsp%3B%20workbooks%2C%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20that%20All%20alerts%20generated%20on%20sentinel%20displays%20under%20output%20column.%20But%20we%20need%20to%20separate%20both%20core%20alert%20and%20outside%20org%20alert.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1738876%22%20slang%3D%22en-US%22%3ERe%3A%20Visualization%20Workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1738876%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%20for%2C%20you%20want%20to%20be%20able%20to%20have%20one%20column%20for%20those%20incidents%20created%20by%20Azure%20Sentinel%20and%20another%20for%20those%20created%20by%20other%20Azure%20security%20products%20like%20Microsoft%20Cloud%20App%20Security.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20do%20this%2C%20you%20need%20to%20get%20to%20the%20actual%20product%20creation%20which%20is%20hidden%20in%20the%20AdditionalData%20field%20and%20is%20called%20%22alertProductNames%22.%26nbsp%3B%20For%20some%20reason%20this%20is%20stored%20as%20a%20JSON%20array%20so%20need%20to%20extract%20that%20value%20and%20then%20expand%20it%20like%20this%3A%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%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20ProductName%20%3D%20(parse_json(AdditionalData).alertProductNames)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Emv-expand%3C%2FSPAN%3E%3CSPAN%3E%20ProductName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EYou%20can%20then%20use%20the%20ProductName%20field%20to%20determine%20who%20generated%20the%20incident.%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1747205%22%20slang%3D%22en-US%22%3ERe%3A%20Visualization%20Workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747205%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Gary%20Bushey%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%3EI%20applied%20that%20Value%20under%20field%20name%20and%20it's%20works.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%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%20Do%20you%20have%20workbooks%20visualization%20template(not%20in-build%20in%20workbooks%20)%20%3F%20For%20only%20for%20Security%20Incident%20query.%20Just%20want%20to%20explore%20my%20self%20into%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1748708%22%20slang%3D%22en-US%22%3ERe%3A%20Visualization%20Workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1748708%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%3BThe%20only%20one%20I%20have%20is%20the%20one%20that%20comes%20with%20Azure%20Sentinel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1753553%22%20slang%3D%22en-US%22%3ERe%3A%20Visualization%20Workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1753553%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3CDIV%3ESecurityIncident%3CBR%20%2F%3E%7C%20extend%20ProductName%20%3D%20(parse_json(AdditionalData).alertProductNames)%3CBR%20%2F%3E%7C%20mv-expand%20ProductName%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EOn%20Above%20Query%20U%20provided%20earlier%20%2C%20In%20that%20there%20is%20Field(Colum)%20called%20'Owner'%3C%2FDIV%3E%3CDIV%3EQuestion%20-%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EHere%2C%20Is%20it%20possible%20only%20shows%20'UserprincipalName'%20or%20'AssignedTO'%26nbsp%3B%20Or%20'%20Email'%20.%26nbsp%3B%20Only%20Need%20One%20Field.%26nbsp%3B%20Can%20you%20please%20provide%20updated%20query%3F%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7B%22userPrincipalName%22%3Anull%2C%22assignedTo%22%3Anull%2C%22objectId%22%3Anull%2C%22email%22%3Anull%7D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EAgain%2C%20thanks%20in%20Advance.%20%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hey Community, 

 

On our Cloud Sentinel env,  i am trying to build workbooks, Under Visualization workbooks create two separate Incidents Column and build on Chart with that Incident.  I would like to display incident from separate work-spaces in Separate column. 

 

Query : 

SecurityIncident

| take 20 

Under Visualization DEMO  workbooks, 

On that All alerts generated on sentinel displays under output column. But we need to separate both core alert and outside org alert.  

10 Replies

@Vshah335 If I understand what you are asking for, you want to be able to have one column for those incidents created by Azure Sentinel and another for those created by other Azure security products like Microsoft Cloud App Security.

 

To do this, you need to get to the actual product creation which is hidden in the AdditionalData field and is called "alertProductNames".  For some reason this is stored as a JSON array so need to extract that value and then expand it like this:

 

SecurityIncident
| extend ProductName = (parse_json(AdditionalData).alertProductNames)
| mv-expand ProductName
 
You can then use the ProductName field to determine who generated the incident.

Thanks Gary Bushey @Gary Bushey 

 

I applied that Value under field name and it's works. 

 

@Gary Bushey  Do you have workbooks visualization template(not in-build in workbooks ) ? For only for Security Incident query. Just want to explore my self into it. 

 

 

@Vshah335 The only one I have is the one that comes with Azure Sentinel.

@Gary Bushey 

 

SecurityIncident
| extend ProductName = (parse_json(AdditionalData).alertProductNames)
| mv-expand ProductName
 
On Above Query U provided earlier , In that there is Field(Colum) called 'Owner'
Question - 
Here, Is it possible only shows 'UserprincipalName' or 'AssignedTO'  Or ' Email' .  Only Need One Field.  Can you please provide updated query? 
{"userPrincipalName":null,"assignedTo":null,"objectId":null,"email":null}
 
Again, thanks in Advance.

@Vshah335 In the query below, you can then use ProductName.alertProductNames or ProductName.Owner or any other entry that is part of the AdditionalData field to get its data.

 

SecurityIncident
| extend ProductName = parse_json(AdditionalData)

@Gary Bushey 

 

SecurityIncident
| extend ProductName = parse_json(AdditionalData, ProductNames.owner)
| mv-expand AdditionalData = " email " 

 

Or 

SecurityIncident
| extend ProductName = parse_json(AdditionalData, ProductNames.owner)
| where AdditionalData = " email " 

 

I am running both query, but throw me error.  Any Idea ? 

@Vshah335 Needs to be more like

 

SecurityIncident
|extend ProductName = parse_json(AdditionalData)
| project ProductName.alertProductNames
 
where "alertProductNames" is an entry that inside of the AdditionalData field.
Hey @Gary Bushey 



I attached screen shot for need to Pharse down field called "Owner"



there are  more elemnets,  "Assign to" Userprinciplename" ,  " Object ID" 

I tried  Query u provided earlier, but won't get results what we need. 

(Output only need on OWNER coloum  "AssignTO " ) 



I hope you understand my question. 

@Vshah335 

Have you tried this?  Also I don't see the screenshot you said you supplied.

 

SecurityIncident
| extend  assignedTo_ = tostring(Owner.assignedTo),
                        userPrincipalName_ = tostring(Owner.userPrincipalName),
                        email_ = tostring(Owner.email)
| where isnotempty (assignedTo_)
| project assignedTo_, userPrincipalName_, email_

 

An example I use:

SecurityIncident
| where TimeGenerated > ago(7d)
| summarize arg_max(LastModifiedTime,*) by tostring(IncidentNumber)
| extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
| mv-expand AlertIds to typeof(string)
| join 
(
    SecurityAlert
    | extend AlertEntities = parse_json(Entities)
    | mv-expand AlertEntities
) on $left.AlertIds == $right.SystemAlertId
| summarize AlertCount=dcount(AlertIds),
            entityList=make_set(tostring(AlertEntities.Type)) by IncidentNumber,
            Status,
            Title,
            Alerts,
            IncidentUrl,
            Owner=tostring(Owner.userPrincipalName),
            assignedTo = tostring(Owner.assignedTo),
            email = tostring(Owner.email),
            product = tostring(parse_json(tostring(AdditionalData.alertProductNames))[0]),
            Tactics =tostring(AdditionalData.tactics)
| project IncidentNumber, Status, AlertCount,Owner, assignedTo, email,  product, Title, Alerts, entityList, Tactics, IncidentUrl
| order by IncidentNumber desc

 

Screenshot 2020-10-14 085658.jpg

@CliveWatson 

Thanks a lot. I got output.