Getting Data In

How to create a search using search criteria stored in a lookup file

Adan12345
Explorer

Hello Community,

I have a lookup file policy_search.csv that has search criteria to find specific policy events in my data.  The file looks like this:

#, policy, search_criteria

1, policyA,  (policy="policyA") OR

2, policyB,  (policy="policyB" AND (protocol="X" OR protocol="Y")) OR

3, policyC, (policy="policyC" AND channel="ch1") OR

 

I want to produce a search like the one below, but using the criteria in the lookup:

index=events | search 

     (policy="policyA") OR

     (policy="policyB" AND (protocol="X" OR protocol="Y")) OR

     (policy="policyC" AND channel="ch1")

| table incident policy protocol channel 

How could I do that? the idea is to maintain the search criteria in the lookup file and have changes reflected automatically in our reports.

I'm looking for something like

index=events | search [| inputlookup policy_search.csv | stats values(search_criteria)]

| table incident policy protocol channel

 

I really appreciate any help.  Thank you very much!

Adan Castaneda

Labels (2)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Depending on how complex your search criteria are, you might be able to do something like this

| makeresults count=20
| eval policy="policy".mvindex(split("ABC",""),random()%3)
| eval protocol=mvindex(split("XYZ",""),random()%3)
| eval channel="ch".random()%5
``` the lines above generate some random data ```
| search 
    [``` next few lines would be replaced by your input lookup ```
    | makeresults
    | eval _raw="#, policy, search_criteria
1, policyA,  (policy=\"policyA\") OR
2, policyB,  (policy=\"policyB\" AND (protocol=\"X\" OR protocol=\"Y\")) OR
3, policyC, (policy=\"policyC\" AND channel=\"ch1\") OR"
    | multikv forceheader=1
    | fields - _raw _time linecount
    ``` depending on your real data, you may or may not need to trim the value in the policy column ```
    | eval policy=trim(policy)
    ``` you don't need the trailing OR in your search criteria as it gets added automatically later ```
    | eval search_criteria=trim(trim(search_criteria), " OR")
    ``` extract the field=value pairs from you search criteria ```
    | rex field=search_criteria max_match=0 "(?<namevalue>\w+=\"[^\"]*?\")"
    ``` convert these to single events - note this is where the limitations are since it will only work with simple search criteria e.g. A=X AND (B=Y OR B=Z) ```
    | mvexpand namevalue
    ``` convert to actual fields with values ```
    | eval _name=mvindex(split(namevalue,"="),0)
    | eval _value=mvindex(split(namevalue,"="),1)
    | eval {_name}=trim(_value,"\"")
    | fields - namevalue _name _value
    ``` regroup fields and values by original search criteria ```
    | stats values(*) as * by search_criteria
    | fields - search_criteria
    ``` format so it can be used in the search ```
    | format]

If you paste the above into the search and press <ctrl><shift>E you will see the expanded search showing the example query

If your search criteria are more complex, you may need to find another way to implement a parser for your criteria or try to break it down into smaller chunks.

View solution in original post

Adan12345
Explorer

Hello ITWhisperer,

Thank you very much for the great detail and explanation. you made my day!!!!

 

Regards,

Adan Castaneda

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Depending on how complex your search criteria are, you might be able to do something like this

| makeresults count=20
| eval policy="policy".mvindex(split("ABC",""),random()%3)
| eval protocol=mvindex(split("XYZ",""),random()%3)
| eval channel="ch".random()%5
``` the lines above generate some random data ```
| search 
    [``` next few lines would be replaced by your input lookup ```
    | makeresults
    | eval _raw="#, policy, search_criteria
1, policyA,  (policy=\"policyA\") OR
2, policyB,  (policy=\"policyB\" AND (protocol=\"X\" OR protocol=\"Y\")) OR
3, policyC, (policy=\"policyC\" AND channel=\"ch1\") OR"
    | multikv forceheader=1
    | fields - _raw _time linecount
    ``` depending on your real data, you may or may not need to trim the value in the policy column ```
    | eval policy=trim(policy)
    ``` you don't need the trailing OR in your search criteria as it gets added automatically later ```
    | eval search_criteria=trim(trim(search_criteria), " OR")
    ``` extract the field=value pairs from you search criteria ```
    | rex field=search_criteria max_match=0 "(?<namevalue>\w+=\"[^\"]*?\")"
    ``` convert these to single events - note this is where the limitations are since it will only work with simple search criteria e.g. A=X AND (B=Y OR B=Z) ```
    | mvexpand namevalue
    ``` convert to actual fields with values ```
    | eval _name=mvindex(split(namevalue,"="),0)
    | eval _value=mvindex(split(namevalue,"="),1)
    | eval {_name}=trim(_value,"\"")
    | fields - namevalue _name _value
    ``` regroup fields and values by original search criteria ```
    | stats values(*) as * by search_criteria
    | fields - search_criteria
    ``` format so it can be used in the search ```
    | format]

If you paste the above into the search and press <ctrl><shift>E you will see the expanded search showing the example query

If your search criteria are more complex, you may need to find another way to implement a parser for your criteria or try to break it down into smaller chunks.