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:
_time | autosave: DC23 | autosave: DC41 | autosave: DC44 | total: DC23 | total: DC41 | total: DC44 |
2020-10-07 | 247 | 50 | 87 | 500 | 600 | 700 |
2020-10-08 | 304 | 0 | 12 | 500 | 600 | 700 |
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?
_time | autosave: DC23 | total: DC23 | autosave%: DC23 | autosave: DC41 | total: DC41 | autosave%: DC41 | autosave: DC44 | total: DC44 | autosave%: DC44 |
2020-10-07 | 247 | 500 | 50 | 600 | 87 | 700 | |||
2020-10-08 | 304 | 500 | 0 | 600 | 12 | 700 |
Thanks,
Cherie
/* 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.
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
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
/* 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.