Splunk Search

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

Explorer

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:

alt text

Raw format:
alt text

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.

alt text

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.

alt text

Super Champion

Explorer

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.

0 Karma

Super Champion

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

0 Karma

Explorer

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

0 Karma

Super Champion

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

0 Karma

Explorer

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

alt text

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

alt text

0 Karma

Explorer

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.

alt text

0 Karma

Explorer

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

0 Karma

Super Champion

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

0 Karma

Explorer

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

alt text

0 Karma

Super Champion

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.

0 Karma

Explorer

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.

0 Karma

Super Champion

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.

0 Karma

Explorer

Perfect, thank you again!

0 Karma

Explorer
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.

0 Karma