Convert SQL query to KQL

Question

1.00/5 (1 vote)

See more:

Convert KQL query to SQL

Problem statement- Check event time stamp is exist in another table.
eg In sql we can user "between" and "and"

SELECT   Evnt_GUID,Transactiontimestamp,* from TestTrasncstion (NOLOCK),  

(

SELECT  star.GlobalID Evnt_GUID, star.TimeGenerated StartTime, sto.TimeGenerated StopTime from

(SELECT GlobalID, SpecialEventName_s, TimeGenerated from JobsEvents (NOLOCK) where SpecialEventName_s='Job Stopped' and SpecialEventDescription_s like '%GUID%' ) sto,

(SELECT GlobalID, SpecialEventName_s, TimeGenerated from JobsEvents (NOLOCK) where SpecialEventName_s='Job Started' and SpecialEventDescription_s like '%GUID%' ) star

WHERE sto.GlobalID=star.GlobalID

) tab

WHERE Transactiontimestamp BETWEEN tab.StartTime and tab.StopTime ORDER BY 2

What I have tried:

Tried summarize operator in KQL but no luck.

Posted 29-Mar-21 22:47pm

Mangesh9692

Updated 6-Nov-22 2:31am

v2

Add a Solution

Comments

Maciej Los 30-Mar-21 5:00am

Your sql query is very poor! Do you know what a join statement is for?

Mangesh9692 30-Mar-21 5:04am

Yes I know but join is not usefull, and my concern is related to KQL(Kusto query language)

Maciej Los 30-Mar-21 5:47am

No! Your first issue is to create proper sql query, then - the second - to write corresponding kql query.

Mangesh9692 30-Mar-21 6:28am

OK let me give me my problem statement so u might be understand what I am trying to write

I have one table in Azure data explorer log analytics work space where I am logging my all transaction with their timestamp with type=transaction with different guid for each log, in the same table I am logging my background job start and stop against same guid(same guid for single cycle ie start and stop event) with their respective time stamp with type=event. Now I want find a record from this table with type=transaction those are falling in Job start and stop event timestamp .Thanks

Maciej Los 30-Mar-21 6:48am

Try this:

SELECT tt.Evnt_GUID, tt.Transactiontimestamp, je.*
FROM TestTrasncstion tt(NOLOCK)
	LEFT JOIN 
	(
		SELECT  Evnt_GUID
		, StartTime = MIN(CASE WHEN SpecialEventName_s='Job Started' THEN TimeGenerated ELSE NULL END)
		, StopTime = MAX(CASE WHEN SpecialEventName_s='Job Stopped' THEN TimeGenerated ELSE NULL END)
		FROM JobsEvents
		GROUP BY Evnt_GUID
		WHERE SpecialEventDescription_s like '%GUID%' 
	) je (NOLOCK) ON tt.Evnt_GUID = je.Evnt_GUID AND tt.Transactiontimestamp BETWEEN je.StartTime and je.StopTime
ORDER BY 2

Mangesh9692 30-Mar-21 23:38pm

Yes this query is working fine as like my previous query but I have written KQL query for same but its not working

Maciej Los 31-Mar-21 2:08am

Now, you can easily convert sql query to kql query.

Mangesh9692 31-Mar-21 2:35am

Yes i have written it but it now working as SQL,Alias is not working. Not giving syntax error but output is not coming

let test=(TestTransctions
|join kind = inner
(TestTransctions)
on $left.GlobalID == $right.GlobalID
| where EventTimeStamp_t > ago(totimespan((TimeGenerated))) and type='event'
| summarize (ets1, ts1) = arg_min (EventTimeStamp_t,SpecialEventName_s), (ets2,ts2) = arg_max(EventTimeStamp_t1, SpecialEventName_s1) by GlobalID
| project ets1, ts1, ets2, ts2, ets3=ets2 - ets1,GlobalID
|project-rename JobStarted=ets1,JobStopped=ets2
|project-keep JobStarted,JobStopped,GlobalID);
let test2=(TestTransctions| where IsTransactionAdhoc_b=='false');
test2
|join kind=leftouter
(test)
on $left.GlobalID == $right.GlobalID
|where EventTimeStamp_t between (todatetime(JobStopped)..todatetime(JobStarted))

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2021-03-30T00:55:00

First of all, please read this: Visual Representation of SQL Joins[^]

I'd try this sql query:

SQL

SELECT tt.Evnt_GUID, tt.Transactiontimestamp, je.*
FROM TestTrasncstion tt(NOLOCK)
	LEFT JOIN 
	(
		SELECT  Evnt_GUID
		, StartTime = MIN(CASE WHEN SpecialEventName_s='Job Started' THEN TimeGenerated ELSE NULL END)
		, StopTime = MAX(CASE WHEN SpecialEventName_s='Job Stopped' THEN TimeGenerated ELSE NULL END)
		FROM JobsEvents
		GROUP BY Evnt_GUID
		WHERE SpecialEventDescription_s like '%GUID%' 
	) je (NOLOCK) ON tt.Evnt_GUID = je.Evnt_GUID AND tt.Transactiontimestamp BETWEEN je.StartTime and je.StopTime
ORDER BY 2

Then you should be able to convert it into kql.

For further details, please see:
Joins (SQL Server) - SQL Server | Microsoft Docs[^]
CASE (Transact-SQL) - SQL Server | Microsoft Docs[^]
MIN (Transact-SQL) - SQL Server | Microsoft Docs[^]
MAX (Transact-SQL) - SQL Server | Microsoft Docs[^]
GROUP BY (Transact-SQL) - SQL Server | Microsoft Docs[^]
join operator - Azure Data Explorer | Microsoft Docs[^]

Member 15822000 · Answer 2 · 2022-11-06T02:31:00

Solution 2

You can use Mostly Harmless Sql Translator: Json Kusto Pandas[^] to translate your sql into kusto/KQL, there is a cookbook for simple sql statements. Good luck! ^_^

Posted 6-Nov-22 2:31am

Member 15822000

Updated 6-Nov-22 2:36am

v4

Convert SQL query to KQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Convert SQL query to KQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us