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 uripath or httpreferrer 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