How to graph multiple series graphs with null values using both gaps and connect

[david_keough]

Splunk 7.2.3
I have been trying to use timechart to graph synthetic transaction application response times.

The calculated data contains two series: RespTime and Violations. Graphing both of these series together appears to force an either or type situation relating to the use of either gaps or connect logic for null values within the series. This might be compounded or by span/bucket logic. I've experimented with several other graph types.

Any ideas moving forward would be appreciated.

The _raw data is in the following format:

GUID|APPid|Synthetic Transaction Host|DateTime|RespTime

My initial approach was to create two series based on the RespTime being a -1 value (indicating a violation) then graphing them both.

| eval Violations=if(CFE_RespTime=-1,CFE_RespTime,"")
| eval RespTime=if(CFE_RespTime=-1,"",CFE_RespTime)
| timechart avg(RespTime) as "Response Time" avg(Violations) as "Threshold Violations"

The second attachment has span=6m but it otherwise the same as the previous logic.

Without an explicit span the attached graph shows the issue I am trying to discuss / solve.

The desire is for either the Response Time series to be entirely connected OR for there to be gaps only at the location of the violations while not having the violations series connect over entirely separate violation values. If there are consecutive values within the violation series then the violation series should connect also.

Choosing connect also connects the violation series while choosing gap creates numerous gaps in the response time series.
Changing the span doesn't alleviate the issue as gaps may randomly show up depending on the specific timing of the hosts generating the data.

In the data below, the -1 values are threshold violations, when they are consecutive they should connect but when they are not there should be gaps in the data.

example table
_time RespTime

2019-11-21 08:45:33 1086
2019-11-21 08:39:56 1260
2019-11-21 08:39:56 1324
2019-11-21 08:40:11 1287
2019-11-21 08:40:16 1139
2019-11-21 08:40:16 1144
2019-11-21 08:33:28 1089
2019-11-21 08:19:56 1266
2019-11-21 08:19:57 1254
2019-11-21 08:20:11 1330
2019-11-21 08:20:15 1123
2019-11-21 08:20:16 1140
2019-11-21 08:13:28 1118
2019-11-21 08:03:27 1047
2019-11-21 07:59:56 1279
2019-11-21 07:59:56 1257
2019-11-21 08:00:08 1183
2019-11-21 08:00:15 1150
2019-11-21 08:00:16 1138
2019-11-21 07:43:28 1058
2019-11-21 07:29:56 1324
2019-11-21 07:29:56 1358
2019-11-21 07:30:11 1290
2019-11-21 07:30:15 1176
2019-11-21 07:30:16 1186
2019-11-21 07:09:56 -1
2019-11-21 07:09:57 2032
2019-11-21 07:10:09 1196
2019-11-21 07:10:15 1165
2019-11-21 07:10:16 1165
2019-11-21 07:03:28 1037
2019-11-21 06:59:56 1277
2019-11-21 06:59:56 1309
2019-11-21 07:00:09 1203
2019-11-21 07:00:15 1128
2019-11-21 07:00:16 1138
2019-11-21 06:53:28 1087
2019-11-21 06:49:56 1291
2019-11-21 06:49:56 -1
2019-11-21 06:50:11 1244
2019-11-21 06:50:15 1117
2019-11-21 06:50:16 1148
2019-11-21 06:43:28 1042
2019-11-21 06:39:56 1297
2019-11-21 06:39:57 1374
2019-11-21 06:40:14 6222
2019-11-21 06:40:15 1121
2019-11-21 06:40:16 1117
2019-11-21 06:29:56 1243
2019-11-21 06:29:56 1245
2019-11-21 06:30:11 1289
2019-11-21 06:30:15 1143
2019-11-21 06:30:16 1101
2019-11-21 06:19:56 1390
2019-11-21 06:19:56 1363
2019-11-21 06:20:09 1223
2019-11-21 06:20:15 1118
2019-11-21 06:20:16 1523
2019-11-21 08:49:56 -1
2019-11-21 08:49:56 1348
2019-11-21 08:50:11 1298
2019-11-21 08:50:15 1180
2019-11-21 08:50:16 1109
2019-11-21 08:29:56 1296
2019-11-21 08:29:57 1278
2019-11-21 08:30:08 1192
2019-11-21 08:30:15 1169
2019-11-21 08:30:16 1151
2019-11-21 08:23:28 1097
2019-11-21 08:09:56 1314
2019-11-21 08:09:56 1247
2019-11-21 08:10:08 1216
2019-11-21 08:10:15 1133
2019-11-21 08:10:16 1144
2019-11-21 07:53:28 1025
2019-11-21 07:49:56 1265
2019-11-21 07:49:56 1273
2019-11-21 07:50:08 1262
2019-11-21 07:50:15 1164
2019-11-21 07:50:16 1095
2019-11-21 07:39:56 1275
2019-11-21 07:39:56 -1
2019-11-21 07:40:08 1169
2019-11-21 07:40:15 1151
2019-11-21 07:40:16 1099
2019-11-21 07:33:28 1073
2019-11-21 07:23:28 1104
2019-11-21 07:19:56 1286
2019-11-21 07:19:57 1268
2019-11-21 07:20:08 1199
2019-11-21 07:20:15 1281
2019-11-21 07:20:16 1123
2019-11-21 07:13:28 1123
2019-11-21 06:33:28 1060
2019-11-21 06:23:27 1054
2019-11-21 06:13:27 1019
2019-11-21 06:09:56 -1
2019-11-21 06:09:56 1255
2019-11-21 06:10:08 1211
2019-11-21 06:10:15 1138

