Forum Discussion
Is there a bug in the make-series function or am I usingit wrong ?
I have a KQL query which looks like this:
VesselTelemetry
| where Timestamp >= todatetime('2023-03-29T08:38:03.6817196Z') and Timestamp < todatetime('2023-03-30T08:38:03.6817202Z')
| where VesselId == '<some-id>' and TelemetryTag == 'stw' and TelemetryQuality >= 192
| order by Timestamp
| summarize arg_max(Timestamp, telemetryValue = TelemetryValue_Double), eventCountValue = count() by bin(Timestamp, totimespan('1.00:00:00'))
This query outputs this:
| Timestamp | Timestamp1 | telemetryValue | eventCountValue |
| 2023-03-29T00:00:00Z | 2023-03-29T23:59:29Z | 12 | 7841 |
| 2023-03-30T00:00:00Z | 2023-03-30T08:37:55Z | 11.6 | 5815 |
When I add a 'make-series' clause to the above query, so that it looks like this:
VesselTelemetry
| where Timestamp >= todatetime('2023-03-29T08:38:03.6817196Z') and Timestamp < todatetime('2023-03-30T08:38:03.6817202Z')
| where VesselId == '<some-id>' and TelemetryTag == 'stw' and TelemetryQuality >= 192
| order by Timestamp
| summarize arg_max(Timestamp, telemetryValue = TelemetryValue_Double), eventCountValue = count() by bin(Timestamp, totimespan('1.00:00:00'))
| make-series telemetrySeries = max(telemetryValue) default = double(null), eventCountSeries = max(eventCountValue) on Timestamp from todatetime('2023-03-29T08:38:03.6817196Z') to todatetime('2023-03-30T08:38:03.6817202Z') step totimespan('1.00:00:00')
The output becomes
| telemetrySeries | eventCountSeries | Timestamp |
| [11.6,null] | [5815,0] | ["2023-03-29T08:38:03.6817196Z","2023-03-30T08:38:03.6817196Z"] |
which is obviously not as expected.
I would expect it to be [12, 11.6] for telemetrySeries and [7841, 5815] for eventCountSeries.
(I add the make-series because in a next step, I want to use a series_fill_forward statement to perform some interpolation).
Am I doing something wrong here, or can this be a bug in the make-series function?
Unfortunately, I need to use the `summarize` clause first, since `arg_max` is not supported inside the `make-series` function.
2 Replies
Ok, If I modify the timestamp boundaries in the make-series function, I get the expected results.
However, you need to make sure that the offsets are large enough. Modifying them by subtracting / adding one second is not enough.
If I change the fromDate to 2023-03-29T00:00 and the toDate to 2023-03-30T22:00:00 in the make-series function like this:VesselTelemetry | where Timestamp >= todatetime('2023-03-29T08:38:03.6817196Z') and Timestamp < todatetime('2023-03-30T08:38:03.6817202Z') | where VesselId == '9728693' and TelemetryTag == 'stw' and TelemetryQuality >= 192 | order by Timestamp | summarize arg_max(Timestamp, telemetryValue = TelemetryValue_Double), eventCountValue = count() by bin(Timestamp, totimespan('1.00:00:00')) | project telemetryValue, eventCountValue, Timestamp | make-series telemetrySeries = max(telemetryValue) default = double(null), eventCountSeries = max(eventCountValue) on Timestamp from todatetime('2023-03-29T00:00:00Z') to todatetime('2023-03-30T22:00:00') step totimespan('1.00:00:00')Then, I get these results:
telemetrySeries eventCountSeries Timestamp [12,11.6] [7841,5815] ["2023-03-29T00:00:00.0000000Z","2023-03-30T00:00:00.0000000Z"] - SuryaJ
Microsoft
Frederik-Gheysels While the start of time is inclusive, end of time is non inclusive.
You created make-series on Timestamp column which has min value of 2023-03-29T00:00:00 but in your KQL query, you used 2023-03-29T08:38:03.6817196Z. so 12 and 7841 will not be included in the output.
Your second query is correct since it included time less than the min timestamp in your table and a little more than the max timestamp.
