topic Re: Need help on creating table in Splunk Search

Need help on creating table

maanick87 — Wed, 26 Jan 2022 23:43:10 GMT

I have table like below using my splunk query.

Request1_tps	Request1_avg	Request1_p95	Request1_p90	Request2_tps	Request2_avg	Request2_p95	Request2_p90
10	1	1.2	1.1	20	2	2.2	2.1

I need to convert above table to below format. Can you provide search criteria for this. Thanks

API	tps	avg	p95	p90
Request1	10	1	1.2	1.1
Request2	20	2	2.2	2.1

Re: Need help on creating table

isoutamo — Thu, 27 Jan 2022 07:48:56 GMT

you can try something like this

| makeresults | eval Request1_tps=10, Request1_avg=1, Request1_p95=1.2, Request1_p90=1.1, Request2_tps=20, Request2_avg=2, Request2_p95=2.2, Request2_p90=2.1 | fields - _time ``` the lines above create a dummy event with the fields mentioned ``` | transpose 0 column_name=name | eval tps=if(match(name, ".*_tps"), 'row 1', null()), API=if(match(name, ".*_tps"), trim(name, "_tps"),API) | eval avg=if(match(name, ".*_avg"), 'row 1', null()), API=if(match(name, ".*_avg"), trim(name, "_avg"),API) | eval p90=if(match(name, ".*_p90"), 'row 1', null()), API=if(match(name, ".*_p95"), trim(name, "_p95"),API) | eval p95=if(match(name, ".*_p95"), 'row 1', null()), API=if(match(name, ".*_p90"), trim(name, "_p90"),API) | stats values(*) as * by API | table API tps avg p95 p90

r. Ismo

Re: Need help on creating table

yuanliu — Thu, 27 Jan 2022 09:25:01 GMT

Using foreach is perhaps simpler:

| foreach Request*_avg [eval API = mvappend(API, "Request<<MATCHSTR>>")] | mvexpand API ``` generate events (rows) for each API ``` | foreach *_* ``` breakdown metrics (<<MATCHSEG2>>), select value by API (<<MATCHSEG1>>) ``` [eval <<MATCHSEG2>> = mvappend(<<MATCHSEG2>>, if(API="<<MATCHSEG1>>", <<MATCHSEG1>>_<<MATCHSEG2>>, null()))] | fields - Request*

This said, I have a feeling that before the search produces that table with Request*_*, it already produced a multi-row table/event mix in the form of

Request1	avg	1
Request2	avg	2
Request1	p90	1.1
Request2	p90	2.1
...

Starting from this kind of table/event mix is much easier to generate the result you are asking for.

Re: Need help on creating table

maanick87 — Thu, 27 Jan 2022 19:10:49 GMT

@isoutamo Thanks for the reply. Your query works if i give naming convention like Request1, Request2 etc. If i change Request1, Request2 to something else and it does not work. Can you please help.

Re: Need help on creating table

isoutamo — Thu, 27 Jan 2022 19:29:36 GMT

Can you give sample from your actual data with values scrambled?

Re: Need help on creating table

maanick87 — Thu, 27 Jan 2022 19:54:33 GMT

@isoutamo My search returns following table.

get_01_manage_tps	get_01_manage_avg	get_01_manage_p90	get_01_manage_p95	get_02_settings_avg	get_02_settings_p90	get_02_settings_p95	get_02_settings_tps	get_03_devices_avg	get_03_devices_p90	get_03_devices_p95	get_03_devices_tps
0.05	1.642	10.083	10.092	0.247	0.269	0.278	0.03	0.333	0.45	0.517	0.23

After transpose , it looks like below which is not correct.

API	tps	avg	p95	p90
et_01_manage		1.642
et_02_settings		0.247
et_03_devices		0.333
get_01_manage	0.05		10.092	10.083
get_02_setting	0.03
get_02_settings			0.278	0.269
get_03_device	0.23
get_03_devices			0.517	0.45

My table should look like below.

API	tps	avg	p95	p90
get_01_manage	0.05	1.642	10.092	10.083
get_02_settings	0.03	0.247	0.278	0.269
get_03_devices	0.23	0.333	0.517	0.45

Re: Need help on creating table

yuanliu — Fri, 28 Jan 2022 04:39:35 GMT

Try this generalized match

| foreach *_avg [eval API = mvappend(API, "<<MATCHSTR>>")] | mvexpand API ``` generate events (rows) for each API ``` | foreach *_* ``` breakdown metrics (<<MATCHSEG2>>), select value by API (<<MATCHSEG1>>) ``` [eval <<MATCHSEG2>> = mvappend(<<MATCHSEG2>>, if(API="<<MATCHSEG1>>", <<MATCHSEG1>>_<<MATCHSEG2>>, null()))] | fields - Request*

Re: Need help on creating table

isoutamo — Fri, 28 Jan 2022 08:55:02 GMT

Thanks, it seems that trim takes away some characters based on your suffix. Here is fixed version.

| makeresults | eval get_01_manage_tps=0.05, get_01_manage_avg=1.642, get_01_manage_p90=10.083, get_01_manage_p95=10.092, get_02_settings_avg=0.247, get_02_settings_p90=0.269, get_02_settings_p95=0.278, get_02_settings_tps=0.03, get_03_devices_avg=0.333, get_03_devices_p90=0.45, get_03_devices_p95=0.517, get_03_devices_tps=0.23 | fields - _time ``` the lines above create a dummy event with the fields mentioned ``` | transpose 0 column_name=name | eval mTPS = if(match(name, ".*_tps$"), "Yes", "No"), mAVG = if(match(name, ".*_avg$"), "Yes", "No"), mP95 = if(match(name, ".*_p95$"), "Yes", "No"), mP90 = if(match(name, ".*_p90$"), "Yes", "No") | eval tps=if(match(name, ".*_tps"), 'row 1', null()), API=if(match(name, ".*_...$"), substr(name, 1,len(name)-4),API) | eval avg=if(match(name, ".*_avg"), 'row 1', null()) | eval p90=if(match(name, ".*_p90"), 'row 1', null()) | eval p95=if(match(name, ".*_p95"), 'row 1', null()) | stats values(*) as * by API | table API tps avg p95 p90

Re: Need help on creating table

maanick87 — Sat, 29 Jan 2022 05:52:30 GMT

@isoutamo Thank you so much. It works.

Re: Need help on creating table

maanick87 — Sat, 29 Jan 2022 05:53:09 GMT

@yuanliu Thanks for the suggestion.

Re: Need help on creating table

isoutamo — Sat, 29 Jan 2022 08:52:38 GMT

For curiosity, can you test both solutions with your data and then told execution times here? I would like to understand performance differences of those solutions.

Re: Need help on creating table

maanick87 — Mon, 31 Jan 2022 17:35:32 GMT

@isoutamo do you want me to try 2 version of queries which you have provided and check execution times?

Re: Need help on creating table

isoutamo — Mon, 31 Jan 2022 18:50:14 GMT

Yes for @yuanliu ‘s and my versions.