CODEWITHSUNDEEP
azure-data-explorerkql
irom
irom

Reputation: 3596

How to write Kusto query to get results in one table?

I have 2 KQL queries and I want to combine them in order to display two rows as one result. Not just result of first query, then result of second query: 

R_CL
| where isnotempty(SrcIP_s) 
| project Message 
| take 1;

R_CL
| where isempty(SrcIP_s) 
| project Message 
| take 1

See sample R_L below.I would like to see 2 rows as result, one with SrcIP_s not empty, and the second with SrcIP_s empty (in this case it will be always same one)

let R_CL = datatable ( SrcIP_s:string, Message:string)
["1.1.1.1" ,"one",
"" ,"two",
"2.2.2.2","three",
"3.3.3.3","four"];
R_CL
| project SrcIP_s, Message

Upvotes: 10

Views: 22849

Answers (2)

Jules
Jules

Reputation: 244

A simple solution for this would be to use the union operator like this:

let query1 = R_CL
    | where isnotempty(SrcIP_s) 
    | project Message 
    | take 1;

let query2 = R_CL
    | where isempty(SrcIP_s) 
    | project Message 
    | take 1;

query1
| union query2;

Upvotes: 11

Kurt P
Kurt P

Reputation: 133

I know this is an old request - but here's a sample query using views and a union for your single query:

Your two separate queries...

R_CL
| where isnotempty(SrcIP_s) 
| project Message 
| take 1;

R_CL
| where isempty(SrcIP_s) 
| project Message 
| take 1

would become:

let Query1 = view () {
R_CL
| where isnotempty(SrcIP_s) 
| project Message 
| take 1;
};
let Query2 = view () {
R_CL
| where isempty(SrcIP_s) 
| project Message 
| take 1
};    
union withsource="TempTableName" Query1, Query2

Upvotes: 9

Related Questions