Forum Discussion

CharlieK95's avatar
CharlieK95
Copper Contributor
Apr 28, 2023

Possibly "Tricky" KQL for Alerts over Time / Split into a Comparison

Hey!

 

I hope you're doing well :smile:

I've been pulling my hair out over some what I think might be impossible, or tricky KQL (but then again - I could be missing something). I have a report that does a comparison of the last 30 days, and last 60 days of alerts received via. Sentinel. The query I've been using to get two tables individually was the below;

 

SecurityIncident 
| where CreatedTime > ago(30d)
// the comparison uses | where CreatedTime < ago(30d) | where CreatedTime > ago(60d)
| summarize count(), arg_max(TimeGenerated,*) by IncidentNumber 
| sort by IncidentNumber desc
| summarize count() by bin(TimeGenerated,1d)

 

When put into "chart view", this does emit the correct values and works fine - but combining the two into a comparison as such, from what I can tell, seems impossible. I've also tried to leverage PowerBI to do this too but again, have spent hours looking into it and can't figure out how to do it. 

 

Has anyone managed to do this before, or could anyone give me any advice to achieve something that would look like the below?

 

 

Any help would be appreciated as always 🙂

 

alerts
analytics
Dashboards
integration
KQL
Log Data

2 Replies

  • GBushey's avatar
    GBushey
    Former Employee
    May 01, 2023
    I haven't worked through this entirely, but have you looked at the table merge feature in Workbooks? You could write two different queries to get the data you need and then merge them into a third workbook query.
    • Clive_Watson's avatar
      Clive_Watson
      Bronze Contributor
      May 02, 2023
      I'm not sure its possible, the closest I have come to this is to position a Chart above a chart in a workbook, or do a side-by-side compare, e.g.

      SecurityIncident
      | summarize 30days_=countif(TimeGenerated between (startofday(ago(30d)) .. now())),
      60days_=countif(TimeGenerated between (ago(60d) .. endofday(ago(31d))))
      by bin(TimeGenerated,1d )
      | render columnchart