Need help on creating table

[maanick87]

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

[yuanliu]

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.

[isoutamo]

Hi

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 

[maanick87]

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

[yuanliu]

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*

[maanick87]

@yuanliu Thanks for the suggestion.

[isoutamo]

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.

[maanick87]

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

[isoutamo]

Yes for @yuanliu ‘s and my versions.

[isoutamo]

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

[maanick87]

@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

[isoutamo]

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

[maanick87]

@isoutamo Thank you so much. It works.