Dynamic search using global_time token for previous business day

[wodrog]

I've setup a dashboard based on charting trade queue information for our application which we are ingesting using a dbconnect SQL query.

I have one chart that is using the global_time picker for earliest and latest time values, I have another chart below it that I want to show for the same period of the previous business day.

I've tried the following two queries in the Data Source for the Previous Business Day chart but not getting any results:

Attempt 1:

index=prod_db sourcetype=dbconnect source=TradeStats
[| makeresults count=1
| eval earliest=if(strftime(now(),"%a")="Mon",relative_time("$global_time.earliest$", "-3d"),relative_time("$global_time.earliest$", "-1d"))
| eval latest=if(strftime(now(),"%a")="Mon",relative_time("$global_time.latest$", "-3d"),relative_time("$global_time.latest$", "-1d"))
| table earliest latest | format ]
| timechart sum(TradeCount) as Processed, latest(TradeQueue) as Queued latest(TradeQueueLatencyMins) as QueueLatencyMins span=1m partial=false

Attempt 2:

index=prod_db sourcetype=dbconnect source=TradeStats
[| makeresults count=1
| eval offsetdays=if(strftime(now(),"%a")="Mon","-3d","-1d")
| eval earliest=relative_time(earliest, offsetdays)
| eval latest=relative_time(latest, offsetdays)
| table earliest latest | format ]
| timechart sum(TradeCount) as Processed, latest(TradeQueue) as Queued latest(TradeQueueLatencyMins) as QueueLatencyMins span=1m partial=false

In attempt 1 i was just trying $global_time.earliiest$ without the quotation marks but kept getting evalcommand malformed, missing ). I tried various ways to escape the $ sign (tried $$)

Basically if I change the global time picker for the chart of current day, I want this to cascade to the next chart. Just doesn't seem to want to work. Only if I hard code the time period in the search.

Note I am using this code block in the dashboard code:

	"defaults": {
		"dataSources": {
			"ds.search": {
				"options": {
					"queryParameters": {
						"latest": "$global_time.latest$",
						"earliest": "$global_time.earliest$"
					},
					"refresh": "120s"
				}
			}
		}
	},

[livehybrid]

Hi @wodrog 

Would something like this work for you? This uses a hidden search/table to generate the earliest/latest for the previous day based on the input and then uses that in the earliest/latest for the search.

