topic Re: Multi-series timechart - struggling to get it working in Splunk Search

Struggling with Multi-series timechart- How to write this rex?

dmoberg — Wed, 03 Aug 2022 15:24:15 GMT

Each Event contains 1-many Transaction Names with associated metrics as per the below example:

2022-08-03T08:47:49.4554569Z TransNames: DavidTrans_2 DavidTrans_1 Total DavidTrans_3
2022-08-03T08:47:49.4633642Z Name: DavidTrans_2
2022-08-03T08:47:49.4995979Z DavidTrans_2 - TransactionsPerSec: 0.92
2022-08-03T08:47:49.5180222Z Name: DavidTrans_1
2022-08-03T08:47:49.5245825Z DavidTrans_1 - TransactionsPerSec: 0.96
2022-08-03T08:47:49.5339575Z Name: DavidTrans_3
2022-08-03T08:47:49.5405933Z DavidTrans_3 - TransactionsPerSec: 0.97

From this Event I want to create a multi-series Timechart, where X is _Time, (Event time), Y is the value for Transactions Per Second and where each graph line represents one of the Transactions (DavidTrans_1, DavidTrans_2, DavidTrans_3. This is just an example and the Transaction name and number of transactions will differ.

I have tried many different ways of doing this, but will paste what I just tried, which does not display any metrics and where the format is completely screwed up.

SEARCH XXXXXXXXX | rex field=_raw max_match=0 " (?<TRANSNAME>.*) - TransactionsPerSec: (?<TRANSPERSEC>.*).*" | timechart list(TRANSNAME),list(TRANSPERSEC) by TRANSNAME

I can add that using the stats command I can at least get the values in a nice table:

SEARCH XXXXXXXXX | rex field=_raw max_match=0 " (?<TRANSNAME>.*) - TransactionsPerSec: (?<TRANSPERSEC>.*).*" | stats list(TRANSNAME),list(TRANSPERSEC)

I feel I have done similar things before but for some reason getting the values displayed in a Timechart was tricky this time.

Re: Multi-series timechart - struggling to get it working

martinpu — Wed, 03 Aug 2022 13:39:57 GMT

2022-08-03T08:47:49.5245825Z DavidTrans_1 - TransactionsPerSec: 0.96

| rex "Z\s+(?<TRANSNAME>.*)\s*-\s*TransactionsPerSec:\s*(?<TRANSPERSEC>\S+)"
| timechart avg(TRANSPERSEC) as TRANSPERSEC by TRANSNAME

You want to see the field value of TRANSPERSEC per TRANSNAME. List can contain multivalues which doesnt work well with graphing. Try to use avg, min, max, median etc instead of list.

Re: Multi-series timechart - struggling to get it working

dmoberg — Wed, 03 Aug 2022 14:10:58 GMT

If I do this then all of the values are averaged out instead of displaying the individual values for each Transaction. See below:

But the look of the graph is better......

Re: Multi-series timechart - struggling to get it working

martinpu — Wed, 03 Aug 2022 14:31:38 GMT

You can force more bins by doing:

| rex "Z\s+(?<TRANSNAME>.*)\s*-\s*TransactionsPerSec:\s*(?<TRANSPERSEC>\S+)"
| timechart bins=500 avg(TRANSPERSEC) as TRANSPERSEC by TRANSNAME

Just increase the bins number.

Could also try this to expand all the multivalues:

| rex "Z\s+(?<TRANSNAME>.*)\s*-\s*TransactionsPerSec:\s*(?<TRANSPERSEC>\S+)"
| timechart list(TRANSPERSEC) as TRANSPERSEC by TRANSNAME
| foreach * [ |mvexpand <<FIELD>>]

Re: Multi-series timechart - struggling to get it working

dmoberg — Mon, 08 Aug 2022 06:41:21 GMT

Thanks for the suggestions, but I was unfortunately not able to get the results properly formatted with those. I was lucky to find a Splunk expert internally at our company that helped me to create the following query that seems to work 🙂

Seems like this query got complex due to the multivalues and multiple key value pairs for each name in the event.

Re: Multi-series timechart - struggling to get it working

ITWhisperer — Mon, 08 Aug 2022 07:15:24 GMT

Assuming the only useful events are those with TransactionsPerSec, try something like this

SEARCH XXXXXXXXX "TransactionsPerSec" | rex field=_raw max_match=0 " (?<TRANSNAME>.*) - TransactionsPerSec: (?<TRANSPERSEC>.*).*" | eval {TRANSNAME} = TRANSPERSEC | fields - TRANSNAME TRANSPERSEC