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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 15822000 · Answer 1 · 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

Maciej Los · Answer 2 · 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[^]