英文:
Auto update Kusto to pull todays date but specific time
问题
我对Kusto查询非常陌生，我有一个正在给我提供正确数据以导出到Excel进行管理的查询。我的唯一问题是，我目前只关心昨天和今天的数据，要分别放在两个不同的工作表中。我可以手动更改日期时间信息，但我想要的是只需刷新数据，它就会提取最新的数据。
这听起来很简单，但我无法弄清楚如何指定我想要的确切时间。我需要的时间范围是从第1天的凌晨2点到第2天的凌晨1点59分。
谢谢
['Telemetry.WorkStation']
| where NexusUid == "08463c7b-fe37-43b6-a0d2-237472b9774d"
| where TelemetryLocalTimeStamp >= make_datetime(2023,2,15,2,0,0) and TelemetryLocalTimeStamp < make_datetime(2023,2,16,1,59,0)
| where NumberOfBinPresentations > 0
英文:
I am very new to Kusto queries and I have one that is giving me the proper data that I export to Excel to manage. My only problem is that I only care (right now) about yesterday and today in two separate Sheets. I can manually change the datetime with the information but I would like to be able to just refresh the data and it pull the newest number.
It sounds pretty simple but I cannot figure out how to specify the exact time I want. Has to be from 2 am day 1 until 1:59 day 2
Thanks
['Telemetry.WorkStation']
| where NexusUid == "08463c7b-fe37-43b6-a0d2-237472b9774d"
| where TelemetryLocalTimeStamp >= make_datetime(2023,2,15,2,0,0) and TelemetryLocalTimeStamp < make_datetime(2023,2,16,01,59,0)
| where NumberOfBinPresentations >0
答案1
得分: 1
ago()、now()、startofday()和一些日期时间算术操作。
// 示例数据生成。不是解决方案的一部分。
let ['Telemetry.WorkStation'] = materialize(range i from 1 to 1000000 step 1 | extend NexusUid = "08463c7b-fe37-43b6-a0d2-237472b9774d", TelemetryLocalTimeStamp = ago(2d * rand()));
// 解决方案从这里开始。
['Telemetry.WorkStation']
| where NexusUid == "08463c7b-fe37-43b6-a0d2-237472b9774d"
| where TelemetryLocalTimeStamp >= startofday(ago(1d)) + 2h
and TelemetryLocalTimeStamp < startofday(now()) + 2h
| summarize count(), min(TelemetryLocalTimeStamp), max(TelemetryLocalTimeStamp)
| count_ | min_TelemetryLocalTimeStamp | max_TelemetryLocalTimeStamp |
|---|---|---|
| 500539 | 2023-02-15T02:00:00.0162031Z | 2023-02-16T01:59:59.8883692Z |
英文:
ago(), now(), startofday() and some datetime arithmetic.
// Sample data generation. Not part of the solution.
let ['Telemetry.WorkStation'] = materialize(range i from 1 to 1000000 step 1 | extend NexusUid = "08463c7b-fe37-43b6-a0d2-237472b9774d", TelemetryLocalTimeStamp = ago(2d * rand()));
// Solution starts here.
['Telemetry.WorkStation']
| where NexusUid == "08463c7b-fe37-43b6-a0d2-237472b9774d"
| where TelemetryLocalTimeStamp >= startofday(ago(1d)) + 2h
and TelemetryLocalTimeStamp < startofday(now()) + 2h
| summarize count(), min(TelemetryLocalTimeStamp), max(TelemetryLocalTimeStamp)
| count_ | min_TelemetryLocalTimeStamp | max_TelemetryLocalTimeStamp |
|---|---|---|
| 500539 | 2023-02-15T02:00:00.0162031Z | 2023-02-16T01:59:59.8883692Z |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论