topic Re: How to chart a search that returns event with multiple facets that have a name/result pair. in Splunk Search

How to chart a search that returns event with multiple facets that have a name/result pair.

WeiseGuy — Thu, 10 Aug 2017 14:28:57 GMT

I am doing the following search:

source="new_relic_insights://NRInsightsAPI_rc_ShopFront_Top10Transactions"
| search *
| head 1

This returns a single event, and within its facets I have a name: xyz and results.sum: 123

The sum corresponds to the name, and I need to chart these on a bar chart.

Here is an example of what is returned:

Raw format:

This is what I have done so far to try to chart it, but because there are multiple values in one row, it doesn't work. Additionally the "total time" values aren't lined up with their corresponding result, for example 58245.xxx should be next to "WebTransaction/MVC/ProductController/Category" but it's not, again I assume this is because of them all being dumped into one row.

Finally, I tried dedup/table to get what I needed and the results.sum line up with each name, however again trying to graph this groups all the values of name as one since they are in one row.

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

cmerriman — Thu, 10 Aug 2017 15:36:40 GMT

have you tried |spath
https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Spath

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

WeiseGuy — Thu, 10 Aug 2017 15:43:06 GMT

I tried to mess with it some but I've used splunk for all of 4 days and I've been working on this for maybe 10 hours now trying to fiddle with things. I have no clue how to use spath to fix this, I've tried extract too and am failing. If you can give some examples that would be great, because I've read docs on both commands and its not working the way I've tried it.

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

WeiseGuy — Thu, 10 Aug 2017 15:51:39 GMT

source="new_relic_insights://NRInsightsAPI_rc_ShopFront_Top10Transactions"
| search *
| head 1
| spath input=name output="Transaction Name" path=facets{}.name
| spath input=sum output="Total Time" path=facets{}.results{}.sum

This didn't seem to change anything at all, so I'm not really sure what I am doing with spath it seems.

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

cmerriman — Thu, 10 Aug 2017 15:57:45 GMT

perhaps something like:
|spath|rename facets.name as name, facets.name.results.sum as sum|table facets sum

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

WeiseGuy — Thu, 10 Aug 2017 16:03:43 GMT

That gave me one row that looks like the last pic I showed using dedup.

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

cmerriman — Thu, 10 Aug 2017 16:11:16 GMT

try using |mvexpand name to make them separate rows, if the name and sum in each row match up to the raw data.

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

WeiseGuy — Thu, 10 Aug 2017 16:19:18 GMT

This is what I got without mvexpand, highlight shows that the data was duplicated for some reason?

This is with mvexpand, data is duplicated in the right column, left column does split out the names at least!

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

WeiseGuy — Thu, 10 Aug 2017 16:29:33 GMT

Playing with it a bit more, I got to this point which is almost perfect, except the sum is showing the first value for every row instead of iterating through the sum that correlates to the name.

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

WeiseGuy — Thu, 10 Aug 2017 16:35:35 GMT

Also, thanks for all your help so far! I figured out removing the |spath gets rid of duplicate sums.

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

cmerriman — Thu, 10 Aug 2017 17:18:55 GMT

so what is your syntax now and are your results not bringing in the right sums still?

if you did |eval name_sum=mvzip(name,sum)|mvexpand name_sum|dedup name_sum

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

WeiseGuy — Thu, 10 Aug 2017 17:26:19 GMT

This is where I am now...

source="new_relic_insights://NRInsightsAPI_rc_ShopFront_Top10Transactions"
| search *
| head 1
| rename facets{}.name as name, facets{}.results{}.sum as sum
| table name sum
| eval name_sum = mvzip(name, sum)
| mvexpand name_sum
| dedup name_sum

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

cmerriman — Thu, 10 Aug 2017 18:06:41 GMT

so, i think that's looking pretty good. at the end add |fields name_sum|rex field=name_sum "(?<name>\D+),(?<sum>.*)"|fields - name_sum
that should split out name and sum back into two separate fields and display only them.

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

WeiseGuy — Thu, 10 Aug 2017 18:13:54 GMT

Holy crap, it worked, thank you so much!

One thing that would be nice but is REALLY one of those "sugar on top" things would be if we could represent the "sum" as a % of the sum of all the "sum" values.

i.e. 55,737 / totalOfAllSum = x%

Not completely necessary, but would help.

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

cmerriman — Thu, 10 Aug 2017 18:15:49 GMT

to do that, add in |eventstats sum(sum) as total|eval percent=round(sum/total*100,2)|fields - total to the end of the syntax. that should do it.

Re: How to chart a search that returns event with multiple facets that have a name/result pair.

WeiseGuy — Thu, 10 Aug 2017 18:22:58 GMT

Perfect, thank you again!