Splunk Dashboard: use tokens in dropdown query source inputlookup

[spisiakmi]

Hi, here is the description.

In a dashboard there is time range picker with its token time_tkn and 2 dropdowns.

1. The first dropdown is connected with the time_tkn (index=index1 host=host1 earliest="$time_tkn.earliest$" latest="$time_tkn.latest$"). Output is a varchar list. This dropdown has its token (dropdown1_tkn). Example: selected value from the dropdown is Text1 Text2. So dropdown1_tkn=”Text1 Text2”
2. There is lookup table table1, which has these important columns:
   workplace, orderId, start_date, start_time, end_date, end_time. start_date, end_date have this format: %d.%m.%Y. start_time, end_time have this format: %H:%M:%S
3. Output in dropdown should be a list of orderId.
4. Here is condition for the list of the dropdown2: to select every orderId from lookuptable, where workplace=$dropdown1_tkn$ and start_date+start_time>=$time_tkn.earliest$ AND end_date+end_time<=$time_tkn_latest$

Can anybody help, please?

[cartergray70543]

Use strptime() to combine the lookup’s date/time fields, then compare them with $time_tkn.earliest$ and $time_tkn.latest$. Filter by workplace="$dropdown1_tkn$" and return the matching orderId values.

[ITWhisperer]

In order to do time range searches, you probably need to convert your start and end times to internal format with strptime e.g.

| eval startdatetime=strptime(start_date."T".start_time,"%d.%m.%YT%H:%M:%S")
| eval enddatetime=strptime(end_date."T".end_time,"%d.%m.%YT%H:%M:%S")

You then need to do a similar thing to your time_tkn values

[spisiakmi]

Here is the code:

| inputlookup table1.csv
| eval start_epoch=strptime(start_date."T".start_time,"%d.%m.%YT%H:%M:%S")
| eval end_epoch=strptime(end_date."T".end_time,"%d.%m.%YT%H:%M:%S")
| where workplace="$dropdown1_tkn$"
| where start_epoch>= tonumber("$time_tkn.earliest$")
AND end_epoch<= tonumber("$time_tkn.latest$")
| dedup orderId
| table orderId

not working unfortunately

[ITWhisperer]

You can't just use tonumber on a time token and expect it get the internal value for time. You could try something like this though

| inputlookup table1.csv
| eval start_epoch=strptime(start_date."T".start_time,"%d.%m.%YT%H:%M:%S")
| eval end_epoch=strptime(end_date."T".end_time,"%d.%m.%YT%H:%M:%S")
| where workplace="$dropdown1_tkn$"
| addinfo
| where start_epoch>= info_min_time
AND end_epoch<= info_max_time
| dedup orderId
| table orderId

[spisiakmi]

yes I tried. Here is the error msg: Could not create search.
If tried also | where workplace="$dropdown1_tkn|s$". No way.
If I remove 

| where workplace="$dropdown1_tkn$"

error msg is: Search produced no results.

If I run it with 

it runs in Search and gives me results of orderId.

[ITWhisperer]

You don't need double quotes and |s token filter. Try

| where workplace=$dropdown1_tkn$

Alternatively, check that the token is being set as expected (temporarily set the title of the input to be the token so you can see it change).

Also, do you have an initial value set for the input?

[spisiakmi]

Ok. Its working
When I add
| addinfo
| where start_epoch>= info_min_time
AND end_epoch<= info_max_time

....-> Search produced no results.

[ITWhisperer]

Run the search and see if it  is as you would expect

[spisiakmi]

yes, the search gives me proper results after run.

It looks like the dropdown doesn't recognize | addinfo
Because after showing info_min_time and info_max_time in title of any object, always appears: 0.000 +Infinity. And it doesn't matter, which time range I pick with the time picker.

[ITWhisperer]

Please share the source of your dashboard, particularly the inputs

[spisiakmi · Accepted solution]

this code is running perfect:

| inputlookup table1.csv
| eval start_epoch=strptime(start_date."T".start_time,"%d.%m.%YT%H:%M:%S")
| eval end_epoch=strptime(end_date."T".end_time,"%d.%m.%YT%H:%M:%S")
| eval earliest=relative_time(now(), "$time_tkn.earliest$")
| eval latest=now()
| eval token=$dropdown1_tkn$
| where start_epoch<=latest AND end_epoch>=earliest
| where workplace=token
| table orderId

| addinfo doesn't work in dropdown code. so my test.

ITWhisperer thank you very much for your support.