Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

How to export incidents in azure sentinel

Copper Contributor

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.

 

Thanks

21 Replies

@Pavan_Gelli would be doing a query against SecurityAlert work?  It shows the alerts but not the actual incidents but the numbers should be close if you don't need the exact information from the incident. If you query in the Logs screen you can export your results.

@Pavan_Gelli Once you have the KQL query you want, run it and then choose the Export menu. Is this what you're talking about?

 

export.jpg

I want close all key vault incidents. But i dont see way to do this using PS @Gary Bushey 

@Pavan_Gelli, you've no doubt solved this problem since late 2019, but for everyone else who finds this entry at the top of their Google search and needs an up-to-date answer, use this KQL code to generate a list of Sentinel incidents:

 

 

SecurityIncident
| summarize LatestEntry = arg_max(TimeGenerated, *) by IncidentNumber
| project IncidentNumber, LatestEntry, Title, Description, Severity, Status, Classification, ClassificationComment, ModifiedBy
| sort by IncidentNumber

 

 

Adjust the Date Range appropriately, adjust the columns you want to see in the project statement.  Then export the results to .csv

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

@KheenanH 

 

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

@KheenanH 

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.

Clive_Watson_0-1699977857690.png


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)



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

I looked at my previous export and that message "Connection to a custom network indicator" appears to be ok. I just have so many results that it felt broken. It looks to be fine. Is it possible to keep the CompromisedEntity?

How can we keep this format and yet let me pick the date range for the query?

 

update - got the entity part, is there a completed//solved column?

@KheenanH 

 

A date range you can do with a "between" here is an example

SecurityAlert
//| where TimeGenerated > ago(5h)
| where TimeGenerated between( datetime(2023-11-01 09:00) .. datetime(2023-11-02 09:00) )

Please see a blog I did for other examples:
https://techcommunity.microsoft.com/t5/microsoft-sentinel-blog/how-to-align-your-analytics-with-time...



You can add "compromisedenity" to the summarise

e.g.

| summarize AlertCount=dcount(AlertIds), arg_max(TimeGenerated,Title,
Severity,
Status,
Owner,
ModifiedBy,
CreatedTime,
FirstModifiedTime,
LastModifiedTime,
ProductName,
CompromisedEntity,
Tags= tostring(parse_json(Labels).labelName),
Comments=tostring(parse_json(Comments).message))
by IncidentNumber

this is amazing. Thank you for helping me out. I think I can start to really look at this more to get what we need. I really do appreciate all your help

@Clive_Watson are you able to help with a different type of question related to this? 

Its always best to ask, someone will answer (I will if I can and if I'm available).
ok, will do. This is not a query issue. The issue I am having is that ALL Azure sentinel logs Status field show as NEW. Even if they are resolved. The rest of the products listed in the log exported will show in progress or resolved or new. Sentinel simply just shows NEW regardless. What could be happening here?

@KheenanH 

 

I'm not seeing that behaviour (I checked three workspaces in 3 regions)

Clive_Watson_0-1701448534364.png

Do you have an Automation Rule changing them back to "new"?

In looking at the query language there was a Status1. I added that and now Sentinel now shows closed @Clive_Watson 

KheenanH_0-1701448942790.png