topic Re: Build a table with multiple key value pair. in Dashboards & Visualizations

Build a table with multiple key value pair.

GersonGarcia — Fri, 02 Feb 2018 01:26:31 GMT

All,
I have thirtpart application that generates one single line per execution.It is one table with COUNT DEVICES PER VERSION format:

index=main sourcetype=check_app_version host=node001 DEVICE_1_v1.0=1 DEVICE_1_v1.1=0 DEVICE_1_v1.3=0 DEVICE_1_v2.0=0 DEVICE_1_v2.1=1 DEVICE_1_v2.2=1 DEVICE_1_v3.0=1 DEVICE_1_v3.1=1 DEVICE_1_v3.2=150 DEVICE_1_Total=155 DEVICE_2_v1.0=0 DEVICE_2_v1.1=0 DEVICE_2_v1.3=0 DEVICE_2_v2.0=0 DEVICE_2_v2.1=20 DEVICE_2_v2.2=150 DEVICE_2_v3.0=0 DEVICE_2_v3.1=0 DEVICE_2_v3.2=12 DEVICE_2_Total=182 DEVICE_3_v1.0=0 DEVICE_3_v1.1=0 DEVICE_3_v1.3=300 DEVICE_3_v2.0=0 DEVICE_3_v2.1=0 DEVICE_3_v2.2=0 DEVICE_3_v3.0=0 DEVICE_3_v3.1=0 DEVICE_3_v3.2=0 DEVICE_3_Total=300 Total_v1.0=1 Total_v1.1=0 Total_v1.3=300 Total_v2.0=0 Total_v2.1=21 Total_v2.2=151 Total_v3.0=1 Total_v3.1=1 Total_v3.2=162 Total_Total=637

I would like to manipulate this data in Splunk generate table like:

    v1.0    v1.1    v1.3    v2.0    v2.1    v2.2    v3.0    v3.1    v3.2    Total
DEVICE_1    1   0   0   0   1   1   1   1   150 155
DEVICE_2    0   0   0   0   20  150 0   0   12  182
DEVICE_3    0   0   300 0   0   0   0   0   0   300
Total   1   0   300 0   21  151 1   1   162 637

What could be the best way to manipulate these KEY=VALUE pair? I can also format the output differently, but I cannot have multiple lines for the same host.

Thank you,

Gerson

Re: Build a table with multiple key value pair.

felipesewaybric — Fri, 02 Feb 2018 02:59:06 GMT

you will need to use some regex in the raw to get the v1.0 from the device, then, you can correlate with some stats by v* and device*, in the end you can set a | add totals to get te total line

Re: Build a table with multiple key value pair.

niketn — Fri, 02 Feb 2018 04:21:01 GMT

@GersonGarcia, since each time your thrid party application returns complete statistics you need wrapped in single event, you would need following command from | head 1 onward.

| makeresults 
| eval _raw="DEVICE_1_v1.0=1 DEVICE_1_v1.1=0 DEVICE_1_v1.3=0 DEVICE_1_v2.0=0 DEVICE_1_v2.1=1 DEVICE_1_v2.2=1 DEVICE_1_v3.0=1 DEVICE_1_v3.1=1 DEVICE_1_v3.2=150 DEVICE_1_Total=155 DEVICE_2_v1.0=0 DEVICE_2_v1.1=0 DEVICE_2_v1.3=0 DEVICE_2_v2.0=0 DEVICE_2_v2.1=20 DEVICE_2_v2.2=150 DEVICE_2_v3.0=0 DEVICE_2_v3.1=0 DEVICE_2_v3.2=12 DEVICE_2_Total=182 DEVICE_3_v1.0=0 DEVICE_3_v1.1=0 DEVICE_3_v1.3=300 DEVICE_3_v2.0=0 DEVICE_3_v2.1=0 DEVICE_3_v2.2=0 DEVICE_3_v3.0=0 DEVICE_3_v3.1=0 DEVICE_3_v3.2=0 DEVICE_3_Total=300 Total_v1.0=1 Total_v1.1=0 Total_v1.3=300 Total_v2.0=0 Total_v2.1=21 Total_v2.2=151 Total_v3.0=1 Total_v3.1=1 Total_v3.2=162 Total_Total=637"
| KV
| head 1
| stats values(DEVICE_*) as DEVICE_*
| transpose column_name="Metrics" 
| rename "row 1" as "Value"
| eval Device=replace(Metrics,"^(DEVICE_[^_])(_.*)","\1")
| eval Metrics=replace(Metrics,"^DEVICE_([^_]_)(.*)","\2")
| chart max(Value) as Value over Device by Metrics
| addtotals col=t labelfield=Device label=Total
| table Device v* Total

