Forum Discussion

kishore_soc's avatar
kishore_soc
Copper Contributor
Aug 04, 2021
Solved

Help Needed

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 P...
  • CliveWatson's avatar
    CliveWatson
    Aug 04, 2021

    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-azure-sentinel-how-schedule-a-report/

     

    "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)

GaryBushey's avatar
GaryBushey
Bronze Contributor
Aug 04, 2021

 

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 == "*"

 

  • kishore_soc's avatar
    kishore_soc
    Copper Contributor
    Aug 04, 2021

    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

    • kishore_soc's avatar
      kishore_soc
      Copper Contributor
      Aug 04, 2021
      Could you please help me out with the new query where i can get the expected results...?
      • CliveWatson's avatar
        CliveWatson
        Former Employee
        Aug 04, 2021
        | 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

Resources