CODEWITHSUNDEEP
azureazure-data-explorerkql
Razi
Razi

Reputation: 1265

How to use result of first KQL query in the second query to filter results?

I have a first KQL query that returns a list of domain names, and then I want to use these to filter another KQL query. I just can't figure out the syntax to do it. Is there a way to use the contains() operator with a for loop/iteration in KQL?

KQL - Query 1

    let hostnames = () {
    AllDomains 
    | where hostname !contains "default.com" and hostname != ""
    | distinct hostname
   }

KQL - Query 2

let start_date = ago(10m);
let end_date = now();
LogEvents 
| where env_time between (start_date .. end_date)
| where headers  contains "X-Forwarded-For"
| where queryString contains (hostnames()) //This is what is needed to filter on all the domains from first query.
| project queryString

Upvotes: 1

Views: 1307

Answers (2)

Yoni L.
Yoni L.

Reputation: 25895

this could work:

let hostnames =
    AllDomains 
    | where isnotempty(hostname) and hostname !has "default.com"
    | distinct hostname
;
let start_date = ago(10m);
let end_date = now();
LogEvents 
| where env_time between (start_date .. end_date)
| where headers contains "X-Forwarded-For"
| where queryString has_any (hostnames)
| project queryString

Upvotes: 1

LITzman
LITzman

Reputation: 740

It would be better if you'll provide a sample of how your data looks and what you are trying to accomplish, but I think that instead of contains you'd want to use has_any

Upvotes: 0

Related Questions