topic How to compare data between two tables from 2 different time periods? in Splunk Search

How to compare data between two tables from 2 different time periods?

trunghung — Tue, 23 Feb 2016 03:52:06 GMT

I have a Splunk search that generates a table of errors. Each row is an error entry. I would like to compare the change of the entries from 2 time periods (24-48hrs ago vs last 24hrs) for spike in errors. Is it possible to do so with Splunk? thanks

Re: How to compare data between two tables from 2 different time periods?

fdi01 — Tue, 23 Feb 2016 09:21:59 GMT

you can send your query ?

Re: How to compare data between two tables from 2 different time periods?

chimell — Tue, 23 Feb 2016 09:38:29 GMT

HI
try with two subsearches join with appendcols command.just complete the following search code

............. earliest=-(24-48)h | ..............|appendcols[search ............. earliest=-24h@h | ..............]

Re: How to compare data between two tables from 2 different time periods?

trunghung — Tue, 29 Sep 2020 08:51:49 GMT

So I tried

but it only show the data from the first query.

Re: How to compare data between two tables from 2 different time periods?

somesoni2 — Tue, 23 Feb 2016 18:26:05 GMT

Try something like this

index=tto* sourcetype=access "Get wtt_err HTTP/1.1" NOT Error=111 earliest=-48h@h latest=now | dedup client_ip | eval Period=if(_time<relative_time(now(),"-24h@h"),"Prior","Current")| chart  count over Error by Period

This should give you 3 columns - Error, Current (Count for last 24 hr) , Prior (Count for lat 48hr to 24 hr)

You can then compare your data for column Current and Prior. Rename the column after comparison to something more suitable for you.

Update#1

Try this

index=tto* sourcetype=access "Get wtt_err HTTP/1.1" NOT Error=111 earliest=-48h@h latest=-24h@h | dedup client_ip | stats count by Error| eval Period="Prior" | append [search index=tto* sourcetype=access "Get wtt_err HTTP/1.1" NOT Error=111 earliest=-24h@h latest=now | dedup client_ip | stats count by Error | eval Period="Current" ] | chart sum(count) over Error by Period

Re: How to compare data between two tables from 2 different time periods?

trunghung — Tue, 23 Feb 2016 18:41:17 GMT

thanks for the suggestion. I tried your query but it yielded no result.

Re: How to compare data between two tables from 2 different time periods?

somesoni2 — Tue, 23 Feb 2016 19:22:11 GMT

Strange.. it should've worked if the base search and the fieldnames are correct. Can you provide any sample working query for the same data?

Re: How to compare data between two tables from 2 different time periods?

trunghung — Tue, 29 Sep 2020 08:52:00 GMT

here is a working query:
index=tto* sourcetype=access "Get wtt_err HTTP/1.1" NOT Error=111 earliest=-48h@h latest=-24h@h | dedup client_ip | stats count by Error | sort count +

thank you for your help

Re: How to compare data between two tables from 2 different time periods?

trunghung — Tue, 29 Sep 2020 08:52:03 GMT

Sorry, here is the query I try to use. thanks
index=tto* sourcetype=access "Get wtt_err HTTP/1.1" NOT Error=111 earliest=-48h@h latest=-24h@h | dedup client_ip | stats count by Error | sort count +

Re: How to compare data between two tables from 2 different time periods?

trunghung — Tue, 23 Feb 2016 21:57:44 GMT

It works now. There was a minor type in the second part of the query. Need to add the * around wtt_err. thank you very much

Re: How to compare data between two tables from 2 different time periods?

Hung_Nguyen — Thu, 25 Feb 2016 01:56:39 GMT

Hi somesoni2,

thank you for your suggestion, it works great. I have 1 last question, in some of my panels, I like to use the statistical table, but the rows aren't sort. I tried | sort count or | sort sum(count) but it doesn't seem to work. Do you know how I can make it sorted? thanks