How to filter query results by a lookuptable containing regex's?

[mertox]

I am trying to filter query results based on regex. They are stored within a lookuptable like this:

path
/etc/group
/etc/passwd
/etc/rc0.d/*
/etc/rc1.d/*
the first step would be to replace all "/" with "\/" to get a valid regex and concat them using "|" later on. The query I would like to use would look like:

| from datamodel:"Change_Analysis"."Filesystem_Changes"
| eval path = file_path + file_name
| eval path_regex = [| inputlookup security_related_paths | eval path = replace(path, "/", "\/") | stats delim="|" values(path) as paths | mvcombine paths | return $paths]
| regex path=path_regex
I'm guessing there is a quite simple solution for this use case...

Thank you in advance!

[VatsalJagani · Accepted solution]

Hi @mertox,

| from datamodel:"Change_Analysis"."Filesystem_Changes"
| eval path = file_path.file_name
| regex path = [| inputlookup security_related_paths | stats values(path) as paths | eval path_regex=mvjoin(paths, "|")  | eval path_regex = "\"".path_regex."\"" | return $path_regex]

map command can also help here, would you mind trying this query as well?

| inputlookup security_related_paths 
| eval path = replace(path, "/", "\/") 
| stats values(path) as paths 
| eval path_regex=mvjoin(paths, "|") 
| map search="| from datamodel:\"Change_Analysis\".\"Filesystem_Changes\" | eval path = file_path.file_name | regex path=$path_regex$"

Try the above two queries selext the one which gives better performance. Hope this helps!!!

[mertox]

The first option fails with:

Error in 'SearchParser': Missing a search command before '\'. Error at position '10284' of search query 'litsearch ((index=* OR index=_*) index=auditd_file...{snipped} {errorcontext = tc\/group|\/etc\/gsha}'.

[VatsalJagani]

Hi @mertox, I've removed | eval path = replace(path, "/", "\/") from the query because I think it should work even without that in regex. Can you please try?

[mertox]

never the less. The map query takes around 2 seconds, the direct one doubles to execution time to around 4.

[VatsalJagani]

Awesome, map does great job then.

[mertox]

Hi @VatsalJagani, seems like this does not solve the issue:

Error in 'SearchParser': Missing a search command before '/'. Error at position '10283' of search query 'litsearch ((index=* OR index=_*) index=auditd_file...{snipped} {errorcontext = etc/group|/etc/gshado}'

but I found the our pitfall. This works quite good:

| from datamodel:"Change_Analysis"."Filesystem_Changes"
 | eval path = file_path.file_name
 | regex path=[| inputlookup security_related_paths | stats values(path) as paths | eval path_regex=mvjoin(paths, "|") | eval path_regex = "\"".path_regex."\"" | return $path_regex]

[VatsalJagani]

Thanks @mertox for finding this solution, I've edited query in my answer so other can get help from that.

[mertox]

works like a charm! Thank you a lot!

[VatsalJagani]

Glad to here that. I've added another query try that one as well let me know if that works and which gives you better performance.