CODEWITHSUNDEEP
azure-data-explorerkql
Tony Campney
Tony Campney

Reputation: 205

Kusto SubQuery Referencing "outer" query

I'm trying to write a Kusto (KQL) query which in SQL I'd write with a subquery that references the "outer" query like below. However, I cannot find/understand how to accomplish the equivalent in KQL. Is it possible to have a subquery reference the "outer" query in KQL? If not, some other method to accomplish the below?

Basic idea is joining a table onto itself to get the next time value. IE return the min time value where time is greater than current record time and matches the key (DeviceName in this case).

DECLARE @DeviceIPs TABLE (DeviceName NVARCHAR(MAX), DeviceIP NVARCHAR(MAX), TimeGenerated DATETIME)

INSERT INTO @DeviceIPs SELECT 'PC1', '192.168.100.1', '2021-01-01'
INSERT INTO @DeviceIPs SELECT 'PC1', '192.168.100.2', '2021-01-02'
INSERT INTO @DeviceIPs SELECT 'PC1', '192.168.100.3', '2021-01-03'
INSERT INTO @DeviceIPs SELECT 'PC2', '192.168.100.3', '2021-01-01'
INSERT INTO @DeviceIPs SELECT 'PC2', '192.168.100.1', '2021-01-02'
INSERT INTO @DeviceIPs SELECT 'PC2', '192.168.100.2', '2021-01-03'

SELECT
    i.DeviceName,
    i.DeviceIP,
    i.TimeGenerated AS BeginDateTime,
    ISNULL((SELECT MIN(i2.TimeGenerated) FROM @DeviceIPs i2 WHERE i.DeviceName = i2.DeviceName AND i.TimeGenerated < i2.TimeGenerated), '2200-01-01') AS EndDateTime
FROM
    @DeviceIPs i

In the above data structure each row represents the time when a device was granted an IP address (BeginDateTime). The result I'm looking for is then to also get the "EndDateTime" which would be the next time the device was granted an IP address. If there is no "next record", I'm just setting some random future dated date as the end date, but that part isn't really pertinent to this question. The expected results would be:

DeviceName DeviceIP BeginDateTime EndDateTime
PC1 192.168.0.1 2021-01-01 2021-01-02
PC1 192.168.0.2 2021-01-02 2021-01-03
PC1 192.168.0.3 2021-01-03 2200-01-01
PC2 192.168.0.3 2021-01-01 2021-01-02
PC2 192.168.0.1 2021-01-02 2021-01-03
PC2 192.168.0.2 2021-01-03 2200-01-01

Upvotes: 0

Views: 808

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

Assuming i understood your intention correctly, the following should work:

.create table DeviceIPs (DeviceName:string, DeviceIP: string, TimeGenerated:datetime)

.ingest inline into table DeviceIPs <|
PC1,192.168.100.1,2021-01-01
PC1,192.168.100.2,2021-01-02
PC1,192.168.100.3,2021-01-03
PC2,192.168.100.3,2021-01-01
PC2,192.168.100.1,2021-01-02
PC2,192.168.100.2,2021-01-03

DeviceIPs
| order by DeviceName asc, TimeGenerated asc
| project DeviceName, DeviceIP, BeginDateTime = TimeGenerated, EndDateTime = case(next(DeviceName) == DeviceName, next(TimeGenerated), datetime(2200-01-01))
DeviceName DeviceIP BeginDateTime EndDateTime
PC1 192.168.100.1 2021-01-01 00:00:00.0000000 2021-01-02 00:00:00.0000000
PC1 192.168.100.2 2021-01-02 00:00:00.0000000 2021-01-03 00:00:00.0000000
PC1 192.168.100.3 2021-01-03 00:00:00.0000000 2200-01-01 00:00:00.0000000
PC2 192.168.100.3 2021-01-01 00:00:00.0000000 2021-01-02 00:00:00.0000000
PC2 192.168.100.1 2021-01-02 00:00:00.0000000 2021-01-03 00:00:00.0000000
PC2 192.168.100.2 2021-01-03 00:00:00.0000000 2200-01-01 00:00:00.0000000

Upvotes: 2

Related Questions