KQL - Query Usage Time

Question

i have a Question about a Query in KQL. I would like to use a Time at the KQL Query who only shows me the Results between 08:00 and 17:00 Time.

How can i build these at the KQL Query? Im only find the DateTime Variable but i need only the Time?

Thanks a lot.

Regards, Phil

score 0 · Answer 1 · answered Oct 26 '22 at 07:10

0

Timestamp%1d will give us only the time part of the day (timespan).

// Data sample generation. Not part of the solution.
let t = materialize (range i from 1 to 20 step 1 | extend Timestamp = ago(7d * rand()));
// Solution starts here.
t
| where Timestamp%1d between (8h .. 17h)
| order by Timestamp%1d asc // Just for display

i	Timestamp
13	2022-10-19T08:26:45.2144968Z
1	2022-10-23T12:00:21.8528635Z
16	2022-10-19T12:50:27.4405648Z
19	2022-10-19T13:00:48.9000836Z
2	2022-10-24T13:19:30.956558Z
8	2022-10-25T13:51:25.726857Z
10	2022-10-22T14:12:09.8304847Z
7	2022-10-25T14:51:14.3011525Z
14	2022-10-20T15:21:04.5173436Z
11	2022-10-20T16:04:06.412613Z
12	2022-10-19T16:48:54.0581289Z

Fiddle

answered Oct 26 '22 at 07:10

David דודו Markovitz

42,900
6
64
88

Hey, thanks for your Answer but it doesnt works. When i use this availabilityResults | where timestamp%1d between (08h..17h) It shows me not only the Results between 08:00 and 17:00. It shows me many more days and other time ranges. – Phil Oct 26 '22 at 07:49
Sorry i must say, that i only would like to see the results from this day. – Phil Oct 26 '22 at 08:01
That's a completely different story. Please accept the answer for this question and post a new question for the modified version. – David דודו Markovitz Oct 26 '22 at 08:11

score 0 · Accepted Answer · answered Oct 26 '22 at 09:38

The below is the example to show logs between specific time:

let start=datetime("10/26/2022 1:04:27.627 AM");
let end=datetime("10/26/2022 1:22:53.745 AM");
traces
| where timestamp > start and timestamp < end

enter image description here

If you only want timestamp then:

let start=datetime("10/26/2022 1:04:27.627 AM");
let end=datetime("10/26/2022 1:22:53.745 AM");
traces
| where  timestamp > start and timestamp < end 
| project timestamp

enter image description here

You can give your date and time in end and start in query.

KQL - Query Usage Time

2 Answers2