Forum Discussion

kishore_soc's avatar
kishore_soc
Copper Contributor
Aug 04, 2021

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 ProductName contains "Azure Defender OR Azure Active Directory Identity Protection"
| where AlertName = "*"

 

Thanks,

Kishore

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

Share