在Grafana中用Kusto实现多图时间序列遇问题：排序警告+无分组显示

尝试在Kusto中实现多机器时间序列分析，并在Grafana中展示分组图表时遇到两个问题：

• Grafana弹出警告：

  Detected long formatted time series but failed to convert from long frame: long series must be sorted ascending by time to be converted.

• 查询已返回数据，但Grafana时间序列图表无法按machineA、machineB、machineC等机器分组展示。

使用的KQL查询代码如下：

let test = datatable (Timestamp: datetime, Id: string, Value: dynamic)
[
  datetime(2022-11-09 11:39:25), "machineA", "True",
  datetime(2022-11-09 11:39:30), "machineA", "True",
  datetime(2022-11-09 11:39:35), "machineA", "False",
  datetime(2022-11-09 11:39:36), "machineA", "False",
  datetime(2022-11-09 11:40:03), "machineA", "True",
  datetime(2022-11-09 11:40:03), "machineA", "True",
  datetime(2022-11-09 11:40:04), "machineA", "True",
  datetime(2022-11-09 11:40:05), "machineA", "True",
  datetime(2022-11-09 11:40:25), "machineA", "False",
  datetime(2022-11-09 11:40:25), "machineA", "False",
  datetime(2022-11-09 11:40:26), "machineA", "False",
  datetime(2022-11-09 11:40:27), "machineA", "False",
  datetime(2022-11-09 11:40:37), "machineA", "True",
  datetime(2022-11-09 11:40:47), "machineA", "False",
  datetime(2022-11-09 11:40:57), "machineA", "True",
  datetime(2022-11-09 11:40:59), "machineA", "True",
  datetime(2022-11-09 11:40:25), "machineB", "True",
  datetime(2022-11-09 11:40:30), "machineB", "True",
  datetime(2022-11-09 11:40:35), "machineB", "False",
  datetime(2022-11-09 11:40:36), "machineB", "False",
  datetime(2022-11-09 11:41:03), "machineB", "True",
  datetime(2022-11-09 11:41:03), "machineB", "True",
  datetime(2022-11-09 11:41:04), "machineB", "True",
  datetime(2022-11-09 11:41:05), "machineB", "True",
  datetime(2022-11-09 11:41:25), "machineB", "False",
  datetime(2022-11-09 11:41:25), "machineB", "False",
  datetime(2022-11-09 11:41:26), "machineB", "False",
  datetime(2022-11-09 11:41:27), "machineB", "False",
  datetime(2022-11-09 11:41:37), "machineB", "True",
  datetime(2022-11-09 11:41:47), "machineB", "False",
  datetime(2022-11-09 11:41:57), "machineB", "True",
  datetime(2022-11-09 11:41:59), "machineB", "True",
  datetime(2022-11-09 11:42:25), "machineC", "True",
  datetime(2022-11-09 11:42:30), "machineC", "True",
  datetime(2022-11-09 11:42:35), "machineC", "False",
  datetime(2022-11-09 11:42:36), "machineC", "False",
  datetime(2022-11-09 11:43:03), "machineC", "True",
  datetime(2022-11-09 11:43:03), "machineC", "True",
  datetime(2022-11-09 11:43:04), "machineC", "True",
  datetime(2022-11-09 11:43:05), "machineC", "True",
  datetime(2022-11-09 11:43:25), "machineC", "False",
  datetime(2022-11-09 11:43:25), "machineC", "False",
  datetime(2022-11-09 11:43:26), "machineC", "False",
  datetime(2022-11-09 11:43:27), "machineC", "False",
  datetime(2022-11-09 11:43:37), "machineC", "True",
  datetime(2022-11-09 11:43:47), "machineC", "False",
  datetime(2022-11-09 11:43:57), "machineC", "False",
  datetime(2022-11-09 11:43:59), "machineC", "False",
];
let tiemposCicloBruto = test
    | where Timestamp > ago(100d)
    | partition hint.strategy=native by Id
    (
        order by Timestamp asc // ordenamos ascendentemente
        | extend prev_Timestamp = prev(Timestamp) // extendemos la fecha previa
        | extend prev_Value = prev(Value) // extendemos el valor previo
        | extend duration = 
        iif( // Condicion ternaria
            prev_Value == "True" and Value == "False" // Si anteriormente estaba en funcion y el valor actual es parado, cuenta como tiempo de ciclo
            or prev_Value == "True" and Value == "True", // Si el valor anterior era funcionando y el actual tambien, la maquina sigue funcionando
            Timestamp - prev_Timestamp, // Para ese caso restamos la diferencia de tiempo
            time(null) // Para el caso contrario, devolvemos nulo
        )
        | project Id, Timestamp, duration, Value, prev_Value
    );
