topic Re: How to replace query input variables and replace with variable values. in Splunk Dev

How to replace query input variables and replace with variable values.

harry3988 — Mon, 24 May 2021 14:51:37 GMT

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

Re: How to replace query input variables and replace with variable values.

ITWhisperer — Mon, 24 May 2021 15:17:59 GMT

| 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."\"")

Re: How to replace query input variables and replace with variable values.

harry3988 — Mon, 24 May 2021 17:25:47 GMT

@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 😞

Re: How to replace query input variables and replace with variable values.

ITWhisperer — Tue, 25 May 2021 13:23:19 GMT

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.

Re: How to replace query input variables and replace with variable values.

ITWhisperer — Tue, 25 May 2021 21:02:09 GMT

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

Re: How to replace query input variables and replace with variable values.

harry3988 — Fri, 28 May 2021 08:11:44 GMT

@ITWhisperer : Thank you for the solution , it has worked with different iterations.

Re: How to replace query input variables and replace with variable values.

ITWhisperer — Sat, 29 May 2021 08:12:30 GMT

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,":")