How to pass urlparam value to KQL

Elastic Stack Kibana
1

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"
}
2

  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"
}
1 Like
3

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

4

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"

}

5

Actually I got this error.

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

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

7

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.

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

8

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

9

Thanks. It makes sense.

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

10

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.

11

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"
}
12

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.