Forum Discussion
How to export incidents in azure sentinel
Hi Team, I have need to export the incidents to excel. Is this possible ? Basically i want to summarize the no of incidents triggered for curtain time period and do further analysis on this. ...
MickTravels This is a really good start. Is there a column field that can get where the alert came from? There is modified by but if i modify the alert to close it then puts my name vs Defender or Sentinel. Also be good to know what other fields people use in this query.
SecurityAlert
| where TimeGenerated > ago(5h)
| join
(
SecurityIncident
| extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
| mv-expand AlertIds to typeof(string), Labels to typeof(string), Comments to typeof(string), AdditionalData to typeof(string), Owner to typeof(string)
) on $left.SystemAlertId == $right.AlertIds
| summarize AlertCount=dcount(AlertIds), arg_max(TimeGenerated,Title,
Severity,
Status,
Owner,
ModifiedBy,
CreatedTime,
FirstModifiedTime,
LastModifiedTime,
ProductName,
Tags= tostring(parse_json(Labels).labelName),
Comments=tostring(parse_json(Comments).message))
by IncidentNumber
| extend IncidentSource = case(
ProductName == "Azure Sentinel", "Analytic",
ProductName == "Azure Active Directory Identity Protection", "Azure AD Identity Protection",
ProductName == "Azure Security Center", "Microsoft Defender for Cloud",
ProductName == "Microsoft Defender Advanced Threat Protection", "Microsoft Defender for Endpoint",
ProductName == "Microsoft Cloud App Security", "Microsoft Defender for Cloud Apps",
ProductName == "Office 365 Advanced Threat Protection", "Microsoft Defender for Office 365",
ProductName == "Azure Advanced Threat Protection", "Microsoft Defender for Identity",
ProductName)
- ok so some follow up to this, and this is really good stuff
The Incident number does not appear to be showing even though I see it referenced in your query above.
i also get Connection to a custom network indicator" in the DisnplayName and AlertName field.
Do you know what may be causing this. I do apologize that i am not familiar with query writing so please if these seem silly its because i do not know how to do it- if you can help me get it to show incident number/entry/title properly then all the columns it currently shows in the above would be perfect as i can modify what i see in excel.
- Can you share a screen shot of what you see please? Please edit out in PII data etc...
Did you use the latest version I posted (I did an update to it after my initial reply)? In my systems I see this result.
Code re-posted just in case (you may need to change line 2 if you haven't had any incidents in the past 5hrs):SecurityAlert | where TimeGenerated > ago(5h) | join ( SecurityIncident | extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds)) | mv-expand AlertIds to typeof(string), Labels to typeof(string), Comments to typeof(string), AdditionalData to typeof(string), Owner to typeof(string) ) on $left.SystemAlertId == $right.AlertIds | summarize AlertCount=dcount(AlertIds), arg_max(TimeGenerated,Title, Severity, Status, Owner, ModifiedBy, CreatedTime, FirstModifiedTime, LastModifiedTime, ProductName, Tags= tostring(parse_json(Labels).labelName), Comments=tostring(parse_json(Comments).message)) by IncidentNumber | extend IncidentSource = case( ProductName == "Azure Sentinel", "Analytic", ProductName == "Azure Active Directory Identity Protection", "Azure AD Identity Protection", ProductName == "Azure Security Center", "Microsoft Defender for Cloud", ProductName == "Microsoft Defender Advanced Threat Protection", "Microsoft Defender for Endpoint", ProductName == "Microsoft Cloud App Security", "Microsoft Defender for Cloud Apps", ProductName == "Office 365 Advanced Threat Protection", "Microsoft Defender for Office 365", ProductName == "Azure Advanced Threat Protection", "Microsoft Defender for Identity", ProductName)