alt textI've got daily searches dumping unique user counts by GUID into a summary index. My example search:
index=summary Uniques earliest=-7d@d latest=@d+10m|
eval Time=strftime(_time,"%m/%d") |
chart last(Uniques) over GUID by Time |
fillnull
This produces a table with 7 days ("mm/dd") across the top, GUIDs down the left, and unique user counts in the cells.
GUID 04/29 04/30 05/01 05/02 05/03 05/04 05/05
foobar 0 12 20 15 30 12 40
barfoo 10 11 24 11 38 22 52
I'd like to add a column to the far right for Delta, showing the change from the FIRST column (the earliest day) and the LAST column (the latest day).
GUID 04/29 04/30 05/01 05/02 05/03 05/04 05/05 Delta
foobar 0 12 20 15 30 12 40 40
barfoo 10 11 24 11 38 22 52 42
Using stats
with earliest()
and latest()
earlier in the chain doesn't work because nulls are there for GUIDs that didn't exist, so the eval
is comparing the first non-null value, not a zero like I want. Once I fill the nulls, the table is already made and the column names are dynamic. An outer join
subsearch limited to the earliest days might do it? Seems like a messy solution, if it would even work. There's gotta be a better way... maybe?
Thanks,
Jon
Hi Guys, it seems like it has been some time , this is little bit unorthodox (matrix) , but it works ( i created sample data from _internal).
However, my answer is not complete, there should be a better way for those evals , I couldn't made eval{} work.
| tstats count where index=_internal sourcetype=splunkd latest=@d earliest=-8d@d by _time,host
| bin _time span=1d
| eval Date=strftime(_time,"%m%d")
| chart sum(count) as count over host by Date useother=false limit=50
| fillnull
| transpose 100
| streamstats current=f window=1 last(row*) as row*_prev
| rename "row *" as row*
| eval row1_increase_percentage=if(isnum('row1') AND isnum('row1_prev'),round(('row1'-'row1_prev')/'row1_prev',2)*100,null())
| eval row2_increase_percentage=if(isnum('row2') AND isnum('row2_prev'),round(('row2'-'row2_prev')/'row2_prev',2)*100,null())
..........
| transpose 100
| sort column
| fields - "row 2"
| filldown
| search column=*increase* OR column="column"
| fields - column
| eval "row 1"=if('row 1'="host","_time",'row 1')
| transpose header_field=_time 100
| fields - column
Hi @akocak
| tstats count where index=_internal sourcetype=splunkd latest=@d earliest=-8d@d by _time span=1d host
| eval Date=strftime(_time,"%m%d")
| streamstats current=f last(count) as prev by host
| eval perc=round((count-prev)/prev,2)*100
| xyseries Date host perc
How about this?
I only have one host, so please let me know your results.
This looks good . I may have overworked mine for internal logs. However, my start point was dynamic number of columns and rows
Until a better solution arrives... Run 2 join
subsearches: 1 for the first day and one for the last. Then fillnulls, then get the delta, and drop the now extra fields.
index=summary Uniques earliest=-7d@d latest=@d+10m|
eval Time=strftime(_time,"%m/%d") |
chart last(Uniques) over GUID by Time |
join type=outer GUID [ search index=summary Uniques earliest=-7d@d latest=-6d@d | stats last(Uniques) as FIRSTDAY ] |
join type=outer GUID [ search index=summary Uniques earliest=-1d@d latest=@d | stats last(Uniques) as LASTDAY ] |
fillnull |
eval Delta=LASTDAY-FIRSTDAY |
fields - LASTDAY FIRSTDAY
I see my mistake now; try this:
index=summary Uniques earliest=-7d@d latest=@d+10m|
eval Time=strftime(_time,"%m/%d") |
chart last(Uniques) over GUID by Time |
fillnull | rex "^(?<firstValue>[\S]+)*(?<lastValue>[\S]+)$" | eval Delta = lastValue-firstValue | fields - *Value
rex
works on _raw by default. Once we create the chart _raw is null. So I don't think rex
will work. (In any case, it doesn't work as presented.) Thanks for trying tho.
Try using eventstats and then piping to xyseries. I spent some time working on and got it almost working enough to conclude that this approach should work for you.
Try this:
index=summary Uniques earliest=-7d@d latest=@d+10m|
stats min(_time) AS firstTime, max(_time) AS lastTime by GUID,_time |
eval Time=strftime(_time,"%m/%d") |
chart last(Uniques), first(firstTime) AS firstTime, first(lastTime) AS lastTime over GUID by Time |
eval Delta=lastTime-firstTime |
fillnull
Or, if for some reason that does't work, this surely should:
index=summary Uniques earliest=-7d@d latest=@d+10m|
eventstats min(_time) AS firstTime, max(_time) AS lastTime by GUID |
eval Time=strftime(_time,"%m/%d") |
chart last(Uniques), first(firstTime) AS firstTime, first(lastTime) AS lastTime over GUID by Time |
eval Delta=lastTime-firstTime |
fillnull
Negative, Ghostrider. Your delta is comparing times. I want a delta of the unique user count between the first column and the last column. (And your column names end up with "firstTime:dd/mm" and "last(Uniques):dd/mm" so the eval
for the time-based Delta fails.)
Can you please share the sample table generated and what additional column you want to add (i.e. the table output from above query and expected table)?
Thanks!!
Tried to draw some table action. Failed. See attached image of what it looks like before I hit submit.