How to edit my search to alert when there is a certain percentage increase in all events when comparing today to yesterday?

[jwalzerpitt]

I'm trying to write a search that I can convert into an alert that will trigger when there's an X% increase when compared to yesterday. I'm looking for surges in the total count of a certain message I'm tracking. My search is as follows, which is looking for a 50% increase :

index=xxx SenderAddress="abc@xyz.com" "FYI: message Text" earliest=-1d@d latest=@d | dedup user | stats count as Yesterday | appendcols [search index=xxx SenderAddress="abc@xyz.com" "FYI: message Text" earliest=@d latest=now | stats count as Today ] | where Today>=0.5*Yesterday

1) Is there a better way/search to track a surge above a certain %? Would something like a Z-score query be better?
2) If this search is solid way to track a surge above a certain %, any suggestions to improve it?

Thx

[somesoni2]

I would write the query this way

index=xxx SenderAddress="abc@xyz.com" "FYI: message Text" earliest=-1d@d latest=now | eval period=if(_time>=relative_time(now(),"@d"),"Today","Yesterday") | chart dc(user) over index by period 
| where Today>=0.5*Yesterday

Ques: There is no dedup in the query for today in your quest. Is that correct or typo?

[jwalzerpitt]

Thx for the reply.

I'm looking for a % increase against all events, not just for the user. I am using | dedup user to reduce when a user makes more than one change as want only one change per user when I'm looking at the total number of events for that specific message.

[somesoni2]

If you're looking for surge in the count, why dedup (or why not dedup in both the queries)?
%percent increase will make more sense if both counts are measured in the same way.

[jwalzerpitt]

I missed adding the | dedup user to the second query - thx for catching

[somesoni2]

So did the above query gave you what you need? (hoping that it might be performing better as well)

[jwalzerpitt]

Based on your query I now have:

index=xxx SenderAddress="abc@xyz.com" "FYI: message Text" earliest=-1d@d latest=now | eval period=if(_time>=relative_time(now(),"@d"),"Today","Yesterday") | chart dc(user) over index by period | where Today>=0.5*Yesterday

If you don't mind, can you break the query down for my edification? Unclear on the effect of moving away from total count to count by user works.

Thx

[somesoni2]

QUery first selects data using base search from yesterday midnight to now (whole of yesterday to now) and then sets period to yesterday OR today based on value of _time compared with current time. Previously you were doing dedup on users on each search to have 1 records per user, means number of results will be same as number of unique users. Here I'm calculating the same based on period (number of unique users yesterday vs number of unique users today) . I have used field index in the chart command to get count of today and yesterday as column, so that you can compare. Else they will come in different rows and you'll need other commands to get them in same row to compare.