Splunk Search

Searching wildcard on a field that can possibly have null values

centrafraserk
Path Finder

Hello everyone,

I am very close to a solution for my problem, but I am not quite there yet. I created a view that allows the user to search on multiple fields in our events, where each user input is defaulted to wildcard. I will use % instead of asterisk throughout because it throws off formatting. Initially hitting search would would bring up every event in the given time period. As you specified further terms for the fields it would narrow the results to a specific set of results based on the user inputs. This worked great until I added the ability to search on a field that has the possibility of containing a null value. Now including % for that field which can contains nulls leaves out every event that contains a null.

For example:

If searching:

 index=log user=* name=*  role=*

I might only receive [user1,name1,role1], when there is another event [user2,name2,null] which I would also like to display.

So in this case I added the logic:

index=log user=* name=*  (role=* OR NOT role=*)

This would return what I want, which is to include nulls. However, now consider the fact that this is a view with user inputs and that % is actually representing a variable $role$ where * was an input by the user. Now think of what happens when you enter an actual value into that field, for instance "role1"

index=log user=$user$ name=$name$  (role=$role$ OR NOT role=*)

becomes:

index=log user=* name=*  (role=role1 OR NOT role=*)

In this case when the user defines a value for role it will then return when role=role1 AND every event where a role is null. This is no good as we are trying to get just were it is equal to role1. I am trying to figure out how to make it so that in this case it will still only return a result for entered value but still include nulls when the value is %.

I would be grateful for any insight that could be provided. I suspect there may be a way to utilize fillnull for this but I am not sure how or where to include it.

1 Solution

cpetterborg
SplunkTrust
SplunkTrust

This should work (every event will have a role, even if it is "", after the eval😞

index=log user=$user$ name=$name$ | eval role=if(isnull(role),"",role) | search role=$role$

Might not, but you can easily try it out and see if it works

View solution in original post

cpetterborg
SplunkTrust
SplunkTrust

This should work (every event will have a role, even if it is "", after the eval😞

index=log user=$user$ name=$name$ | eval role=if(isnull(role),"",role) | search role=$role$

Might not, but you can easily try it out and see if it works

centrafraserk
Path Finder

This works perfectly. Thank you so much for taking the the time to answer. I was unaware that you could pipe to a new search after eval. You could do something similar with the fillnull command and pipe back to searching for the possible null variable. However, your solution works exactly as intended. Thanks again!

0 Karma

cpetterborg
SplunkTrust
SplunkTrust

I like to keep things simple, and this seemed to me to be the simplest solution. There are many ways to skin this cat, though. This way doesn't require much work, and no modifying the XML source. I don't know if it is the most efficient, but support-wise it is good.

Glad to help out!

0 Karma

lguinn2
Legend

Your default base search must include both nulls and non-nulls for the role, so it must look like this

index=log user=* name=* 

Now, when you use tokens, you need to change it to something like:

index=log user=$user$ name=$name$ $role_constraint$

You already have populated the drop-downs for user and name, so I won't repeat them here. The XML for the role constraint should look like this:

<input type="dropdown" token="role_constraint">
  <label>Select a role:</label>
  <choice value=" ">All</choice>
  <search>
    <query>
      index=log earliest=-24h | stats count by role | eval constraint="role=" . "\"" . role . "\""
    </query>
  </search>
  <fieldForLabel>role</fieldForLabel>
  <fieldForValue>constraint</fieldForValue>
</input>

This input inserts nothing but a blank space into the search if "All" is chosen - so even events without a role field will be selected. If the user selects a particular role, the role_constraint token will contain the entire string role="myRole"
the eval constraint="role=" . "\"" . role . "\"" may not be perfect, you may have to play around with it a bit to make it work...

And I am sure that this isn't the only way to do this, but I found that using the prefix and suffix options wouldn't really give you what you need, either.

centrafraserk
Path Finder

Thank you for taking the time to give such an in depth answer. Both yourself and cpetterborg came to the same conclusion, that you would need to fill the null values with a space. His answer was a little more plug and play, and since I was using Text Input boxes versus drop down, I opted to try his first. Your solution would work great for someone with the same issue though. I appreciate the time you put into this!

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...