Please try out and confirm.

Re: Build a table with multiple key value pair.

GersonGarcia — Fri, 02 Feb 2018 18:43:56 GMT

@niketnilay,

It sort of worked... Unfortunately in my case, I have much more than 1000 key=value fields and it is truncating it.
But I have a question, what | KV does? I noticed that it converted 1.0 to 1_0 (in my case I need all versions) but I couldn't find this in my env.

Thank you,
Gerson

Re: Build a table with multiple key value pair.

GersonGarcia — Tue, 29 Sep 2020 17:53:40 GMT

I built this one as test:

| makeresults 
| eval _raw="Total_fw_ver_0.0.00=2 Total_fw_ver_0.10.00=1 Total_fw_ver_1.6.04=215 Total_fw_ver_1.6.07=8 Total_fw_ver_1.6.7a=1 Total_fw_ver_1.6.08=39 Total_fw_ver_1.6.09=310 Total_fw_ver_1.8.0f=6 Total_fw_ver_1.8.01=21 Total_fw_ver_1.8.04=4 Total_fw_ver_1.8.04=581 Total_fw_ver_1.8.06=2 Total_fw_ver_1.8.08=1 Total_fw_ver_1.8.08=4 Total_fw_ver_1.8.8f=9 Total_fw_ver_1.8.09=6379 Total_fw_ver_1.9.01=1 Total_fw_ver_1.10.02=2 Total_fw_ver_1.10.04=1 Total_fw_ver_1.10.6a=424 Total_fw_ver_1.10.6e=1496 Total_fw_ver_1.10.78=1 Total_fw_ver_1.10.b1=1 Total_fw_ver_2.0.01=1 Total_fw_ver_2.0.02=9 Total_fw_ver_2.0.05=80 Total_fw_ver_2.2.00=3 Total_fw_ver_2.2.02=6303 Total_fw_ver_2.2.04=13957 Total_fw_ver_2.5.10=3 Total_fw_ver_2.6.02=2 Total_fw_ver_2.6.02=2 Total_fw_ver_2.6.03=5 Total_fw_ver_2.6.03=24 Total_fw_ver_2.6.03=1 Total_fw_ver_2.6.03=1 Total_fw_ver_2.6.3a=1665 Total_fw_ver_2.6.3b=6837 Total_fw_ver_2.6.3c=3 Total_fw_ver_2.6.5b=1 Total_fw_ver_2.6.06=19 Total_fw_ver_2.6.6b=35 Total_fw_ver_2.6.07=1 Total_fw_ver_2.6.09=153 Total_fw_ver_2.6.09=123 Total_fw_ver_2.6.9a=3242 Total_fw_ver_2.6.9b=22824 Total_fw_ver_2.6.9c=283 Total_fw_ver_2.6.23=1503 Total_fw_ver_2.7.3b=1 Total_fw_ver_2.7.63=1 Total_fw_ver_2.7.80=1 Total_fw_ver_2.8.00=3 Total_fw_ver_2.8.0b=1 Total_fw_ver_2.8.0c=2 Total_fw_ver_2.8.01=5 Total_fw_ver_2.8.1a=3 Total_fw_ver_2.8.03=47 Total_fw_ver_2.8.03=3 Total_fw_ver_2.8.03=1 Total_fw_ver_2.8.04=1 Total_fw_ver_2.8.4a=4 Total_fw_ver_2.8.4b=1 Total_fw_ver_2.8.4c=1 Total_fw_ver_2.8.05=37 Total_fw_ver_2.8.06=537 Total_fw_ver_2.9.3b=1 Total_fw_ver_2.9.61=1 Total_fw_ver_2.9.3b=1 Total_fw_ver_2.10.03=1 Total_fw_ver_2.10.3a=1 Total_fw_ver_2.10.04=4 Total_fw_ver_2.10.06=1 Total_fw_ver_2.10.6a=6 Total_fw_ver_2.10.6b=113 Total_fw_ver_2.10.08=4 Total_fw_ver_2.10.08=1176 Total_fw_ver_2.10.8a=17338 Total_fw_ver_2.10.8b=44485 Total_fw_ver_2.10.8c=16138 Total_fw_ver_2.10.10=1 Total_fw_ver_2.10.10=7 Total_fw_ver_2.10.12=7 Total_fw_ver_2.10.12=8 Total_fw_ver_2.10.14=60 Total_fw_ver_2.10.14=4 Total_fw_ver_2.10.14=777 Total_fw_ver_2.11.28=1 Total_fw_ver_2.11.72=1 Total_fw_ver_2.11.89=1 Total_fw_ver_2.12.00=36 Total_fw_ver_2.12.00=5 Total_fw_ver_2.14.0c=1 Total_fw_ver_2.14.1c=103 Total_fw_ver_2.15.65=2 Total_fw_ver_2.16.00=1 Total_fw_ver_2.16.01=9 Total_fw_ver_2.16.02=1 Total_fw_ver_2.16.3c=1 Total_fw_ver_2.16.05=41 Total_fw_ver_2.16.5a=78429 Total_fw_ver_2.16.5c=1 Total_fw_ver_2.60.00=1 Total_fw_ver_3.0.03=152 Total_fw_ver_3.1.9d=330 Total_fw_ver_3.1.be=688 Total_fw_ver_3.1.bf=5 Total_fw_ver_3.1.bf=543 Total_fw_ver_3.1.7b=1 Total_fw_ver_3.1.ad=3 Total_fw_ver_3.1.77=7 Total_fw_ver_3.1.4d=1 Total_fw_ver_3.1.8a=6 Total_fw_ver_3.1.df=1 Total_fw_ver_3.1.df=1 Total_fw_ver_3.1.b2=1 Total_fw_ver_3.1.c1=2 Total_fw_ver_3.2.01=71 Total_fw_ver_3.2.1a=2 Total_fw_ver_3.2.1b=101 Total_fw_ver_3.2.1c=4 Total_fw_ver_3.2.02=422 Total_fw_ver_3.2.02=23 Total_fw_ver_3.2.2a=9173 Total_fw_ver_3.2.2b=83566 Total_fw_ver_3.2.2c=27 Total_fw_ver_3.2.2f=9 Total_fw_ver_3.2.3b=4084 Total_fw_ver_3.2.3c=1 Total_fw_ver_3.3.63=2 Total_fw_ver_3.3.ab=1 Total_fw_ver_3.4.01=122 Total_fw_ver_3.4.02=1 Total_fw_ver_3.4.03=2270 Total_fw_ver_3.4.03=28135 Total_fw_ver_3.4.3c=140 Total_fw_ver_3.4.04=1 Total_fw_ver_3.4.04=504 Total_fw_ver_3.4.04=80 Total_fw_ver_3.4.4b=1488 Total_fw_ver_3.4.05=450 Total_fw_ver_3.4.05=1557 Total_fw_ver_3.4.5b=770 Total_fw_ver_3.4.5c=1769 Total_fw_ver_3.5.56=1 Total_fw_ver_3.6.00=1 Total_fw_ver_3.6.2b=79 Total_fw_ver_3.6.05=33 Total_fw_ver_3.6.5c=1 Total_fw_ver_3.8.01=1036 Total_fw_ver_3.8.01=61 Total_fw_ver_3.8.1a=6943 Total_fw_ver_3.8.1b=93929 Total_fw_ver_3.8.03=5 Total_fw_ver_3.8.3b=14110 Total_fw_ver_3.8.05=1 Total_fw_ver_3.9.42=1 Total_fw_ver_3.9.4f=4 Total_fw_ver_3.10.00=35 Total_fw_ver_3.10.00=1987 Total_fw_ver_3.10.0a=138716 Total_fw_ver_3.10.0b=5419356 Total_fw_ver_3.10.01=1571 Total_fw_ver_3.10.01=10450 Total_fw_ver_3.10.1b=92 Total_fw_ver_3.99.15=2 Total_fw_ver_4.0.01=4 Total_fw_ver_47.d0.80=1 Total_fw_ver_Total=6050939" 
| KV 
| head 1 
| stats values(Total_fw_ver_*) as Total_fw_ver_* 
| transpose column_name="Metrics" 
| rename "row 1" as "Value" 
| eval fwver=replace(Metrics,"^Total_fw_ver_","") 
| eval fwver=replace(fwver,"_",".") 
| table fwver Value

There is a Total_fw_ver_Total=6050939 that is not in the final output, also addtotals of this table should be the be at least 6050939.

Is there any limitation of KV or stats?

Thank you.
Gerson

Re: Build a table with multiple key value pair.

niketn — Fri, 02 Feb 2018 20:37:08 GMT

KV command is only for mocked up data, to automatically extract Key Value pairs. Alternatively, extract command can also be used https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Extract

While working with actual index you would not need KV command.

Re: Build a table with multiple key value pair.

GersonGarcia — Tue, 29 Sep 2020 17:53:44 GMT

Thank you. The problem is that not all data is processed. It got cut around Total_fw_ver_3.2.1a=2

Re: Build a table with multiple key value pair.

niketn — Sun, 04 Feb 2018 04:06:14 GMT

@GersonGarcia, can you give you current search? Make sure | KV command or | extract command is not present as the same is not required. change the transpose command as following which indicated no limit on number of rows

| transpose 0 column_name="Metrics"

Please try out and confirm.