Splunk Search

Compare two counts from two time searches

smcdonald20
Path Finder

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?

Tags (2)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

| delta count

View solution in original post

woodcock
Esteemed Legend

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

| delta count

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

smcdonald20
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

woodcock
Esteemed Legend

Add this to your search:

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

smcdonald20
Path Finder

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?

0 Karma

lakromani
Builder

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

woodcock
Esteemed Legend

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.

0 Karma

somesoni2
Revered Legend

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
0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.