CODEWITHSUNDEEP
azureazure-log-analyticskqlazure-sentinel
Grace A
Grace A

Reputation: 175

Execute Kusto query present in the Table result

I have a table that has many other columns such as username, hostname, etc. One of the columns also stores a certain Query.

UserQueryTable

Username Hostname CustomQuery
Sam xyz some_query_1
David abc some_query_2
Rock mno some_query_3
Well stu some_query_4

When I run a kql such as :

UserQueryTable | where Username == "Sam"

I get:

Username Hostname CustomQuery
Sam xyz some_query_1

Note the "some_query_1" value under CustomQuery? That is an actual KQL query that is also part of the table result. I want to find a way where I can retrieve the "some_query_1" and EXECUTE it right after my KQL "UserQueryTable | where Username == "Sam""

That CustomQuery query will give me additional info about my alert and I need to get that Query string from the table and execute it. The CustomQuery in the table looks something like this

let alertedEvent = datatable(compressedRec: string)
[' -----redacted----7ziphQG4Di05dfsdfdsgdgS6uThq4H5fclBccCH6wW8M//sdfgty==']
| extend raw = todynamic(zlib_decompress_from_base64_string(compressedRec)) | evaluate bag_unpack(raw) | project-away compressedRec;
alertedEvent

So basically the 1st Query returns a result where one of the returned column itself contains Queries and I want to be able to run the returned Queries. The Query_ == CustomQuery the Query_ here is the CustomQuery

I tried using the User-defined functions but have not been able to come up with something that works. Please help!

Upvotes: 0

Views: 1606

Answers (3)

Abdalla Elzedy
Abdalla Elzedy

Reputation: 21

There is a better solution than running the query, you can extract the compressed text with a regex and decompress in the same line

Table
| extend Compressed = extract(@"\['([^;]+)']",1,<CompressedTextQuery>)
| extend raw = todynamic(zlib_decompress_from_base64_string(Compressed))

Upvotes: 0

Antonio Augusto Viana
Antonio Augusto Viana

Reputation: 21

AFAIK, for security reasons, you can't do that.

To accomplish what you want you would need to write a client app that get the results of the first query and the runs the second ones.

For reference you can't even reference table names "dynamically" on KQL.

Upvotes: 1

Ravit D
Ravit D

Reputation: 907

If I understand your question correctly, you have a query that returns a list of queries and would like to get as a result a list of queries out of this set that was actually run.

In that case, you can:

  1. Use .show queries command which returns the list of queries that was executed on your cluster(read more here). Notice .show queries would return the list of queries you ran, or - if you have database admin permissions - the list of queries anyone ran on the database.
  2. Enable diagnostic settings on your cluster, and send Query logs (read more here). This would send all queries that were executed on your cluster to a log analytics workspace of your choosing.

You can then use either of these options and join with your table to figure out which queries were actually executed. For instance, using the first option:

.show queries | join datatable (Query_: string)
        [
            "Table | where somecol contains 1",
            "Table | where somecol contains 2"
        ] on $left.Text == $right.Query_

Upvotes: 0

Related Questions