topic Re: How to filter rows based on a regex match on all fields? in Splunk Search

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

DanK — Fri, 11 Sep 2020 02:31:14 GMT

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.

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

renjith_nair — Fri, 11 Sep 2020 04:59:41 GMT

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"

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

DanK — Fri, 11 Sep 2020 05:31:34 GMT

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?

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

renjith_nair — Fri, 11 Sep 2020 10:28:34 GMT

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

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

isoutamo — Fri, 11 Sep 2020 13:29:44 GMT

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

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

DanK — Fri, 11 Sep 2020 15:14:02 GMT

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.

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

DanK — Fri, 11 Sep 2020 15:05:16 GMT

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(<<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 <. 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.

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

isoutamo — Fri, 11 Sep 2020 15:32:21 GMT

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. “.” ?

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

DanK — Fri, 11 Sep 2020 16:17:22 GMT

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.

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

DanK — Fri, 11 Sep 2020 18:33:31 GMT

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.

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

isoutamo — Sat, 12 Sep 2020 07:54:17 GMT

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