{
    "title": "Answers-SearchPreviousDay",
    "description": "",
    "inputs": {
        "input_zIorjrMc": {
            "options": {
                "defaultValue": "-24h@h,now",
                "token": "tr_global"
            },
            "title": "Main Time Selector",
            "type": "input.timerange"
        }
    },
    "defaults": {
        "dataSources": {
            "ds.o11y": {
                "options": {
                    "queryParameters": {
                        "earliest": "$global_time.earliest$",
                        "latest": "$global_time.latest$"
                    }
                }
            },
            "ds.search": {
                "options": {
                    "queryParameters": {
                        "earliest": "-24h@h",
                        "latest": "now"
                    }
                }
            }
        }
    },
    "visualizations": {
        "viz_5ZrDUHwo": {
            "containerOptions": {},
            "dataSources": {
                "primary": "ds_zdaUZXdL"
            },
            "eventHandlers": [
                {
                    "options": {
                        "tokens": [
                            {
                                "key": "row.info_min_time.value",
                                "token": "eventid"
                            }
                        ]
                    },
                    "type": "drilldown.setToken"
                }
            ],
            "options": {
                "stackMode": "stacked"
            },
            "showLastUpdated": false,
            "showProgressBar": false,
            "title": "This shows for time selected in picker",
            "type": "splunk.column"
        },
        "viz_BcDlqy4I": {
            "options": {
                "markdown": "Earliest = $globalTimeSpl:result.earliest$  \nLatest = $globalTimeSpl:result.latest$"
            },
            "type": "splunk.markdown"
        },
        "viz_NgmH6lHI": {
            "containerOptions": {},
            "dataSources": {
                "primary": "ds_BlYVOfBA"
            },
            "eventHandlers": [
                {
                    "options": {
                        "tokens": [
                            {
                                "key": "row.info_min_time.value",
                                "token": "eventid"
                            }
                        ]
                    },
                    "type": "drilldown.setToken"
                }
            ],
            "options": {
                "stackMode": "stacked"
            },
            "showLastUpdated": false,
            "showProgressBar": false,
            "title": "This shows for time selected - 24 hours",
            "type": "splunk.column"
        },
        "viz_zUx2Zt29": {
            "dataSources": {
                "primary": "ds_ZKBDXZy2_ds_BlYVOfBA"
            },
            "type": "splunk.table"
        }
    },
    "dataSources": {
        "ds_BlYVOfBA": {
            "name": "global",
            "options": {
                "query": "| tstats count where index=main earliest=$globalTimeSpl:result.earliest$ latest=$globalTimeSpl:result.latest$ by _time, host span=15m\n|  timechart span=15m sum(count) as count by host",
                "queryParameters": {
                    "earliest": "$tr_global.earliest$",
                    "latest": "$tr_global.latest$"
                }
            },
            "type": "ds.search"
        },
        "ds_ZKBDXZy2_ds_BlYVOfBA": {
            "name": "globalTimeSpl",
            "options": {
                "enableSmartSources": true,
                "query": "| makeresults \n| addinfo\n|  eval earliest=info_min_time-86400\n|  eval latest=info_max_time-86400",
                "queryParameters": {
                    "earliest": "$tr_global.earliest$",
                    "latest": "$tr_global.latest$"
                }
            },
            "type": "ds.search"
        },
        "ds_aOEeGNWG": {
            "name": "Search_1",
            "options": {
                "query": "| tstats count WHERE index=_internal by host"
            },
            "type": "ds.search"
        },
        "ds_ccCiW2S8": {
            "name": "tstat",
            "options": {
                "query": "| tstats count where index=_internal by _time span=1h",
                "queryParameters": {
                    "earliest": "$tr_global.earliest$",
                    "latest": "$tr_global.latest$"
                }
            },
            "type": "ds.search"
        },
        "ds_gRgnjURi": {
            "name": "Search_3",
            "options": {
                "query": "| tstats count where index=_internal by source, host"
            },
            "type": "ds.search"
        },
        "ds_rt307Czb": {
            "name": "timeSPL",
            "options": {
                "enableSmartSources": true,
                "query": "| makeresults \n| addinfo",
                "queryParameters": {
                    "earliest": "-60m@m",
                    "latest": "now"
                }
            },
            "type": "ds.search"
        },
        "ds_thns3Lsu": {
            "name": "Search_2",
            "options": {
                "earliest": "$global_time.earliest$",
                "latest": "$global_time.latest$",
                "o11yDSType": "serviceMap",
                "services": [
                    "paymentservice"
                ]
            },
            "type": "ds.o11y"
        },
        "ds_zdaUZXdL": {
            "name": "CurrentPickerTime",
            "options": {
                "query": "| tstats count where index=main by _time, host span=15m\n|  timechart span=15m sum(count) as count by host",
                "queryParameters": {
                    "earliest": "$tr_global.earliest$",
                    "latest": "$tr_global.latest$"
                }
            },
            "type": "ds.search"
        }
    },
    "layout": {
        "globalInputs": [
            "input_zIorjrMc"
        ],
        "layoutDefinitions": {
            "layout_1": {
                "options": {
                    "display": "auto",
                    "height": 960,
                    "width": 1440
                },
                "structure": [
                    {
                        "item": "viz_NgmH6lHI",
                        "position": {
                            "h": 270,
                            "w": 1390,
                            "x": 10,
                            "y": 350
                        },
                        "type": "block"
                    },
                    {
                        "item": "viz_BcDlqy4I",
                        "position": {
                            "h": 50,
                            "w": 300,
                            "x": 20,
                            "y": 10
                        },
                        "type": "block"
                    },
                    {
                        "item": "viz_zUx2Zt29",
                        "position": {
                            "h": 100,
                            "w": 680,
                            "x": 1470,
                            "y": 10
                        },
                        "type": "block"
                    },
                    {
                        "item": "viz_5ZrDUHwo",
                        "position": {
                            "h": 270,
                            "w": 1390,
                            "x": 10,
                            "y": 60
                        },
                        "type": "block"
                    }
                ],
                "type": "absolute"
            }
        },
        "tabs": {
            "items": [
                {
                    "label": "New tab",
                    "layoutId": "layout_1"
                }
            ]
        }
    },
    "applicationProperties": {
        "collapseNavigation": true,
        "hideEdit": false,
        "hideExport": false,
        "hideOpenInSearch": false
    }
}

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[bowesmana · Accepted solution]

You can't really use a time picker token like that because it is unlikely to be an epoch, so the trick is to use

| addinfo

and you will get some info_* fields that will represent the time picker used to control that search, e.g. info_min_time and info_max_time.

Then you can use those in the relative_time statement. You could also just do the math and say in your subsearch

[
  | makeresults
  | addinfo
  | eval go_back_days=if(strftime(info_min_time, "%a")="Mon", 3, 1)
  | eval earliest=info_min_time - (go_back_days * 86400), 
         latest=info_max_time - (go_back_days * 86400)
  | fields earliest latest 
]

Note use fields, when you want fields and table if you need to render something, which in this case, you don't.

[wodrog]

Thanks, this solved the issue and now my dashboard is more dynamic instead of the hardcoded time frames I initially was using.

[bowesmana]

@wodrog note that @livehybrid solution of using hidden searches is also a good alternative to putting it in a subsearch - both techniques work and, as you can see, use the same principle of | addinfo to get the master time picker search range as an epoch time.