Using different time periods in one Azure log query

Question

So I have an Azure log query (KQL) that takes in a date parameter, like check log entries for the last X amount of days. In this query I look up values from two different logs, and I would like to have the two lookups use different dates for the respective logs. To get an idea of what I'm looking for, see the query below which is almost what I have now, with a bit of pseudo code where I can't quite figure out how to structure it.

let usernames = LogNumberOne
| where TimeGenerated > {timeperiod:start} and TimeGenerated < {timeperiod:end}
| bla bla bla lots of stuff
let computernames = LogNumberTwo
| where TimeGenerated > {timeperiod:start} - 2d
| where bla bla bla lots of stuff
usernames
| join kind=innerunique (computernames) on session_id
| some logic to display table

So from LogNumberOne I want the values within the specified time period, but from LogNumberTwo I want the values from the specified time period plus another 2 days before that. Is this possible or do I need another parameter? I have tried with the query above, so {timeperiod:start} - 2d, but that doesn't seem to work, it just uses the timeperiod:start value without subtracting 2 days.

Alexander Sloutsky Alexander Sloutsky · Accepted Answer · 2020-05-14T17:17:56

See next variant for using join with filter later.

let usernames  = datatable(col1:string, session_id:string, Timestamp:datetime )
[
'user1', '1', datetime(2020-05-14 16:00:00),
'user2', '2', datetime(2020-05-14 16:05:30),
];
let computernames  =
datatable(session_id:string, ComputerName:string, Timestamp:datetime )
[
'1', 'Computer1', datetime(2020-05-14 16:00:30),
'2', 'Computer2', datetime(2020-05-14 16:06:20),
];
usernames
| join kind=inner (
    computernames
    | project-rename ComputerTime = Timestamp
) on session_id
| where Timestamp between(ComputerTime .. (-2d))

In case large sets of join are involved - use technique described in the following article: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/join-timewindow

let window = 2d;
let usernames  = datatable(col1:string, session_id:string, Timestamp:datetime )
[
'user1', '1', datetime(2020-05-13 16:00:00),
'user2', '2', datetime(2020-05-12 16:05:30),
];
let computernames  =
datatable(session_id:string, ComputerName:string, Timestamp:datetime )
[
'1', 'Computer1', datetime(2020-05-14 16:00:30),
'2', 'Computer2', datetime(2020-05-14 16:06:20),
];
usernames
| extend _timeKey = range(bin(Timestamp, 1d), bin(Timestamp, 1d)+window, 1d)
| mv-expand _timeKey to typeof(datetime)
| join kind=inner (
    computernames
    | project-rename ComputerTime = Timestamp
    | extend _timeKey = bin(ComputerTime, 1d)
) on session_id, _timeKey
| where Timestamp between(ComputerTime .. (-window))

Using different time periods in one Azure log query

1 Answers