Help Needed

[kishore_soc]

Hi Community,

I need to fetch the last 24 hours alerts on sentinel as a report. I have used the below query but that didn't work. can some one help me out on this...?

SecurityAlert
| where ProductName contains "Azure Defender OR Azure Active Directory Identity Protection"
| where AlertName = "*"

Thanks,

Kishore

[#2]

@kishore_soc There are a couple of things wrong with your query

1) The "OR" that you have in contains should not be inside the quotes

2) It does not look like you need to use contains since you are looking for the exact string that would show up.  Use == instead 

3) The AlertName comparison requires two equal signs, ==, to act as a comparison rather than trying to set a value.   Not sure that you want it to equal "*" either, as I don't see that value listed, but what is it you are trying to accomplish with this last line?

I rewrote your query to look like

SecurityAlert
| where ProductName == "Azure Defender" or ProductName == "Active Directory Identity Protection"
| where AlertName == "*"

[#3]

Hi Gary,
Thanks for you prompt replay...!
I did not get any results by using the provided query. Please have a look into the below screen shot.

Thanks,

Kishore

[#4]

Could you please help me out with the new query where i can get the expected results...?

[#5]

| where AlertName = "*"

As Gary mentioned is looking for an Alert named "*", its not doing a wildcard if that's what you are trying to do?

This would be enough to show all alerts

SecurityAlert
| where ProductName == "Azure Defender" or ProductName == "Azure Active Directory Identity Protection"

or if you need to filter on a specific AlertName or names?

SecurityAlert
| where ProductName == "Azure Defender" or ProductName == "Azure Active Directory Identity Protection"
| where AlertName == "Atypical travel" or AlertName startswith "Unfamiliar"

or

SecurityAlert
| where ProductName == "Azure Defender" or ProductName == "Azure Active Directory Identity Protection"
| summarize count() by AlertName

[#6]

@CliveWatson Thanks. it's working fine...!

Could you please help me out how to schedule a report for the last 24 hours alert results on daily basis...? including analyst actions and comments. when i go with the Owner entity on the logs tab it doesn't showing any. Please look into the below screenshot.

Thanks,

Kishore

[#7]

@kishore_soc There is no column called Owner in the SecurityAlert table which is why you are getting the error with the project-keep command.

[#8]

Is there any other column name which can show the assigned analysts names in the results...?

[#9 · Accepted solution]

@kishore_soc 

Scheduling a report - please see the "playbook" example here https://cloudblogs.microsoft.com/industry-blog/en-gb/cross-industry/2020/06/17/log-analytics-or-azur...

"Owner" is a column in SecurityIncident not SecurityAlert - hence the failure, so you need to join the two Tables

SecurityIncident
| summarize arg_max(TimeGenerated,*) by tostring(IncidentNumber)
| extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
| mv-expand AlertIds to typeof(string)
| join 
(
    SecurityAlert
    | where ProductName == "Azure Defender" or ProductName == "Azure Active Directory Identity Protection"
    | extend AlertEntities = parse_json(Entities)
    | mv-expand AlertEntities
)  on $left.AlertIds == $right.SystemAlertId
| summarize by IncidentNumber, tostring(Owner), Title, Alerts, DisplayName, IncidentSeverity=Severity, AlertSeverity, Status

Add this at the end of the last line for comments and tags (if required)

, tostring(Comments), tags=tostring(Labels)