How to compare column values in a table with dynamic column names?

[twinspop]

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

[akocak]

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

[to4kawa]

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.

[akocak]

This looks good . I may have overworked mine for internal logs. However, my start point was dynamic number of columns and rows

[twinspop]

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

[woodcock]

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

[twinspop]

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.

[woodcock]

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.

[woodcock]

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

[twinspop]

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.)

[vganjare]

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!!

[twinspop]

Tried to draw some table action. Failed. See attached image of what it looks like before I hit submit.