I have query results that look like this:
Risk Age Total
High gt30 16
High gt60 3
High gt90 184
Low gt30 4
Low gt60 42
Low gt90 84
I want it to be in this X-Y table format where X is Age and Y is Risk:
And note that there is no result for Med risk, but that has to be accounted for in the end formatted table as zeroes.
How the Splunk do I get that to happen?
Alternatively, I can get the results in this form, with zeroes for Med:
But then, how do you convert that into the desired X-Y table format?
@claatu
Can you please try this?
YOUR_SEARCH
| table Risk Age Total
| append [|makeresults| eval Risk="High,Med,Low",Risk=split(Risk,","),Age="gt30,gt60,gt90",Age=split(Age,","),order="1,3,2",order=split(order,",") | mvexpand Risk] | fillnull value=0 Total
| chart sum(Total) over Risk by Age |eval priority = if (Risk="High",1,if(Risk="Med",2,if(Risk="Low",3,4)))| sort priority | fields - priority
My Sample search:
| makeresults
| eval data="High gt30 16|High gt60 3|High gt90 184|Low gt30 4|Low gt60 42|Low gt90 84|Med gt90 8" , data=split(data,"|")
| mvexpand data
| eval Risk=mvindex(split(data," "),0),Age=mvindex(split(data," "),1),Total=mvindex(split(data," "),2)
| table Risk Age Total
| append [|makeresults| eval Risk="High,Med,Low",Risk=split(Risk,","),Age="gt30,gt60,gt90",Age=split(Age,","),order="1,3,2",order=split(order,",") | mvexpand Risk] | fillnull value=0 Total
| chart sum(Total) over Risk by Age |eval priority = if (Risk="High",1,if(Risk="Med",2,if(Risk="Low",3,4)))| sort priority | fields - priority
Thanks
@claatu
Can you please try this?
YOUR_SEARCH
| table Risk Age Total
| append [|makeresults| eval Risk="High,Med,Low",Risk=split(Risk,","),Age="gt30,gt60,gt90",Age=split(Age,","),order="1,3,2",order=split(order,",") | mvexpand Risk] | fillnull value=0 Total
| chart sum(Total) over Risk by Age |eval priority = if (Risk="High",1,if(Risk="Med",2,if(Risk="Low",3,4)))| sort priority | fields - priority
My Sample search:
| makeresults
| eval data="High gt30 16|High gt60 3|High gt90 184|Low gt30 4|Low gt60 42|Low gt90 84|Med gt90 8" , data=split(data,"|")
| mvexpand data
| eval Risk=mvindex(split(data," "),0),Age=mvindex(split(data," "),1),Total=mvindex(split(data," "),2)
| table Risk Age Total
| append [|makeresults| eval Risk="High,Med,Low",Risk=split(Risk,","),Age="gt30,gt60,gt90",Age=split(Age,","),order="1,3,2",order=split(order,",") | mvexpand Risk] | fillnull value=0 Total
| chart sum(Total) over Risk by Age |eval priority = if (Risk="High",1,if(Risk="Med",2,if(Risk="Low",3,4)))| sort priority | fields - priority
Thanks
Wow, that works! Is there a way to make it sort like this:
High
Med
Low
instead of
High
Low
Med
?
Figured it out.
| eval sort_field=case(Risk="High",1, Risk="Med",2, Risk="Low",3) | sort sort_field | fields Risk gt30 gt60 gt90
Yes you can create a new field and assign numbers to it at the end of your code, add this -
eval priority = if (risk="High",1,if(risk="Medieum",2,if(risk="Low",3,4)))| sort priority | fields - priority
If you have this data
Risk Age Total
High gt30 16
High gt60 3
High gt90 184
Low gt30 4
Low gt60 42
Low gt90 84
you can have query as chart sum(total) by risk age
This method does not produce a zero result for the Med column.
can you post the query you're using now
It's really long and involved so I don't want to post that here. But if you want to experiment you can just create a lookup table per the results I first mentioned (or the alternative results), and do an |inputlookup on it and go from there.