topic Re: Trending of top 20 errors comparing previous days errors in Security

Trending of top 20 errors comparing previous days errors

macadminrohit — Fri, 23 Feb 2018 19:45:01 GMT

Hi,

I have a requirement to do a trend of today's top 10 errors and then compare the count of those same top 10 errors from yesterday.

index=servers sourcetype=json Name=* Version=* Id=* level=Error OR Critical | top limit=20 msg this gives me today's top errors. how do i a write subsearch which will actually give me the count of each error comparing from yesterday.

Re: Trending of top 20 errors comparing previous days errors

somesoni2 — Fri, 23 Feb 2018 20:27:16 GMT

Try like this

index=servers sourcetype=json Name= Version= Id=* level=Error OR Critical earliest=-1d@d latest=now 
| eval Period=if(_time<relative_time(now(),"@d"),"Yesterday","Today") 
| chart count over msg by Period 
| sort 20 -Today

The time range is included in the search inline, which select data for today and yesterday.
The output will be top 20 errors based on count of Today. It'll also include column "Yesterday" showing count of same error yesterday.

Re: Trending of top 20 errors comparing previous days errors

macadminrohit — Fri, 23 Feb 2018 23:28:12 GMT

Thanks Man, It works like a champ. Can you explain the logic behind Period=if(_time

Re: Trending of top 20 errors comparing previous days errors

somesoni2 — Sat, 24 Feb 2018 00:02:15 GMT

The Period field is set to value "Yesterday" if they are from yesterday (_time of events is older than midnight today). Its set to "Today" otherwise. The chart command will create columns for each value of field Period with count of events for corresponding msg field.

Re: Trending of top 20 errors comparing previous days errors

macadminrohit — Sun, 25 Feb 2018 02:05:45 GMT

what change should I introduce in the query if I need for 24 hours window, Like comparison between last 24 hours and 24 hours before that.

I tried to change the query but numbers were way off.

Re: Trending of top 20 errors comparing previous days errors

macadminrohit — Tue, 29 Sep 2020 18:11:38 GMT

Also Still trying to understand technically what this statement would do :

_time < relative_time(now(),"@d")

Re: Trending of top 20 errors comparing previous days errors

niketn — Sun, 25 Feb 2018 06:00:13 GMT

@macadminrohit, since there are several cascaded functions in eval. In order to understand you can break down the command to the following first:

relative_time(now(),"@d"): The relative_time() function takes two arguments. First one is the epoch time and second one is the snap to time. In this case it takes the current time using now() function and then snaps to the beginning of the day using @d which is same as -0d@d.

So, essentially using relative_time(), the epoch time for current date midnight is set and using _time < current date mid night we are able to find yesterday's data.

To answer your previous query to compare twp windows i.e. last 48 hours - 24 hours and 24 hrs to now you can try something like the following run anywhere example based on Splunk's _internal index.

index=_internal sourcetype=splunkd log_level!="INFO" earliest=-48h@h latest=now 
| eval Period=if(_time<relative_time(now(),"-24h@h"),"48 to 24 hrs","24 hrs to now") 
| chart count over component by Period

Re: Trending of top 20 errors comparing previous days errors

macadminrohit — Mon, 26 Feb 2018 21:01:15 GMT

Perfect Thanks Niket and Somesh