I want to diff the counts before and after a certain date.

[claatu]

I want to diff the counts before and after a certain date. Here is the 'before' query.

sourcetype=alpha   _time<1501646400 | … | stats count BY site_id 

For dev/example purposes, I hardcoded the date. The ... is a bunch of stuff I hope is not germane to this issue. In any case, I get what I want from the first query. It gives me a nice table with site_id and count columns. I can add:

 | eval start_count=count

and still I get nice table of site_id and start_count.

Now I run the same query, but flip the comparator to greater than. So:

sourcetype=alpha _time>1501646400 | … | stats count BY site_id

If I run that by itself I get a nice table as before. But I need them both. I have tried an append query (appending this greater than query to the prior less-than query) with adding to this last one a:

|eval curr_count=count

But the results are weird, not a nice table of site_id start_count curr_count. I want to end up with a start_count and a curr_count (by site_id) so I can do:

eval diffcount = start_count - curr_count (per site_id)

Probably the way to go is with a summary index, so if you can tell me how to do that great. Any suggestions though are welcome.

[msivill_splunk]

A standalone alternative

| makeresults count=10 
| streamstats count as number, min(_time) as first_time 
| eval _time = _time - (10-number) 
| eval site_id = CASE(number=1,"A",number=2,"B",number=3,"B",number=4,"C",number=5,"A",number=6,"A",number=7,"C",number=8,"C",number=9,"C",number=10,"C") 
| eval cut_off_time = first_time - 5
| eval is_start_count = IF(_time < cut_off_time, "start_count", "current_count") 
| stats count by site_id,is_start_count 
| xyseries site_id,is_start_count,count 
| fillnull 
| eval diff_count = start_count - current_count 
| table site_id, start_count, current_count, diff_count

[somesoni2]

Try like this

sourcetype=alpha  | … | eval period=if(_time<1501646400,"before_count","curr_count") | chart count BY site_id period | eval diffcount = start_count - curr_count