Forum Discussion

Porter76's avatar
Porter76
Brass Contributor
Sep 22, 2023

Help with a query to count

Trying to create a query that will count all of the diffrent ruleid's over the past week but having a hard time. Any help appreciated.   Thanks!
kusto
siem
Clive_Watson's avatar
Clive_Watson
Bronze Contributor
Sep 22, 2023

Porter76 

 

Its will be similar to this, you'll have to amend lines 1 & 2 to match your Table and Columns 

 

AzureActivity
| extend ruleID = tostring(parse_json(Properties).activitySubstatusValue)
| summarize count() by ruleID

 

Porter76's avatar
Porter76
Brass Contributor
Sep 25, 2023

Thanks so much Clive, that worked like a charm.
Is it possible to create an alert in log analytics whenever the count for a particular WAF rule being triggered exceeds a certain threshold in a given time frame?

i.e. if the count for "AWSManagedRulesAnonymousIpList" was typically 1000 in an hour and spiked to 15000, how can I alert on this?

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Sep 25, 2023

    Porter76 

     

    AWSCloudTrail
| where TimeGenerated > ago(1h)
//| summarize count() by EventSource
| count
| where Count > 1000

    or

    AWSCloudTrail
| where TimeGenerated > ago(1d)
| summarize countPerHour=count() by EventSource, bin(TimeGenerated,1h)
| where countPerHour > 1000

     

    • Porter76's avatar
      Porter76
      Brass Contributor
      Sep 26, 2023
      Thanks Clive, could you explain the difference bewteen the 2 here? How would I apply this to a specific ruleid?

Resources