tiemposCicloBruto // La consulta para 1d completo tarda entre 1-1.5s
| where isnotnull(duration)
| partition hint.strategy=native by Id ( // partimos por Id
    order by Timestamp asc // debe ser siempre ascendente si no pierde la logica
    | scan declare (y:timespan=time(null), x:timespan=time(null)) with ( // declaramos el scan
        step s1: true => // declaramos el paso
        x=iif(s1.Value == "True" and Value == "True", iif(isnull(s1.x), duration, s1.x)+s1.duration, time(null)), // si tenemos varios True-True consecutivos, sumamos la duracion anterior a la actual, asignandola a X
        y=iif(s1.Value=="False" and Value=="False", duration, // si tenemos el caso de que es False-False, partimos de la duracion
                iif(s1.Value=="True" and Value=="False", s1.x+duration, time(null))); // si tenemos que la maquina estaba funcionando y para, sumamos las duraciones consecutivas de mientras que estaba funcionando
    )
    | extend next_Id=next(Id)
    | extend tiempoCiclo=iif(isempty(next_Id), duration, iif(isnull(y) and prev_Value == "True" and Value=="False", duration+prev(duration), iif(isnotnull(y), y, time(null)))) / 1s // y para aquellos cambios de maquina o para aquellos donde no hubiera valor por casuistica, asignamos la duracion o la duracion+duracion previa
    | where isnotnull(tiempoCiclo) // filtramos
    | project-away prev_Value, x, y, duration, Value, next_Id
)

1. 排序警告原因：查询中仅在partition内部对单台机器的时间做了升序排序，但最终返回的全量结果集Timestamp并非全局有序（比如machineA的所有条目都在machineB之前，machineB又在machineC之前），而Grafana要求long格式的时间序列必须全局按时间升序排列。
2. 分组不显示原因：Grafana时间序列图表需要明确将Id字段指定为分组维度，否则无法自动按机器拆分序列；同时需确保结果字段类型符合要求（Timestamp为datetime，tiempoCiclo为数值，Id为字符串）。

1. 修复排序警告 #

在查询的最后添加全局时间升序排序，确保全量结果集按时间有序：

| order by Timestamp asc

2. 配置Grafana面板实现分组显示 #

• 打开Grafana时间序列面板的Fields设置
• 将Timestamp字段的类型设置为Time
• 将Id字段的类型设置为Series（作为分组维度）
• 将tiempoCiclo字段的类型设置为Number（作为数值）

修改后的完整KQL查询 #

let test = datatable (Timestamp: datetime, Id: string, Value: dynamic)
[
  datetime(2022-11-09 11:39:25), "machineA", "True",
  datetime(2022-11-09 11:39:30), "machineA", "True",
  datetime(2022-11-09 11:39:35), "machineA", "False",
  datetime(2022-11-09 11:39:36), "machineA", "False",
  datetime(2022-11-09 11:40:03), "machineA", "True",
  datetime(2022-11-09 11:40:03), "machineA", "True",
  datetime(2022-11-09 11:40:04), "machineA", "True",
  datetime(2022-11-09 11:40:05), "machineA", "True",
  datetime(2022-11-09 11:40:25), "machineA", "False",
  datetime(2022-11-09 11:40:25), "machineA", "False",
  datetime(2022-11-09 11:40:26), "machineA", "False",
  datetime(2022-11-09 11:40:27), "machineA", "False",
  datetime(2022-11-09 11:40:37), "machineA", "True",
  datetime(2022-11-09 11:40:47), "machineA", "False",
  datetime(2022-11-09 11:40:57), "machineA", "True",
  datetime(2022-11-09 11:40:59), "machineA", "True",
  datetime(2022-11-09 11:40:25), "machineB", "True",
  datetime(2022-11-09 11:40:30), "machineB", "True",
  datetime(2022-11-09 11:40:35), "machineB", "False",
  datetime(2022-11-09 11:40:36), "machineB", "False",
  datetime(2022-11-09 11:41:03), "machineB", "True",
  datetime(2022-11-09 11:41:03), "machineB", "True",
  datetime(2022-11-09 11:41:04), "machineB", "True",
  datetime(2022-11-09 11:41:05), "machineB", "True",
  datetime(2022-11-09 11:41:25), "machineB", "False",
  datetime(2022-11-09 11:41:25), "machineB", "False",
  datetime(2022-11-09 11:41:26), "machineB", "False",
  datetime(2022-11-09 11:41:27), "machineB", "False",
  datetime(2022-11-09 11:41:37), "machineB", "True",
  datetime(2022-11-09 11:41:47), "machineB", "False",
  datetime(2022-11-09 11:41:57), "machineB", "True",
  datetime(2022-11-09 11:41:59), "machineB", "True",
  datetime(2022-11-09 11:42:25), "machineC", "True",
  datetime(2022-11-09 11:42:30), "machineC", "True",
  datetime(2022-11-09 11:42:35), "machineC", "False",
  datetime(2022-11-09 11:42:36), "machineC", "False",
  datetime(2022-11-09 11:43:03), "machineC", "True",
  datetime(2022-11-09 11:43:03), "machineC", "True",
  datetime(2022-11-09 11:43:04), "machineC", "True",
  datetime(2022-11-09 11:43:05), "machineC", "True",
  datetime(2022-11-09 11:43:25), "machineC", "False",
  datetime(2022-11-09 11:43:25), "machineC", "False",
  datetime(2022-11-09 11:43:26), "machineC", "False",
  datetime(2022-11-09 11:43:27), "machineC", "False",
  datetime(2022-11-09 11:43:37), "machineC", "True",
  datetime(2022-11-09 11:43:47), "machineC", "False",
  datetime(2022-11-09 11:43:57), "machineC", "False",
  datetime(2022-11-09 11:43:59), "machineC", "False",
];
let tiemposCicloBruto = test
    | where Timestamp > ago(100d)
    | partition hint.strategy=native by Id