topic Re: grouping and delta in search in Splunk Search

grouping and delta in search

RobertRi — Wed, 23 Jan 2013 11:25:47 GMT

I have the following problem with a search.

This is my data

01/23/2013 08:00 user=Mimi pieces=23 price=30 region=noe
01/23/2013 09:00 user=Mimi pieces=0 price=33 region=ooe
01/23/2013 10:00 user=Mimi pieces=13 price=30 region=w
01/23/2013 08:00 user=Franz pieces=26 price=23 region=noe
01/23/2013 09:00 user=Franz pieces=21 price=73 region=ooe
01/23/2013 10:00 user=Franz pieces=43 price=12 region=w
01/23/2013 08:00 user=Sandra pieces=565 price=54 region=noe
01/23/2013 09:00 user=Sandra pieces=453 price=12 region=ooe
01/23/2013 10:00 user=Sandra pieces=233 price=21 region=w
01/23/2013 08:00 user=Susi pieces=0 price=320 region=noe
01/23/2013 09:00 user=Susi pieces=5 price=3 region=ooe
01/23/2013 10:00 user=Susi pieces=50 price=33 region=w

Now I want to see the delta for each user and each time, how many pieces the user have sold.
If I try it with a single user, this works fine

sourcetype=delta  user=sandra | reverse | delta pieces as delta | stats avg(pieces) as pieces,avg(delta) as delta by user,_time

If I would like to see all users with there deltas and I am ommit the user=sandra then I get a list with all users and the delta is calculated between the old and the new user.

How I can make the search, that I only get the delta values for each user separated in a list to make a chart?

Update:

This search looks better, but how I can add the delta for pieces

sourcetype=delta  user=* | reverse | chart avg(pieces) as pieces by _time, user

_time   Franz   Mimi    Sandra  Susi
1   1/23/13 8:00:00.000 AM  26.000000   23.000000   565.000000  0.000000
2   1/23/13 9:00:00.000 AM  21.000000   0.000000    453.000000  5.000000
3   1/23/13 10:00:00.000 AM 43.000000   13.000000   233.000000  50.000000

Update:

I think I'm near to the answer
I can use the parameter for delta p=4 then, splunk calculate the right values of each user

sourcetype=delta | reverse | stats avg(pieces) as pieces by _time, user | delta pieces p=4

_time   user    pieces  delta(pieces)
1   1/23/13 8:00:00.000 AM  Franz   26.000000   
2   1/23/13 8:00:00.000 AM  Mimi    23.000000   
3   1/23/13 8:00:00.000 AM  Sandra  565.000000  
4   1/23/13 8:00:00.000 AM  Susi    0.000000    
5   1/23/13 9:00:00.000 AM  Franz   21.000000   -5.000000
6   1/23/13 9:00:00.000 AM  Mimi    0.000000    -23.000000
7   1/23/13 9:00:00.000 AM  Sandra  453.000000  -112.000000
8   1/23/13 9:00:00.000 AM  Susi    5.000000    5.000000
9   1/23/13 10:00:00.000 AM Franz   43.000000   22.000000
10  1/23/13 10:00:00.000 AM Mimi    13.000000   13.000000
11  1/23/13 10:00:00.000 AM Sandra  233.000000  -220.000000
12  1/23/13 10:00:00.000 AM Susi    50.000000   45.000000

I have tried to combine a search with a subsearch to get the distinct users but I get a error message for the delta function

sourcetype=delta | reverse | stats avg(pieces) as pieces by _time, user | delta pieces [search sourcetype=delta  | stats dc(user) as tmp | eval tmp= "p=" . tmp]

Thanks#Rob

Re: grouping and delta in search

jonuwz — Wed, 23 Jan 2013 14:32:44 GMT

You need to use streamstats to calculate deltas if you need a 'by' clause:

Example :

* earliest=-1h@h
| bin _time span=5m
| stats count by _time, sourcetype
| streamstats window=2 global=f current=f first(count) as p_count by sourcetype
| eval delta=count-p_count

This gives you the the change in count every 5 minutes per sourcetype.

Add this if you want to visualise it :

 | xyseries _time,sourcetype,delta
 | makecontinuous _time

Re: grouping and delta in search

RobertRi — Mon, 28 Sep 2020 13:10:17 GMT

Hi jonuwz.

This command looks very difficult to me :-), but the first part works for my data.
I had to modify window= attribute from streamstats to 1, that I get the last previous sample correct. Now this command works fine

The second part is totally encrypted for me, could you explain in short words how the second part works

Thanks for your help
Rob

Re: grouping and delta in search

jonuwz — Wed, 23 Jan 2013 15:17:20 GMT

Your data is already binnned into daily chunks (from the look of it) so you probably dont need the | bin _time span=5m part

xyseries is like chart/timechart apart from you dont need to use an aggregate function. docs

makecontinuous _time looks at the _time field, and makes it continuous if there's any missing samples. This is needed for splunk to print the times on the graphs in a readable format. (timechart does this bit automatically)

Re: grouping and delta in search

neerajsafenet — Tue, 17 Feb 2015 12:01:00 GMT

Thanks a lot buddy. This solved my problem. 🙂