Splunk Search

[Need Help]how to put columns for one DataCenter together

cheriemilk
Path Finder

Hi team,

I have below query:

sourcetype=xxxx AND "POST /123?123_form_type=review&itrModule=cherie*"
| rex field=_raw "POST\s+(?<uri>.*)HTTP.*name\=(?<name>.*?)\&"
| eval saveMode=if(uri like "%cherie=true%", "1", "0")
| timechart span=1d count(eval(saveMode=1)) as autosave
count(eval(saveMode=0 OR saveMode=1)) as total by DC

Query result in splunk:

 
_timeautosave: DC23autosave: DC41autosave: DC44total: DC23total: DC41total: DC44
2020-10-07
 

247

5087500600700
2020-10-08304012500600700
 

But  the expected result  I want is:

1. put the columns with same DC together for easy to compare 

2. add a new column autosave%: DCxx which is calculated out by autosave/total*100, I tried with eval command after timechart command, but the column doesn't display.

| timechart span=1d count(eval(saveMode=1)) as autosave
count(eval(saveMode=0 OR saveMode=1)) as total by DC
| eval autosave%=round((autosave/total) * 100, 2)

So the expected table returned should be like this. Is this achievable?

_timeautosave: DC23total: DC23autosave%: DC23autosave: DC41total: DC41autosave%: DC41autosave: DC44total: DC44autosave%: DC44
2020-10-07247500 50600 87700 
2020-10-08304500 0600 12700 

 

 

Thanks,

Cherie

 

 

 

 

Labels (4)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
/* Transpose to get the field names as values in the column field */
| transpose 0
/* Rename the fields so that they can be ordered in the order required */
| rex field=column mode=sed "s/(?<max>autosave): (?<cert>.+)/\2: field1/"
| rex field=column mode=sed "s/(?<tot>total): (?<cert>.+)/\2: field2/"
| rex field=column mode=sed "s/(?<perc>percent): (?<cert>.+)/\2: field3/"
/* Transpose using the values in the column field as field names in transposed table */
| transpose 0 header_field=column
/* Remove column field (as it isn't needed) */
| fields - column
/* Reorder the fields in the table */
| table _time *
/* Transpose so we can rename the fields back */
| transpose 0
/* Rename the values in the column field */
| rex field=column mode=sed "s/(?<cert>.+): (?<f1>field1)/autosave: \1/"
| rex field=column mode=sed "s/(?<cert>.+): (?<f2>field2)/total: \1/"
| rex field=column mode=sed "s/(?<cert>.+): (?<f3>field3)/autosave%: \1/"
/* Transpose using the values in the column field as field names in transposed table */
| transpose 0 header_field=column
/* Remove column field (as it isn't needed) */
| fields - column

Hope that helps with your understanding

If you want to understand more, try just adding the commands one at a time and see what each is doing to the results.

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
sourcetype=xxxx AND "POST /123?123_form_type=review&itrModule=cherie*"
| rex field=_raw "POST\s+(?<uri>.*)HTTP.*name\=(?<name>.*?)\&"
| eval saveMode=if(uri like "%cherie=true%", "1", "0")
| bin span=1d _time
| stats count(eval(saveMode=1)) as autosave
count(eval(saveMode=0 OR saveMode=1)) as total by _time DC
| eval percent=round(autosave * 100 / total,2)
| chart values(total) as total values(autosave) as autosave values(percent) as percent by _time DC
| transpose 0
| rex field=column mode=sed "s/(?<max>autosave): (?<cert>.+)/\2: field1/"
| rex field=column mode=sed "s/(?<tot>total): (?<cert>.+)/\2: field2/"
| rex field=column mode=sed "s/(?<perc>percent): (?<cert>.+)/\2: field3/"
| transpose 0 header_field=column
| fields - column
| table _time *
| transpose 0
| rex field=column mode=sed "s/(?<cert>.+): (?<f1>field1)/autosave: \1/"
| rex field=column mode=sed "s/(?<cert>.+): (?<f2>field2)/total: \1/"
| rex field=column mode=sed "s/(?<cert>.+): (?<f3>field3)/autosave%: \1/"
| transpose 0 header_field=column
| fields - column
0 Karma

cheriemilk
Path Finder

Hi @ITWhisperer ,

Thank you for the help. I tried your solution and it does return the expected table format now.

I can understand below part.

sourcetype=xxxx AND "POST /123?123_form_type=review&itrModule=cherie*"
| rex field=_raw "POST\s+(?<uri>.*)HTTP.*name\=(?<name>.*?)\&"
| eval saveMode=if(uri like "%cherie=true%", "1", "0")
| bin span=1d _time
| stats count(eval(saveMode=1)) as autosave
count(eval(saveMode=0 OR saveMode=1)) as total by _time DC
| eval percent=round(autosave * 100 / total,2)
| chart values(total) as total values(autosave) as autosave values(percent) as percent by _time DC

But for the rest part It's a bit complex for me to understand. Could you please help explain the solution logic that has 3 times of transpose and 6 rex commands with mode=sed.

| transpose 0
| rex field=column mode=sed "s/(?<max>autosave): (?<cert>.+)/\2: field1/"
| rex field=column mode=sed "s/(?<tot>total): (?<cert>.+)/\2: field2/"
| rex field=column mode=sed "s/(?<perc>percent): (?<cert>.+)/\2: field3/"
| transpose 0 header_field=column
| fields - column
| table _time *
| transpose 0
| rex field=column mode=sed "s/(?<cert>.+): (?<f1>field1)/autosave: \1/"
| rex field=column mode=sed "s/(?<cert>.+): (?<f2>field2)/total: \1/"
| rex field=column mode=sed "s/(?<cert>.+): (?<f3>field3)/autosave%: \1/"
| transpose 0 header_field=column
| fields - column

 

Thanks,

Cherie

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
/* Transpose to get the field names as values in the column field */
| transpose 0
/* Rename the fields so that they can be ordered in the order required */
| rex field=column mode=sed "s/(?<max>autosave): (?<cert>.+)/\2: field1/"
| rex field=column mode=sed "s/(?<tot>total): (?<cert>.+)/\2: field2/"
| rex field=column mode=sed "s/(?<perc>percent): (?<cert>.+)/\2: field3/"
/* Transpose using the values in the column field as field names in transposed table */
| transpose 0 header_field=column
/* Remove column field (as it isn't needed) */
| fields - column
/* Reorder the fields in the table */
| table _time *
/* Transpose so we can rename the fields back */
| transpose 0
/* Rename the values in the column field */
| rex field=column mode=sed "s/(?<cert>.+): (?<f1>field1)/autosave: \1/"
| rex field=column mode=sed "s/(?<cert>.+): (?<f2>field2)/total: \1/"
| rex field=column mode=sed "s/(?<cert>.+): (?<f3>field3)/autosave%: \1/"
/* Transpose using the values in the column field as field names in transposed table */
| transpose 0 header_field=column
/* Remove column field (as it isn't needed) */
| fields - column

Hope that helps with your understanding

If you want to understand more, try just adding the commands one at a time and see what each is doing to the results.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...