How to pass urlparam value to KQL

[tangkalo]

Hi, I read about threads related to this need and know that there is currently not a way to directly plug in the value in the KQL using urlparam; however, is there a way to do it indirectly? I am thinking about using variables. It works in markdown using var_set and {var "variableName"} but I don't know the syntax of using {var "variableName"} in the KQL. I tried the following but it doesn't evaluate the '{var name="urlParameter"}' correctly(no result returned). Any tips will be apprepriated. Thanks.

| var_set name="requestMethodParam" value={urlparam param=requestMethod default="xxx"}
| var  name="requestMethodParam"
| essql 
  query={string "SELECT \"@timestamp\"
as dateTime, (perfElapseTime)/1000 as \"Time\"
FROM \"log*\"
WHERE filename = 'performance.log'
ND perfMethodName = '{var name=\"urlParameter\"}'
ORDER BY dateTime asc"
}

[wylie (Wylie Conlon)]

1. You're not using KQL in your example, but I can see that you are having issues with your SQL WHERE clause.

2. The {string function only concatenates strings, it does not evaluate expressions inside strings

3. Try this:

| var_set name="requestMethodParam" value={urlparam param=requestMethod default="xxx"}
| var  name="requestMethodParam"
| essql 
  query={string "SELECT \"@timestamp\"
as dateTime, (perfElapseTime)/1000 as \"Time\"
FROM \"log*\"
WHERE filename = 'performance.log'
ND perfMethodName = '"
{var name="urlParameter"}
"' ORDER BY dateTime asc"
}

[Felix_Roessel]

The issue is that you not used a new expression within your query string.

You need to end the first string using "
then it is evaluating your expression and concatinating the result String into the expression.

Here you can find examples die that

Kibana Canvas examples | Download now from Elastic content share

[tangkalo]

Thanks. Unfortunately it didn't work. Here's the final version based on your suggestion on the expression.

filters
| var_set name="requestMethodParam" value={urlparam param=requestMethod default="xxx"}
| var  name="requestMethodParam"
| essql 
  query={string "SELECT \"@timestamp\"
as dateTime, (perfElapseTime)/1000 as \"Time\"
FROM \"log*\"
WHERE filename = 'performance.log'
AND perfMethodName = '\"{var name=\"requestMethodParam\"}\"'
ORDER BY dateTime asc"

}

[tangkalo]

Actually I got this error.

Whoops! Expression failed
Expression failed with the message:
[essql] > Can not cast 'string' to any of 'filter'

[wylie (Wylie Conlon)]

That error is because you have added the extra | var name="requestMethodParam" in front of essql. You need to remove that from line 3.

[tangkalo]

Hi, Wylie. Do yo mean removing the whole line on line 3 or just the pipe |?

I am afraid I don't really understand how var_set and var works according to the documentation below.

Canvas function reference | Kibana Guide [7.12] | Elastic

I also found examples of them here - How to create a new line on a kibana canvas table - #6 by wylie .

[wylie (Wylie Conlon)]

You need to remove the whole piped | var ... function on line 3, because it is returning a string type that essql can't take.

[tangkalo]

@tangkalo

var name="requestMethodParam"

Thanks. It makes sense.

Unfortunately it still returns a empty resultset - I verified by using | render as "debug".

[wylie (Wylie Conlon)]

At this point you will have to debug the SQL query using your own data. I recommend either looking at the Network tab to capture the actual SQL query that you're running, or you could remove the essql command and just run the string "SELECT ..." to get the query. That could help you debug.

[tangkalo]

Thanks @wylie @Felix_Roessel . I finally got what Felix was saying. Here's the final version that works and be able to use the ulr param value.

filters
| var_set name="requestMethodParam" value={urlparam param="requestMethod" default="xxx"}
| essql {string   "SELECT \"@timestamp\"
as dateTime, (perfElapseTime)/1000 as \"Time\"
FROM \"log*\"
WHERE filename = 'performance.log'
AND branch = 'chq' AND perfMethodName = '" {var "requestMethodParam"} "' ORDER BY dateTime asc"
}