Splunk Search

Need help on creating table

maanick87
Loves-to-Learn Lots

I have table like below using my splunk query.

Request1_tpsRequest1_avgRequest1_p95Request1_p90Request2_tpsRequest2_avgRequest2_p95Request2_p90
1011.21.12022.22.1

 

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

APItpsavgp95p90
Request11011.21.1
Request22022.22.1
Labels (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

Request1avg1
Request2avg2
Request1p901.1
Request2p902.1
...  

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

Tags (2)
0 Karma

isoutamo
SplunkTrust
SplunkTrust

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 

0 Karma

maanick87
Loves-to-Learn Lots

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
Loves-to-Learn Lots

@yuanliu Thanks for the suggestion.

0 Karma

isoutamo
SplunkTrust
SplunkTrust
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.
0 Karma

maanick87
Loves-to-Learn Lots

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

0 Karma

isoutamo
SplunkTrust
SplunkTrust

Yes for @yuanliu ‘s and my versions.

0 Karma

isoutamo
SplunkTrust
SplunkTrust
Can you give sample from your actual data with values scrambled?
0 Karma

maanick87
Loves-to-Learn Lots

 

@isoutamo  My search returns following table.

get_01_manage_tpsget_01_manage_avgget_01_manage_p90get_01_manage_p95get_02_settings_avgget_02_settings_p90get_02_settings_p95get_02_settings_tpsget_03_devices_avgget_03_devices_p90get_03_devices_p95get_03_devices_tps
0.051.64210.08310.0920.2470.2690.2780.030.3330.450.5170.23

 

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

APItpsavgp95p90
et_01_manage 1.642  
et_02_settings 0.247  
et_03_devices 0.333  
get_01_manage0.05 10.09210.083
get_02_setting0.03   
get_02_settings  0.2780.269
get_03_device0.23   
get_03_devices  0.5170.45

 

My table should look like below.

 

APItpsavgp95p90
get_01_manage0.051.64210.09210.083
get_02_settings0.030.2470.2780.269
get_03_devices0.230.3330.5170.45
0 Karma

isoutamo
SplunkTrust
SplunkTrust

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
0 Karma

maanick87
Loves-to-Learn Lots

@isoutamo Thank you so much. It works.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...