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
Ultra Champion
/* 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
Ultra Champion
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
Ultra Champion
/* 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
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!