DQL for disks with wasted space

[LouisCypher]

Hello,

could you help me with dql to generate a table with the following columns :  host, disk, disk total size and disk usage% (or disk available) order by disk total size desc, top n records .

Thx!

[jason_gs · Accepted solution]

try something like this...

timeseries t_disk=avg(dt.host.disk.used.percent), interval: 2h,by:{dt.entity.host, dt.entity.disk}
| fieldsAdd Server=entityName(dt.entity.host)
| fieldsAdd Disk=entityName(dt.entity.disk)
| fieldsAdd `Used_Disk_Space_(%)`=arrayAvg(t_disk)
| fieldsRemove timeframe,interval, dt.entity.host, t_disk

| join [
    timeseries disk.used_sum = avg(dt.host.disk.used), disk.avail_sum = avg(dt.host.disk.avail), by:{dt.entity.disk}
    | fieldsAdd total_disk = toLong((arrayAvg(disk.used_sum) + arrayAvg(disk.avail_sum)) / 1024 / 1024)
    | fieldsKeep total_disk, dt.entity.disk
  ], on:{dt.entity.disk}
| fieldsAdd `Total_Disk_(GB)`  = right.total_disk
| fieldsRemove right.dt.entity.disk, right.total_disk
| sort `Used_Disk_Space_(%)` desc
| limit 10