topic Re: How to compare column values in a table with dynamic column names? in Dashboards & Visualizations

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

twinspop — Wed, 06 May 2015 01:15:08 GMT

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

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

vganjare — Wed, 06 May 2015 05:55:02 GMT

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

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

twinspop — Wed, 06 May 2015 14:02:43 GMT

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

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

woodcock — Wed, 06 May 2015 14:23:30 GMT

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

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

twinspop — Wed, 06 May 2015 14:41:27 GMT

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

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

woodcock — Wed, 06 May 2015 16:33:18 GMT

I see my mistake now; try this:

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

twinspop — Wed, 06 May 2015 16:47:51 GMT

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.

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

twinspop — Wed, 06 May 2015 17:05:12 GMT

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

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

woodcock — Wed, 06 May 2015 18:50:04 GMT

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.

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

akocak — Mon, 11 May 2020 20:15:57 GMT

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

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

to4kawa — Mon, 11 May 2020 20:54:16 GMT

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.

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

akocak — Tue, 12 May 2020 14:42:29 GMT

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