How to search the top messages in the last 24 hours and count those same messages in the same 24 hour period from the previous week?

[kcanrah]

I'm trying to create a search for the top 15 messages that occurred in the last 24 hours. Then take those top 15 messages and count how many times each of those messages occurred in a 24 hour period the previous week and display it on a column chart.

[sundareshr · Accepted solution]

Try this

EDITED

index=foo sourcetype=bar earliest=-1w@w [search index=foo sourcetype=bar earliest=-24h@h | top 15 field | fields + field ]  | eval dow=strftime(_time, "%a") | where dow=strftime(now(), "%a") | eval period=if(strftime(_time, "%d")=strftime(now(), "%d"), "Today", "Prev Week"))) | chart count over field by period

[kcanrah]

That mostly worked! There are a couple of extra parenthesis after "Prev Week", there should only be one. It currently only gets today's data so the numbers are drastically lower than a full days worth of data. How can I change that to a 24 hour period? Is there a way to make it faster? it took just under 10 minutes to run.

[sundareshr]

Here's the edited version to compare against last 24hrs...

 index=foo sourcetype=bar field=* earliest=-1w@w [search index=foo sourcetype=bar field=* earliest=-24h@h | top 15 field | fields + field ]  | eval dow=strftime(_time, "%a") | where dow=strftime(now(), "%a") | eval period=if(_time>relative_time(now(), "-24h@h"), "Current", "Prev Week"))) | chart count over field by period

To improve the performance... well that's tricky one. This does have a subsearch, which by deseign is inefficient/expensive. One option would be to create a .csv with top 10 list using outputlookup and use that instead of the wide-open search. That would work if the top 10 list could be from yesterday or some other period that gives enough time to pre-run the search and generate the list/

The other option would be look at adding more filter to the base search (main and sub) to try and filter out as much data on the indexer.

If these two fail or are not an option, you will have to look at the job inspector to see what's taking the time and go from there.

EDITED
I edited the subsearch to return only last 24hrs events. Should be much faster now.

[nigelhorne]

Nice idea, but it sticks on "parsing search", so the search doesn't actually work.

[kcanrah]

Thank you for your help!