Splunk Search
Highlighted

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

Explorer

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 siteid and startcount.

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

sourcetype=alpha time>1501646400 | … | stats count BY siteid

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 siteid startcount currcount. I want to end up with a startcount and a currcount (by siteid) so I can do:

eval diffcount = startcount - currcount (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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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 
Highlighted

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

Splunk Employee
Splunk Employee

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
0 Karma