Splunk Search

How to compare a dynamic list of possible names in a drop-down to the token value for searching events?

Path Finder

Background
I have a dashboard with a drop-down box that is populated with the names of queues which i get from an index of events for these queues. I then pass the token produced by the user selecting one or more names to a statistics box which should then return all the results in the index relating to those names.

Where I run into trouble

I can't for the life of me find a way to compare the name field given by an event to the token value. I have tried constructing a regex value with wildcards and the name value, many variations on "where $Names_tok$ like name" and subsearches but i haven't had any luck. Here is the search i used when i was pulling directly from the database which works fine:

    | dbquery "Database" "select "name", "time_stamp" ,"inboundTotalBytes"                                    
    from $table_tok$ where ("time_stamp" > sysdate-$time_tok$) and '$Names_tok$' like  '%'||"name"||'%' "
    | rename time_stamp as _time                   
    | sort +_time +"name"
    | table _time name inboundTotalMessages inboundTotalBytes pendingMessageCount pendingMessageSize
0 Karma
1 Solution

Path Finder

Turns out it was an ID10T issue where I was using 'like' incorrectly and with a quick reshuffle and some quotation marks it all works fine. For anyone interested the final search ended up looking like this:

            index=$table_tok$             
            | eval StartTime = relative_time(NOW(),"-1d")         
            | eval tokenstring="$field1$"         
            | where (StartTime < time_stamp) and like(tokenstring, "%"+name+"%")           
            | rename time_stamp as _time            
            | sort +_time +name            
            | table _time name TotalMessages TotalBytes

Thanks for the help guys!

View solution in original post

0 Karma

Path Finder

Turns out it was an ID10T issue where I was using 'like' incorrectly and with a quick reshuffle and some quotation marks it all works fine. For anyone interested the final search ended up looking like this:

            index=$table_tok$             
            | eval StartTime = relative_time(NOW(),"-1d")         
            | eval tokenstring="$field1$"         
            | where (StartTime < time_stamp) and like(tokenstring, "%"+name+"%")           
            | rename time_stamp as _time            
            | sort +_time +name            
            | table _time name TotalMessages TotalBytes

Thanks for the help guys!

View solution in original post

0 Karma

Champion

what type of database are you querying? The syntax looks different than MSSQL, so this probably won't help, but I would typically use something like "| dbquery db "select .... where name in $Names_tok$". And so then in my dropdown settings, I would just specify all of the prefix/sufix values so that my token would look as needed, e.g. "('name','name2,'some other name', 'etc')"

0 Karma

Path Finder

@Maciep: Its an oracle database, and sorry I should clarify the project I am working on requires the dashboard to be populated from an index so the this syntax is now obsolete, I just thought it would be useful to show a working example of what im trying to replace.

@Rsennet_splunk: He is the syntax of the dropdown box.

<input type="multiselect" token="field1">
      <label>Service</label>
      <selectFirstChoice>true</selectFirstChoice>
      <populatingSearch fieldForValue="name" fieldForLabel="name">index="collectivequeue" 
        | eval StartTime = relative_time(NOW(),"-1d") 
        | where (StartTime &lt; time_stamp) 
        | rename time_stamp as _time 
        | sort +_time +name 
        | dedup name 
        | table name</populatingSearch>
    </input>

From what I can tell the token is a string of all the selected names (separated by spaces) which explains why I can use the '$Names_tok$' like '%'||"name"||'%' syntax in my oracle query.

Basically this question boils down to how can I filter the results I'm getting back by comparing the name field in each event with a string containing lots of names. I believe the only place this is possible would be in the where clause but I am unable to use wildcards/regex in this section to allow name to match a string containing that name (the token).

0 Karma

Splunk Employee
Splunk Employee

It might help if you showed us the syntax for the dropdown input in the dashboard. I suspect the token you are creating doesn't look like you think it does... especially if you have been trying and trying, things that you know should work. Some of the settings in the edit dialog like "Field for Value" and "Field for Label" if misinterpreted can produce a token that just simply has the wrong info in it. The easiest thing to do would be to test it in the dashboard. Create a simple Splunk search and put the token somewhere left of the first pipe so it can't hurt anything. create a search like:
index=internal $Nametok$| stats count by sourcetype
create that in a "statistics" panel, and hook it up to your dropdown...
When it runs, hover over the lower left hand corner of the panel and click the magnifying glass to open the resulting search in the search view. You'll see your token value to the left of the pipe and then can be sure that you've got it right...

With Splunk... the answer is always "YES!". It just might require more regex than you're prepared for!
0 Karma