Multi Select dropdown: How to create a CSV file that populates a standard dropdown?

[jimdaulton]

I have a CSV file that populates a standard dropdown .

The selection made, in this standard dropdown, then populates the second dropdown which is a Multi-Select.

I then use the token, from the Multi-Select, to perform my search.

This works great when there is only one item selected in the Multi-Select dropdown, however, when multiple items are selected it returns incorrect results.

Any help would be most appreciated!

This is my source code:

<form version="1.1">
<label>JRD AUR Divert Zone Multi Dropdown (CSV) Counts by Downtime</label>
<description>Testing for CSV Dropdown Functionality</description>
<fieldset submitButton="false" autoRun="false">
<input type="time" token="field1" searchWhenChanged="true">
<label>Time Selection</label>
<default>
<earliest>-24h@h</earliest>
<latest>now</latest>
</default>
</input>
<input type="dropdown" token="Locationid_tok" searchWhenChanged="false">
<label>Select Bins Location</label>
<fieldForLabel>Locationid</fieldForLabel>
<fieldForValue>Locationid</fieldForValue>
<search>
<query>| inputlookup AUR_Bin_Divert_Zones.csv | dedup Locationid | table "Locationid"</query>
<earliest>0</earliest>
<latest></latest>
</search>
<choice value="*">All</choice>
</input>
<input type="multiselect" token="Zoneid_tok" searchWhenChanged="true">
<label>Select Divert Zone(s)</label>
<fieldForLabel>Zoneid</fieldForLabel>
<fieldForValue>Zoneid</fieldForValue>
<search>
<query>| inputlookup AUR_Bin_Divert_Zones.csv | search Locationid="$Locationid_tok$" | table "Zoneid"</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<choice value="*">All</choice>
<delimiter> OR </delimiter>
</input>
</fieldset>
<row>
<panel>
<table>
<search>
<query>index=5_ip_cnv sourcetype=ftae_hmi_alarms $Zoneid_tok$
|eval Time=_time
|transaction Alarm startswith=*$Zoneid_tok$",1,0,192" endswith=*$Zoneid_tok$",0,0,192" maxevents=2
|eval Downtime = strftime(duration, "%M:%S")
|makemv delim=";" Message
|eval EventType=mvindex(Message,0)
|rename Description as EventLocation
|eval Date=lower(strftime(_time,"%+"))
|eval date_wday=lower(strftime(_time,"%A"))
|eval date_hour_EST=tonumber(strftime(_time, "%H"))
|where NOT (date_wday="saturday" OR date_wday="sunday")
|where (date_hour_EST&gt;=9 AND date_hour_EST&lt;19)
|rename Downtime as "Downtime in Minutes:Seconds"
|sort +Time
|table Date EventType EventLocation "Downtime in Minutes:Seconds"
</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">60</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">none</option>
<option name="percentagesRow">false</option>
<option name="refresh.display">progressbar</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<format type="number" field="Time">
<option name="precision">3</option>
<option name="useThousandSeparators">false</option>
</format>
</table>
</panel>
</row>
</form>

[bowesmana]

You don't have any field constraint or prefix/suffix values in your ZoneId_tok token, so this search query

index=5_ip_cnv sourcetype=ftae_hmi_alarms $Zoneid_tok$
|eval Time=_time
|transaction Alarm startswith=*$Zoneid_tok$",1,0,192" endswith=*$Zoneid_tok$",0,0,192" maxevents=2

will translate into

index=5_ip_cnv sourcetype=ftae_hmi_alarms ZONE_A OR ZONE_B OR ZONE_C...
|eval Time=_time
|transaction Alarm startswith=*ZONE_A OR ZONE_B OR ZONE_C...",1,0,192" endswith=*ZONE_A OR ZONE_B OR ZONE_C...",0,0,192" maxevents=2

the first search line may be fine with your data if you are just looking for those words in your raw data, but I expect that you do not have events that have the startswith and endswith strings with the expanded token string.