[to4kawa · Accepted solution]

| makeresults
| eval raw="_time,RespTime
 2019-11-21 08:45:33    1086
 2019-11-21 08:39:56    1260
 2019-11-21 08:39:56    1324
 2019-11-21 08:40:11    1287
 2019-11-21 08:40:16    1139
 2019-11-21 08:40:16    1144
 2019-11-21 08:33:28    1089
 2019-11-21 08:19:56    1266
 2019-11-21 08:19:57    1254
 2019-11-21 08:20:11    1330
 2019-11-21 08:20:15    1123
 2019-11-21 08:20:16    1140
 2019-11-21 08:13:28    1118
 2019-11-21 08:03:27    1047
 2019-11-21 07:59:56    1279
 2019-11-21 07:59:56    1257
 2019-11-21 08:00:08    1183
 2019-11-21 08:00:15    1150
 2019-11-21 08:00:16    1138
 2019-11-21 07:43:28    1058
 2019-11-21 07:29:56    1324
 2019-11-21 07:29:56    1358
 2019-11-21 07:30:11    1290
 2019-11-21 07:30:15    1176
 2019-11-21 07:30:16    1186
 2019-11-21 07:09:56    -1
 2019-11-21 07:09:57    2032
 2019-11-21 07:10:09    1196
 2019-11-21 07:10:15    1165
 2019-11-21 07:10:16    1165
 2019-11-21 07:03:28    1037
 2019-11-21 06:59:56    1277
 2019-11-21 06:59:56    1309
 2019-11-21 07:00:09    1203
 2019-11-21 07:00:15    1128
 2019-11-21 07:00:16    1138
 2019-11-21 06:53:28    1087
 2019-11-21 06:49:56    1291
 2019-11-21 06:49:56    -1
 2019-11-21 06:50:11    1244
 2019-11-21 06:50:15    1117
 2019-11-21 06:50:16    1148
 2019-11-21 06:43:28    1042
 2019-11-21 06:39:56    1297
 2019-11-21 06:39:57    1374
 2019-11-21 06:40:14    6222
 2019-11-21 06:40:15    1121
 2019-11-21 06:40:16    1117
 2019-11-21 06:29:56    1243
 2019-11-21 06:29:56    1245
 2019-11-21 06:30:11    1289
 2019-11-21 06:30:15    1143
 2019-11-21 06:30:16    1101
 2019-11-21 06:19:56    1390
 2019-11-21 06:19:56    1363
 2019-11-21 06:20:09    1223
 2019-11-21 06:20:15    1118
 2019-11-21 06:20:16    1523
 2019-11-21 08:49:56    -1
 2019-11-21 08:49:56    1348
 2019-11-21 08:50:11    1298
 2019-11-21 08:50:15    1180
 2019-11-21 08:50:16    1109
 2019-11-21 08:29:56    1296
 2019-11-21 08:29:57    1278
 2019-11-21 08:30:08    1192
 2019-11-21 08:30:15    1169
 2019-11-21 08:30:16    1151
 2019-11-21 08:23:28    1097
 2019-11-21 08:09:56    1314
 2019-11-21 08:09:56    1247
 2019-11-21 08:10:08    1216
 2019-11-21 08:10:15    1133
 2019-11-21 08:10:16    1144
 2019-11-21 07:53:28    1025
 2019-11-21 07:49:56    1265
 2019-11-21 07:49:56    1273
 2019-11-21 07:50:08    1262
 2019-11-21 07:50:15    1164
 2019-11-21 07:50:16    1095
 2019-11-21 07:39:56    1275
 2019-11-21 07:39:56    -1
 2019-11-21 07:40:08    1169
 2019-11-21 07:40:15    1151
 2019-11-21 07:40:16    1099
 2019-11-21 07:33:28    1073
 2019-11-21 07:23:28    1104
 2019-11-21 07:19:56    1286
 2019-11-21 07:19:57    1268
 2019-11-21 07:20:08    1199
 2019-11-21 07:20:15    1281
 2019-11-21 07:20:16    1123
 2019-11-21 07:13:28    1123
 2019-11-21 06:33:28    1060
 2019-11-21 06:23:27    1054
 2019-11-21 06:13:27    1019
 2019-11-21 06:09:56    -1
 2019-11-21 06:09:56    1255
 2019-11-21 06:10:08    1211
 2019-11-21 06:10:15    1138"
| eval _raw=ltrim(replace(raw,"  +",","),",")
| multikv forceheader=1
| eval _time=strptime(time,"%Y-%m-%d %H:%M:%S")
| table _time RespTime
`comment("this is sample data")`
| sort 0 _time
| streamstats avg(eval(if(RespTime!=-1,RespTime,0))) as "Response time" avg(eval(if(RespTime==-1,0,null))) as "Threshold Violations" time_window=6m
| table _time "Response time" "Threshold Violations"

Hi,I think the timechart command is unnecessary.
I hope you can display the graph(Visualisation > Line Chart) as it is here.

[david_keough]

I had the thought this morning to use stream stats to govern this. Your intuition was right. I had to modify the code a bit to get it to work. Line 103 had an extra comma that derailed the rest.

I adapted the code from 109 on to give me this

| streamstats avg(eval(if(CFE_RespTime!=-1,CFE_RespTime,0))) as "Response Time" avg(eval(if(CFE_RespTime==-1,0,null))) as "Threshold Violations" time_window=6m
| table _time "Response Time" "Threshold Violations"


It appears to have borne some great fruit though at higher resolutions it doesn't graph the individual points. It looks like its the better of the two worlds for this application.

Thank you.