This message was deleted.

I have tried timeseries query but it doesn't allow to group by on dimensions I have tried GroupBy query but it's not finding a proper way to order the data in descending order of time with limit.

Can you share the query? Are you looking for the latest value for the KPIs by deviceID and ObjectID, or what is the aggregation? Do you always need all objects and devices? In order to get segment pruning you will need to filter it at least on __time. Additional pruning on other dimensions through secondary partitioning might also be possible but only if you filter on something else.

Group By query where order by doesn't work,

{
  "queryType": "groupBy",
  "dataSource": "snmp_interface",
  "granularity": "five_minute",
  "dimensions": [
    "device",
    "interface"
  ],
  "limitSpec": {
    "type"    : "default",
    "limit"   : 10000,
    "columns" : [{
    "dimension" : "_ordered",
    "direction" : "descending"
}]}
  "aggregations": [
    {
      "type": "doubleMin",
      "name": "interface.received.octets",
      "fieldName": "interface.received.octets"
    },
    {
      "type": "doubleSum",
      "name": "interface.out.packets",
      "fieldName": "interface.out.packets"
    },
    {
      "type": "stringLast",
      "name": "_ordered",
      "fieldName": "__time"
    }
  ],
  "intervals": [
    "2023-12-11/2023-12-13"
  ]
}

What result are you trying to achieve? I think those two queries express very different intents. Are you looking for the latest 1-minute or 5-minute aggs for each interface/device ? How often does each interface/device create events? Something like:

SELECT FLOOR(__time, 'PT5M') time_bucket, interface, device, SUM(octets) sum_octets, SUM(packets) sum_packets
FROM snmp_interface
WHERE TIME_IN_INTERVAL(__time, '2023-12-11/P1D')
GROUP BY 1,2,3
ORDER BY time_bucket DESC
LIMIT 1000

You can plug that into the Console and ask for an Explain to get the Native Query version of it.

I collect data every minute, Each device can have multiple interfaces. { timestamp=120000, data= [ d1 i11 <packets, octets....> d1 i12 <packets, octets....> d1 i13 <packets, octets....> d2 i11 <packets, octets....> d2 i22 <packets, octets....> d2 i33 <packets, octets....> ] I am trying to plot a historical view for each interfaces-device combo for a given granularity E.g.: For

all interfaces

in druid, show the historical trend view of

packets

with the granularity of

five_minutes

for

today

@John Kowtko Could you please look into this? @Sergio Ferragut Previously you were having discussion with Natasha regarding this in the following thread :- https://apachedruidworkspace.slack.com/archives/C0309C9L90D/p1693605318497269 I'm having similar requirement...