Splunk Search

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

WeiseGuy
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

cmerriman
Super Champion

WeiseGuy
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

cmerriman
Super Champion

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

0 Karma

WeiseGuy
Explorer

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

0 Karma

cmerriman
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

WeiseGuy
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

WeiseGuy
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

WeiseGuy
Explorer

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

0 Karma

cmerriman
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

WeiseGuy
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

cmerriman
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

WeiseGuy
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

cmerriman
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

WeiseGuy
Explorer

Perfect, thank you again!

0 Karma

WeiseGuy
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...