I've got a lookup table that consists of two columns; "Description" and "PCRE". What I'm looking to do is search my proxy data and, if the regex from the PCRE column matches the _raw data, add the corresponding value of the "Description" column to the results. The CSV contains over 200 regular expressions and is formatted something like this:
Description, PCRE
D1, http:\/\/[^\abc]$
D2, http:\/\/[^\xyz]$
D3, http:\/\/[^\123]$
I'm using the following search which will provide all hits, but I can't seem to figure out how to add in the Description field from the lookup:
index=proxy
| fields _time user action src dest_ip dest bytes_out bytes_in uri_path http_referrer
| regex [| inputlookup pcre_list
| rename "PCRE" as search
| fields search
| format
| table search]
| stats count by src dest
Your fields command & table command are only returning the search field. Add the Description field.
Not sure why you even have fields command here. Your table command is achieving the same result.
index=proxy
| fields _time user action src dest_ip dest bytes_out bytes_in uri_path http_referrer
| regex [| inputlookup pcre_list
| rename "PCRE" as search
| fields search Description
| format
| table search Description]
| stats count by src dest
Also note some of these commands have limits, and you may want to use the job inspector if you're seeing a hard stop at a number like 50000 or 1000, etc.. I'm currently battling a report that only spits 514 results out once put through a table command... no clue why but 514 is a UDP port... so I'm thinking i've got a broken inputs stanza or something. rant over.
I can't add the Description field because the results of that subsearch are being fed to the regex command.
Maybe I'm trying to reinvent the wheel here- is it possible to do a lookup on a field against a regex within a lookup table? I'm testing now with match_type=WILDCARD but it doesn't seem to be working.
The end result I'm looking for is, if the uri_path or http_referrer fields from the proxy data match the regex in the CSV PCRE column, it will add the Description values from the CSV to the results.
index=proxy
| inputlookup pcre_list #<-this would return Description and search (the regex) in theory
| eval Description=if(match(uri_path,search),Description,"No Regex Match") #<-this would use 'search' (the regex) to match on 'uri_path' which is found in index=proxy. If 'uri_path' matched the regex, 'Description' would take the value of 'Description' (which was returned from the lookup. basically saying 1=1), however if 'uri_path' didn't match the regex, 'Description' would become "No Regex Match".
| table Description search src dest #<- here we'd table 'Description' and 'search', and you should add the other fields you want to run your stats command on like 'src' and 'dest'.
| stats count by src dest Description
something like this.. where you use if(match... then return the description
I just dont think its going to work like that.... I'll have to do it in dev to see how.
I had tried the eval if(match) but couldn't get it to work, I'm going to revisit it again this morning to see what I might have missed. Thanks for your feedback so far!