Show a top 20 list of servers that consume the most disk space

[MillerGEP]

Hi Everyone. I'm trying to create a dashboard with a top 20 list of servers that consume the most disk space. I'm using this DQL, but it's not working. I think it's because I don't have the dt.host.disk.total metric. So, which one can I use? What do you recommend?

timeseries {used=avg(dt.host.disk.used), total=avg(dt.host.disk.total)}, by:{dt.host.name,dt.host.disk.name}
| fieldsAdd capacidad=arrayAvg(total), usado=arrayAvg(used)
| fieldsAdd Porcentaje_Usado=(usado*100)/capacidad
| sort Porcentaje_Usado desc
| fieldsRename Host=dt.host.name, Disco=dt.host.disk.name
| fields Host, Disco, capacidad, usado, Porcentaje_Usado
| limit 20

Any idea ?

[p_devulapalli]

@MillerGEP You can try something like below for total

| fieldsAdd 
 Disk = entityName(dt.entity.disk),
 Free = arrayLast(`avg(dt.host.disk.free)`),
 Space_Used = arrayLast(`avg(dt.host.disk.used)`),
 Space_Available = arrayLast(`avg(dt.host.disk.avail)`)
| fieldsAdd Space_Total = Space_Used + Space_Available

[MillerGEP]

Hi @p_devulapalli  thanks for the sugerence. I don't know if i doing something bad, because isn't working.

The message indicate that dt.host.disk.free doesn't exist ---->

And i see that metric only work for f5 hosts and my dashboard is for host with oneagent installed...

Any idea ?

[JeanBlanc]

Hi @MillerGEP,

Dynatrace provide the following DQL to get 15 hosts with highest utilization (5 highest CPU, 5 highest memory, 5 highest disk usages) :

fetch dt.entity.host
| filter id in [

timeseries { CPU=avg(dt.host.cpu.usage) }, by: {dt.entity.host}
| fieldsAdd CPU=arrayLast(CPU)
| filterOut isNull(CPU)
| sort CPU desc
| limit 5

| append [
timeseries { Memory=avg(dt.host.memory.usage) }, by: {dt.entity.host}
| fieldsAdd Memory=arrayLast(Memory)
| filterOut isNull(Memory)
| sort Memory desc
| limit 5
]

| append [
timeseries { Disk=avg(dt.host.disk.used.percent) }, by: {dt.entity.host}
| fieldsAdd Disk=arrayLast(Disk)
| filterOut isNull(Disk)
| sort Disk desc
| limit 5
]

| dedup dt.entity.host
| fields dt.entity.host
]

| lookup [
timeseries {
CPU=avg(dt.host.cpu.usage),
Memory=avg(dt.host.memory.usage),
Disk=avg(dt.host.disk.used.percent)
}, by: {dt.entity.host}
| fields CPU=arrayLast(CPU), Memory=arrayLast(Memory), Disk=arrayLast(Disk), dt.entity.host
], sourceField:id, lookupField:dt.entity.host, fields: {CPU, Memory, Disk, dt.entity.host}, executionOrder:leftFirst

| fields Name=entity.name, dt.entity.host, CPU, Memory, Disk

Is that OK for you ?

Regards,

[MillerGEP]

Hi @JeanBlanc  i think that maybe this DQL working for me. But I have 2 questions:

1) What disk/unit/filesystem consume the percentage, disk C or D or /opt, the dql show the consume of disk but not indicate wich.

2) How can only show data of host that have installed oneagent, on the screen you can see name like dispatcher.resolution, bank-integrator wich isn't clear if is a server.

[Pierre_L]

Hi @MillerGEP ,

I suggest to add 2 dimensions in your query : dt.entity.disk and dt.metric.source

Then you have to do a lookup to fetch disk name.

A step by step query like this should help you (starting on the lookup part of your query) :

| lookup [
timeseries {
Disk=avg(dt.host.disk.used.percent)
}, by: {dt.entity.host,dt.entity.disk,dt.metrics.source}
| fields Disk=arrayLast(Disk), dt.entity.host, dt.entity.disk, dt.metrics.source
], sourceField:id, lookupField:dt.entity.host, fields: {Disk, dt.entity.host, dt.entity.disk,dt.metrics.source}, executionOrder:leftFirst

| fields Name=entity.name, dt.entity.host, Disk, DiskId=dt.entity.disk, DataSource=dt.metrics.source

| lookup [
fetch dt.entity.disk
| fields id, entity.name
 ], sourceField:DiskId , lookupField:id, fields:{entity.name}, executionOrder:leftFirst
| fields Name, Disk, DiskName=entity.name, DataSource

Hope this helps !