Without seeing an example of your data, I suspect you do not need to specify the zone data in the startswith and endswith strings. 

On a separate note regarding transaction, it can silently give you wrong results if your data set is large, as it will have to hold onto partial transactions until it finds an end event, so if you have long durations, you can potentially end up with results that are wrong.

It is generally possible to use stats to replace transaction which can achieve the same thing, but doing so requires some knowledge of your data.

[jimdaulton]

Thank you for your reply bowesmana,

You are correct that I do not have events that have the startswith and endswith strings with the expanded token string.

I am attaching a screenshot of some data. Bear in mind that the Zone can range from 111 through 347.

For future expansion reasons I would really like to utilize the 

startswith=*$Zoneid_tok$",1,0,192" endswith=*$Zoneid_tok$",0,0,192"

if at all possible (where the 1 is the beginning of the event and the 0 is the end of the event).

I truly appreciate any help that you can provide.

[bowesmana]

So, it seems like your zones repeat themselves.

Here is an example of using your data. You can paste this example into your search

| makeresults
| eval x=split("2023-09-18 11:22:05.9145992, E7F93BB1-608A-4D2F-AF34-0ED1AB279A65, AUR MCPA Alarm 16,2, Full; Bins East; Level 1; Divert Row 057; Zone 113,1,0,192###2023-09-18 11:31:35.7205659, 2C8701D0-7B9D-4F99-8679-A4F3F98086C9, AUR MCPA Alarm 16,2, Full; Bins East; Level 1; Divert Row 057; Zone 113,0,0,192###2023-09-18 11:36:24.1803900, 0C07C755-C59B-4E9F-92A6-E60EC1790E00, AUR MCPA Alarm 14,2, Full; Bins East; Level 1; Divert Row 223; Zone 121,0,0,192###2023-09-18 12:00:27.1437935, 0BE15F46-AA1E-46D2-97FF-5E8F68EC4415, AUR MCPA Alarm 14,2, Full; Bins East; Level 1; Divert Row 223; Zone 121,1,0,192###2023-09-18 12:00:37.1563574, 67E5E8C7-3D36-41C9-9062-F71AF3481012, AUR MCPA Alarm 14,2, Full; Bins East; Level 1; Divert Row 223; Zone 121,0,0,192###2023-09-18 12:00:47.1724708, 39C5326A-B2B6-478A-9756-8FAD049074C9, AUR MCPA Alarm 13,2, Full; Bins East; Level 1; Divert Row 227; Zone 122,1,0,192###2023-09-18 12:00:55.1835517, 7C060FE4-3441-4BEB-AFFE-97D8E0E5F324, AUR MCPA Alarm 13,2, Full; Bins East; Level 1; Divert Row 227; Zone 122,0,0,192###2023-09-18 12:03:27.3790874, B40D0D99-8E60-4AC8-8F34-2DA037945463, AUR MCPA Alarm 24,2, Full; Bins East; Level 1; Divert Row 121; Zone 117,1,0,192###2023-09-18 12:03:31.3853304, B72D54D5-B7B8-4928-83D2-DF64FAAD52BD, AUR MCPA Alarm 24,2, Full; Bins East; Level 1; Divert Row 121; Zone 117,0,0,192###2023-09-18 12:11:28.9249859, 3323D5D6-98BE-4867-86D9-7068225C44E6, AUR MCPA Alarm 19,2, Full; Bins East; Level 1; Divert Row 095; Zone 116,1,0,192###2023-09-18 12:11:32.9266932, 32C54B9A-03E1-4E70-9F6E-F34FF4D4EF8D, AUR MCPA Alarm 19,2, Full; Bins East; Level 1; Divert Row 095; Zone 116,0,0,192###2023-09-18 12:20:34.8242708, 1231E232-07F7-40F6-8CC0-23A80D9693DA, AUR MCPA Alarm 14,2, Full; Bins East; Level 1; Divert Row 223; Zone 121,1,0,192###2023-09-18 12:21:01.8614482, D807C593-5F41-44F3-9BEA-601BCEA45A96, AUR MCPA Alarm 14,2, Full; Bins East; Level 1; Divert Row 223; Zone 121,0,0,192###2023-09-18 12:41:58.6150128, 04A9F0AC-34E2-4514-9301-E607F5B90DBB, AUR MCPA Alarm 14,2, Full; Bins East; Level 1; Divert Row 223; Zone 121,1,0,192###2023-09-18 12:42:16.6309373, DAF119E7-8BE5-4B14-AF98-EC34F52CF343, AUR MCPA Alarm 14,2, Full; Bins East; Level 1; Divert Row 223; Zone 121,0,0,192###2023-09-18 12:45:56.3032344, CF2988F9-7354-4C6F-A320-ED50AF43F149, AUR MCPA Alarm 14,2, Full; Bins East; Level 1; Divert Row 223; Zone 121,1,0,192###2023-09-18 12:48:22.3814934, F12CAAFE-8861-40A5-8763-EDF02C25722F, AUR MCPA Alarm 14,2, Full; Bins East; Level 1; Divert Row 223; Zone 121,0,0,192###2023-09-18 12:49:10.4169289, C72DB2E5-A7E6-471C-8BAC-280A91E28338, AUR MCPA Alarm 14,2, Full; Bins East; Level 1; Divert Row 223; Zone 121,1,0,192###2023-09-18 12:53:18.5610031, 4C8CAF70-1A73-4318-A0DF-B42F76352277, AUR MCPA Alarm 18,2, Full; Bins East; Level 1; Divert Row 257; Zone 123,1,0,192###2023-09-18 12:53:56.5822544, 9D2E9472-7FCF-4266-A7C5-76942F4E9D71, AUR MCPA Alarm 18,2, Full; Bins East; Level 1; Divert Row 257; Zone 123,0,0,192###2023-09-18 12:57:56.9627790, CC8B059B-5A4F-46CE-9CB2-0E6F98E95A1B, AUR MCPA Alarm 13,2, Full; Bins East; Level 1; Divert Row 227; Zone 122,1,0,192###2023-09-18 13:01:11.2381480, ECC5639E-14DA-4067-9874-DAC23B56F50A, AUR MCPA Alarm 13,2, Full; Bins East; Level 1; Divert Row 227; Zone 122,0,0,192", "###")
| mvexpand x
| rename x as _raw
| eval _time=strptime(_raw, "%F %T.%Q")
| sort - _time
| fields _time _raw
``` The above creates your data set ```
``` Extract the zone and state ```
| rex "Zone (?<zone>\d+),(?<state>\d)"
``` Now look for 2 events per transaction ```
| transaction maxevents=2 zone startswith=eval(state=1) endswith=eval(state=0)

If you set up a field extraction to extract zone and state automatically, you can then search for zone=X or zone=Y in the search and then the transaction command is simple.

Note that transaction has limitations and the "length" of your transactions is quite long, so you should look at using some kind of stats to evaluate these.

[bowesmana]

Here's another way to find those transaction - replace transaction with this

| streamstats global=f reset_after="state=1" range(_time) as duration list(_raw) as events count as eventcount by zone
| where state=1
| table _time events zone state duration eventcount

[jimdaulton]

@bowesmana It looks like you are very knowledgeable in Splunk, and possibly spent quite a bit of time on your replies, and I really appreciate your help. No wonder you have earned so many badges!

I tried what you suggested, and probably due to my inexperience, it comes back with no results every time.

I can run a query, that does not use dropdowns, that works how I'd like it but I am trying to simplify things for my maintenance teammates that do not have Splunk knowledge so that they just select items from dropdowns.

If you can spare a little more time I would truly appreciate it.

An example of the results I am looking for are shown below (the EventLocation is pulled from a message).

[bowesmana]

Can you share what is working for the search and a further example of what is not. If tokens/dropdown is not working, it will be related to the data - your original example of token usage would not work, so that has to change.