Click here to Skip to main content
15,867,964 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
Updated 6-Nov-22 2:31am
v2
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

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[^]
 
Share this answer
 
Comments
Mangesh9692 14-Apr-21 23:50pm    
I have tried this but no luck. Could you please help me on this




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))
Maciej Los 15-Apr-21 3:32am    
I have never used KQL. So, i can't help you. Please, take a look here: SQL to Kusto query translation - Azure Data Explorer | Microsoft Docs[^]
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! ^_^
 
Share this answer
 
v4

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900