Dec 17 2019
05:44 AM
- last edited on
Dec 23 2021
10:17 AM
by
TechCommunityAP
Dec 17 2019
05:44 AM
- last edited on
Dec 23 2021
10:17 AM
by
TechCommunityAP
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
Dec 17 2019 08:07 AM
@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.
Dec 19 2019 04:45 AM - edited Jan 03 2020 01:00 PM
@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?
Jun 07 2023 01:35 PM - edited Jun 07 2023 01:36 PM
@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
Nov 14 2023 06:56 AM
@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.
Nov 14 2023 07:17 AM - edited Nov 14 2023 07:22 AM
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)
Nov 14 2023 07:59 AM
Nov 14 2023 08:07 AM
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)
Nov 14 2023 08:08 AM
Nov 14 2023 08:10 AM
Nov 14 2023 08:23 AM - edited Nov 14 2023 08:32 AM
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?
Nov 14 2023 08:36 AM
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
Nov 14 2023 08:41 AM
Dec 01 2023 08:05 AM
@Clive_Watson are you able to help with a different type of question related to this?
Dec 01 2023 08:11 AM
Dec 01 2023 08:15 AM
Dec 01 2023 08:39 AM
I'm not seeing that behaviour (I checked three workspaces in 3 regions)
Do you have an Automation Rule changing them back to "new"?
Dec 01 2023 08:42 AM
In looking at the query language there was a Status1. I added that and now Sentinel now shows closed @Clive_Watson
Mar 08 2024 08:39 AM
@Clive_Watson i am back at this again.
ive been poking around for a few days. ive found that i am unable to find where it stores any notes we enter whether its when we resolve it or not. Also is there a way to get the classification reason out of here?
Mar 08 2024 09:18 AM
This will get you "all" columns. You can when you are happy, replace the "*" on line 11 with a list of the specific columns you need.
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)
| join kind=inner
(
SecurityAlert
) on $right.SystemAlertId == $left.AlertIds
| summarize AlertCount=dcount(AlertIds),
arg_max
(
TimeGenerated, *
)
by IncidentNumber
Including the three for Classification