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.
have you tried |spath
https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Spath
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.
perhaps something like:
|spath|rename facets.name as name, facets.name.results.sum as sum|table facets sum
That gave me one row that looks like the last pic I showed using dedup.
try using |mvexpand name
to make them separate rows, if the name and sum in each row match up to the raw data.
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!
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.
Also, thanks for all your help so far! I figured out removing the |spath gets rid of duplicate sums.
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
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
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.
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.
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.
Perfect, thank you again!
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.