Compare two counts from two time searches

[smcdonald20]

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?

[woodcock · Accepted solution]

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

| delta count

[DalJeanis]

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

[smcdonald20]

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

[woodcock]

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

[woodcock]

Add this to your search:

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

[smcdonald20]

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?

[lakromani]

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

[woodcock]

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.

[somesoni2]

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