topic Re: Compare two counts from two time searches in Splunk Search

Compare two counts from two time searches

smcdonald20 — Tue, 29 Sep 2020 13:00:09 GMT

I need to be able to find the difference between two "Count" values; the count for today, and the count yesterday.

My search:
index=ad source=otl_adcomputerscan
| stats count by source, reportTime

This gives me results similar to below:

Source---------------- ReportTime Count
otl_adcomputerscan 2017-02-20 16070
otl_adcomputerscan 2017-02-21 16088

I want to be able to find the difference between the count today, and the count yesterday.
What is the best way to do this?

Re: Compare two counts from two time searches

somesoni2 — Tue, 21 Feb 2017 16:24:21 GMT

Assuming you always want to compare today with yesterday, try this

index=ad source=otl_adcomputerscan | eval reportTime=if(strptime(reportTime,"%Y-%m-%d")>=relative_time(now(),"@d"),"Today","Yesterday")
| chart count over source by reportTime | eval differecence=Today-Yesterday

Re: Compare two counts from two time searches

woodcock — Tue, 21 Feb 2017 16:41:19 GMT

Add this to your search:

| streamstats count AS row
| eval row="row" . row
| xyseries source row count
| eval delta=row2-row1

Re: Compare two counts from two time searches

smcdonald20 — Tue, 21 Feb 2017 18:04:22 GMT

Could only accept one answer, but this worked also!

Can you please explain to me what the "streamstats count as row" bit is doing?
And the "xyseries source row count" row?

Re: Compare two counts from two time searches

woodcock — Tue, 21 Feb 2017 18:21:34 GMT

The streamstats adds a row field to each row; xyseries converts a column to a string of rows (which is what chart is also doing in the other answer). Add the piped commands one-by-one to see what each does.

Re: Compare two counts from two time searches

lakromani — Tue, 21 Feb 2017 18:36:04 GMT

If you like to get credits to a good answer, up vote it.

Re: Compare two counts from two time searches

woodcock — Tue, 21 Feb 2017 18:49:44 GMT

I realized that this is even easier; add this to your search:

| delta count

Re: Compare two counts from two time searches

DalJeanis — Tue, 21 Feb 2017 21:57:47 GMT

...experiencing the pain that comes with stubbing your toe on the obvious solution...

Re: Compare two counts from two time searches

smcdonald20 — Wed, 22 Feb 2017 10:44:37 GMT

This is great! This will give me a list of delta's, how do I always use the most recent delta?

Re: Compare two counts from two time searches

woodcock — Wed, 22 Feb 2017 15:57:15 GMT

You can use either sort or reverse to change the order and use head or tail to keep only the top/last row.