Hello ,
I have an event log being printed as :
Mon May 24 07:15:19 PDT 2021 buildAndExecuteQuery query SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE :1 OR emp:Region LIKE :2) AND emp.department LIKE :3 with parameters [%region07%, %region09%,%0840%].
I m trying to extract an sql query field with the following desired output
SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE : "region07" OR emp:Region LIKE :"region09") AND emp.department LIKE :"0840 "
Tried extracting the input variable fields and the parameter fields using rex command command and not able to move further on how to replace the value in the query .
Any help would be appreciated.
Thanks in advance.
Harris
Minor improvements so that map is only run once
| makeresults
| eval _raw="Mon May 24 07:15:19 PDT 2021 buildAndExecuteQuery query SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE :1 OR emp:Region LIKE :2) with parameters [%region07%, %region09%].
Mon May 24 07:15:19 PDT 2021 buildAndExecuteQuery query SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE :1 OR emp:Region LIKE :2) AND emp.department LIKE :3 with parameters [%region07%, %region09%,%0840%]."
| multikv noheader=t
| fields _raw
| fields - _time
| rex "query (?<query>.*)\s+with\sparameters\s\[(?<parameters>[^\]]+)\]"
| rex field=parameters max_match=0 "%(?<parameter>[^%]+)%"
| eval count=mvcount(parameter)
| eventstats max(count) as countmax
| eval expanded=query
| rex field=expanded mode=sed "s/ :(?<d>\d+)/ :param_\1 /g"
| appendpipe
[| `generate_fields("param",countmax)` ]
| reverse
| filldown param_*
| where isnotnull(_raw)
| foreach param_*
[| eval expanded=if(count >= <<MATCHSTR>>,replace(expanded," :<<FIELD>> "," :".mvindex(parameter,<<FIELD>>-1)), expanded)]
| fields - count countmax param*
| reverse
generate_fields
eval i=mvjoin(mvrange(1,$count_INPUT$+1)," ") | map maxsearches=1 search="| `generate_fields_inner($prefix_INPUT$,$$i$$)`"
No changes to generate_fields_inner
@ITWhisperer : Thank you for the pointer. The query which I have given was an example of 3 parameters, but there are scenarios where we do have parameters ranging from 2-30 in that manual hard code of parameters would not work 😞
This can be done with the use of macros
Firstly, the changes to the search
| makeresults
| eval _raw="Mon May 24 07:15:19 PDT 2021 buildAndExecuteQuery query SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE :1 OR emp:Region LIKE :2) with parameters [%region07%, %region09%].
Mon May 24 07:15:19 PDT 2021 buildAndExecuteQuery query SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE :1 OR emp:Region LIKE :2) AND emp.department LIKE :3 with parameters [%region07%, %region09%,%0840%]."
| multikv noheader=t
| fields _raw
| fields - _time
| rex "query (?<query>.*)\s+with\sparameters\s\[(?<parameters>[^\]]+)\]"
| rex field=parameters max_match=0 "%(?<parameter>[^%]+)%"
| eval count=mvcount(parameter)
| eval expanded=query
| rex field=expanded mode=sed "s/(?<param> :\d+)/\1 /g"
| streamstats count as row
| appendpipe
[| `generate_fields("param",count)`
| streamstats count as row ]
| stats list(*) as * list(_*) as _* by row
| foreach param_*
[| eval expanded=if(count >= <<MATCHSTR>>,replace(expanded," :".<<FIELD>>." "," :".mvindex(parameter,<<FIELD>>-1)), expanded)]
| fields - count param* row
The first part sets up some example data - I duplicated the rows more or less to show it working with different numbers of parameters.
The next part splits off the query string and parameters as splits the parameters into a mv field
Next count the number of parameters for each event and take a copy of the query string (the copy isn't strictly necessary but does make it easier to see the before and after).
Then add a space after each of the :parameters to help distinguish 1 from 10 for example.
Tag each event with a row number so that the generated fields can be joined back up. There is possibly another way to do this, but I haven't worked that one through.
Now, appendpipe calling the macro (to be defined later) and tagging these additional events in the same manner.
Join the events up again using the row as a common field.
Now for each of the added fields, replace the corresponding placeholder in the query string with the corresponding parameter value.
Now for the macro, or rather macros. The first macro - generate_fields - is defined as such
eval i=mvjoin(mvrange(1,$count_INPUT$+1)," ")
| map maxsearches=1000 search="| `generate_fields_inner($prefix_INPUT$,$$i$$)`"
This macro takes two named arguments - prefix_INPUT,count_INPUT
Note that it uses map with maxsearches=1000, this is to avoid potentially crippling splunk. Also, this macro calls another macro - generate_fields_inner - which does the bulk of the work. This first macro is designed to expand the count to a string of space separated values
The second macro - generate_fields_inner - is defined as such
makeresults | fields - _time
| foreach $integer_list_value_INPUT$
[| eval _counter = if(isnull(_counter),1,_counter + 1)
| eval $prefix_INPUT$_{_counter}=_counter]
| fields - _counter
This macro also takes two arguments - prefix_INPUT,integer_list_value_INPUT
This macro is designed to generate fields based on the integers in the list containing the corresponding integer. This allows the main search to use the value (-1) to index into the parameter list to extract the corresponding argument value.
Thanks to @woodcock for the inspiration for this solution.
Minor improvements so that map is only run once
| makeresults
| eval _raw="Mon May 24 07:15:19 PDT 2021 buildAndExecuteQuery query SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE :1 OR emp:Region LIKE :2) with parameters [%region07%, %region09%].
Mon May 24 07:15:19 PDT 2021 buildAndExecuteQuery query SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE :1 OR emp:Region LIKE :2) AND emp.department LIKE :3 with parameters [%region07%, %region09%,%0840%]."
| multikv noheader=t
| fields _raw
| fields - _time
| rex "query (?<query>.*)\s+with\sparameters\s\[(?<parameters>[^\]]+)\]"
| rex field=parameters max_match=0 "%(?<parameter>[^%]+)%"
| eval count=mvcount(parameter)
| eventstats max(count) as countmax
| eval expanded=query
| rex field=expanded mode=sed "s/ :(?<d>\d+)/ :param_\1 /g"
| appendpipe
[| `generate_fields("param",countmax)` ]
| reverse
| filldown param_*
| where isnotnull(_raw)
| foreach param_*
[| eval expanded=if(count >= <<MATCHSTR>>,replace(expanded," :<<FIELD>> "," :".mvindex(parameter,<<FIELD>>-1)), expanded)]
| fields - count countmax param*
| reverse
generate_fields
eval i=mvjoin(mvrange(1,$count_INPUT$+1)," ") | map maxsearches=1 search="| `generate_fields_inner($prefix_INPUT$,$$i$$)`"
No changes to generate_fields_inner
@ITWhisperer : Thank you for the solution , it has worked with different iterations.
I found a simpler solution - the ":" are in the mvzip and trim because they appear in your example but I am not sure if they are necessary - it is assumed with this solution that the parameters in the original query string are in ascending order - if not, you could do something about rearranging the parameter mv field so that this is in the corresponding order are the placeholders in the query string
| makeresults
| eval _raw="Mon May 24 07:15:19 PDT 2021 buildAndExecuteQuery query SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE :1 OR emp:Region LIKE :2) with parameters [%region07%, %region09%].
Mon May 24 07:15:19 PDT 2021 buildAndExecuteQuery query SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE :1 OR emp:Region LIKE :2) AND emp.department LIKE :3 with parameters [%region07%, %region09%,%0840%]."
| multikv noheader=t
| fields _raw
| fields - _time
| rex "query (?<query>.*)\s+with\sparameters\s\[(?<parameters>[^\]]+)\]"
| rex field=parameters max_match=0 "%(?<parameter>[^%]+)%"
| eval parameter=mvappend(parameter,"")
| eval expanded=split(replace(query,":\d+","#"),"#")
| eval expanded=mvzip(expanded,parameter,":")
| eval expanded=mvjoin(expanded,"")
| eval expanded=trim(expanded,":")
| makeresults
| eval _raw="Mon May 24 07:15:19 PDT 2021 buildAndExecuteQuery query SELECT emp.Name,emp.age,emp.address from emp where (emp.Region LIKE :1 OR emp:Region LIKE :2) AND emp.department LIKE :3 with parameters [%region07%, %region09%,%0840%]."
| rex "query (?<query>.*)\s+with\sparameters\s\[%(?<one>[^%]+)%,\s*%(?<two>[^%]+)%,\s*%(?<three>[^%]+)%\]"
| eval query=replace(query,":1",":\"".one."\"")
| eval query=replace(query,":2",":\"".two."\"")
| eval query=replace(query,":3",":\"".three."\"")