topic Re: [Need Help]how to put columns for one DataCenter together in Splunk Search

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

cheriemilk — Thu, 08 Oct 2020 07:07:02 GMT

Hi team,

I have below query:

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

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

ITWhisperer — Thu, 08 Oct 2020 08:41:43 GMT

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

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

cheriemilk — Thu, 08 Oct 2020 13:52:01 GMT

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

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

ITWhisperer — Thu, 08 Oct 2020 14:14:30 GMT

/* 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.