Splunk Search

How to filter rows based on a regex match on all fields?

DanK
Explorer

Is it possible to filter search result rows by a search expression which can be applied to all fields of a row?

According to the documentation for regex it appears you should be able to use it without specifying a field:

 

| ...
| regex "some regex search string"

 

However when I give it a try, it yields no results.

I did find this while searching the internet:

 

| ...
| eval matchCount=0
| foreach * [eval matchCount = matchCount + if(match(<<FIELD>>, "my regex search string"), 1, 0) ]
| where matchCount > 0

 

 However I was wondering if there was a way to do this without adding the 'matchCount' column.

Labels (2)
0 Karma
1 Solution

isoutamo
SplunkTrust
SplunkTrust
You could surround your query by <![CDATA[ ..... ]]> to keep it more readable without use escaping characters.
Can you try ‘<<FIELD>>’ if those fields contains e.g. “.” ?

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

regex is applied on the field (if you specify) or on the _raw event by default .

Is your fields extracted ? Is it possible to apply the filter on the _raw in the base search part ?

e.g.

| makeresults |eval _raw="wewillsearchfor-this-intheevent"
|regex "this"

 

0 Karma

DanK
Explorer

Sorry i'm not super familiar with events but I don't believe i'm using them. The data source is from JDBC <-> MongoDB and is being accessed via:

 

| dbxquery query="SELECT ..."

 

Under the Search tool, results only appear under 'Statistics' and not 'Events'.

I'm also using this in the dashboard. I just tried the alternative I mentioned above and it doesn't seem to work. I am able to get it to work under the Search tool though.

Unfortunately, your suggestion of using _raw doesn't work. I tried the following but I get no result.

 

| dbxquery ...
| regex _raw="some regex"

 

Is there a way to | eval each row of data into a field so that i can do a regex search on that field?

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Ok if its extracted using dbConnect _raw will not work.

What's the issue while using the foreach solution? If its just about additional field, either you can remove it by fields - matchCount

0 Karma

DanK
Explorer

Unfortunately the issue isn't with the additional field. Removing it is fine and it's what I've been trying to do, but although the search works in the Search application, the exact same query doesn't work in the dashboard.

So this is working in Search:

| dbxquery query="SELECT ..." connection="..."
| where match(Status, ".*")
| eval matchCount = 0
| foreach * [eval matchCount = matchCount + if(match(<<FIELD>>, ".*"), 1, 0)]

but this is not in dashboard:

<form>
  <search id="mySearch">
    <query>
      | dbxquery ...
    </query>
  </search>
  ...
  <row>
    <panel>
      ...
      <table>
        <search base="mySearch">
          <query>
            | eval textSearchMatchCount = 0
            | foreach * [eval textSearchMatchCount = textSearchMatchCount + if(match(&lt;&lt;FIELD>>, "$textSearchFilter$"), 1, 0)]
            | where textSearchMatchCount > 1
            | fields - textSearchMatchCount
          </query>
        </search>
      </table>
    </panel>
  </row>
</form>

The dashboard was complaining about the two lesser-than characters of <<FIELD>> so i replaced then with &lt;. This works fine when either "" or ".*" string is used as the value of the $textSearchFilter$ token, but if I put a single other character, such as "P", there are zero results. Even "^.*$" works, but not "^.*P.*$" even though there are columns with the P character in them.

0 Karma

isoutamo
SplunkTrust
SplunkTrust
You could surround your query by <![CDATA[ ..... ]]> to keep it more readable without use escaping characters.
Can you try ‘<<FIELD>>’ if those fields contains e.g. “.” ?

View solution in original post

DanK
Explorer

Thanks for the <![CDATA[ ..... ]]> suggestion! It works great!

I tried surrounding the <<FIELD>> with single and double-quotes but it didn't make a difference. None of the field names have a period in it.

One thing I did notice though is that the search itself seems to tries to match against the field name instead of the field value. I tried a character 'S' in my search and it matched with every data row, but the value of the match column is '2' which is the same number of column names which have the 'S' character in them.

This is different from the behaviour in the Search app, perhaps this is a known difference or a bug.

0 Karma

DanK
Explorer

I'm not really sure what happened, maybe I needed to refresh after I used <![CDATA[ ..... ]]> or perhaps I had a typo in my implementation of the original foreach alternative. But this is working now.

isoutamo
SplunkTrust
SplunkTrust
Every time after you change dashboard it’s good to do refresh and some times even restart is mandatory.

isoutamo
SplunkTrust
SplunkTrust
Or should you change that dbxquery to avoid that kind of rows which you want throw away?
r. Ismo
0 Karma

DanK
Explorer

That's definitely an option but I consider it the last resort.

The reason for this is because I use the search as a base search for multiple panels in the dashboard. It's a larger more complex search so following the DRY principle I'd rather not copy it with slight modifications when there's an opportunity to add a filter to it.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!