Sum distinct values

[mert tan]

Hi There,

I'm having a trouble to "Exclude" the duplicate values.

I need to sum of unique values.

At the moment, I can find the repeated values with count_values

Here is the code

count_values("value", vmware_vm_guest_disk_capacity{vm_name="xxx.com"} )

So, I'm pretty new at the promql. I couldn't write the query for it.

So, If you guys show me how can I add the values inside the Element or How to get unique values from query, I'll appreciate it.

Element	Value
{value="121687089152"}	24
{value="84417077248"}	1
{value="782929920"}	1
{value="2136997888"}	4

[Brian Candler]

Can you share the raw metrics, i.e.

vmware_vm_guest_disk_capacity{vm_name="xxx.com"}

and then explain what you're trying to extract from them? 

[mert tan]

Thank you for your response,

I'm calculating the vmware disk usage, but prometheus has no this feature at the moment, so I'm doing vmware_vm_guest_disk_capacity - vmware_vm_guest_disk_free

Here is the problem, regarding the virtualization files, the files are pointing each other and it brings duplicate values. So results are incorrect.

In the powershell with get-harddisk command I can get the real usages. If you know how can I use get-harddisk in Prometheus that will work as well.

$vm = get-vm host.com
get-harddisk -vm $vm

So If I can get the unique results, I'll be able to calculate the correct numbers

Here is the what I'm seeing after the query of  vmware_vm_guest_disk_capacity{vm_name="host.com"}

vmware_vm_guest_disk_capacity{job="vmware",partition="/",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/backup",vm_name="host.com"}    84417077248
vmware_vm_guest_disk_capacity{job="vmware",partition="/boot",vm_name="host.com"}    782929920
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/etc/alternatives",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/etc/apache2",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/etc/mail",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/etc/pki/ca-trust",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/etc/pki/tls",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/etc/scl",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/home/fld",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/opt",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/tmp",vm_name="host.com"}    2136997888
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/usr",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/archives",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/data",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/lists",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/locks",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/logs",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/qfiles",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/spam",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/usr/sbin",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/var",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/var/cpanel/email_send_limits",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/var/cpanel/php/sessions",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/var/log",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/var/spool",vm_name="host.com"}    121687089152
vmware_vm_guest_disk_capacity{job="vmware",partition="/home/virtfs/fld/var/tmp",vm_name="host.com"}    2136997888
vmware_vm_guest_disk_capacity{job="vmware",partition="/tmp",vm_name="host.com"}    2136997888
vmware_vm_guest_disk_capacity{job="vmware",partition="/var/tmp",vm_name="host.com"}    2136997888

[Brian Candler]

You're assuming that two disks that have the same capacity, are the same disk.  You can't be sure that's true.  The way you tell two metrics apart is that they have different labels, not different values.

What I *think* is going on is you have a load of alias mounts, where an existing filesystem is aliased under /home/virtfs/fld.  So I'd suggest you simply filter those out, to leave the real filesystems.

Unfortunately you don't have a filesystem mount type label (perhaps you could fix the exporter to include this?), but until then you could do:

vmware_vm_guest_disk_capacity{partition!~"/home/virtfs/.*|/var/tmp"}

(on the assumption /var/tmp is an alias of /tmp, although again I can't be sure of that; it could be two separate disks of identical size).  Then you can extend it to:

vmware_vm_guest_disk_capacity{partition!~"/home/virtfs/.*|/var/tmp"} - vmware_vm_guest_disk_free

You don't need to repeat the label filter: the subtraction only operates between two series with exactly the same set of labels.

[mert tan]

Brian,

Even vmware_vm_guest_disk_free values are the same and unfortunately, the file structure is not consistence. I can't rely on them.

Here is the capacity - free calculation and you can see from them there is a pointing issue.

The last point I came,

( count_values ("value", vmware_vm_guest_disk_capacity{ vm_name="$vm_name" } ) ) and I can see the unique values but I couldn't sum them.

I've attached the screenshot.

What can I do after this point ?

vmware_vm_guest_disk_capacity{vm_name="host.com"} - vmware_vm_guest_disk_free{vm_name="host.com"}

{job="vmware",partition="/",vm_name="host.com"}    29796904960
{job="vmware",partition="/backup",vm_name="host.com"}    35256586240
{job="vmware",partition="/boot",vm_name="host.com"}    202129408
{job="vmware",partition="/home/virtfs/fld/etc/alternatives",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/etc/apache2",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/etc/mail",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/etc/pki/ca-trust",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/etc/pki/tls",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/etc/scl",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/home/fld",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/opt",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/tmp",vm_name="host.com"}    34209792
{job="vmware",partition="/home/virtfs/fld/usr",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/archives",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/data",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/lists",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/locks",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/logs",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/qfiles",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/usr/local/cpanel/3rdparty/mailman/spam",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/usr/sbin",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/var",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/var/cpanel/email_send_limits",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/var/cpanel/php/sessions",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/var/log",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/var/spool",vm_name="host.com"}    29796904960
{job="vmware",partition="/home/virtfs/fld/var/tmp",vm_name="host.com"}    34209792
{job="vmware",partition="/tmp",vm_name="host.com"}    34209792
{job="vmware",partition="/var/tmp",vm_name="host.com"}    34209792

[Brian Candler]

You can write your own code which queries the prometheus API and does whatever you like with the data.

PromQL can't do what you're asking, because in general it is not sensible for processing timeseries.  The *definition* of a timeseries is a collection of values with a unique set of labels.  All those things are unique timeseries; it doesn't matter whether the values happen to be the same or not.

If I were in your situation, what I would do is to see if I could fix the vmware exporter to include a filesystem type, like node_exporter has:

node_filesystem_size_bytes{device="lxcfs", fstype="fuse.lxcfs", instance="nuc1", job="node", mountpoint="/var/lib/lxcfs", netbox_type="device"} => 0 @[1583914742.097]

node_filesystem_size_bytes{device="tmpfs", fstype="tmpfs", instance="nuc1", job="node", mountpoint="/run", netbox_type="device"} => 824844288 @[1583914742.097]

node_filesystem_size_bytes{device="tmpfs", fstype="tmpfs", instance="nuc1", job="node", mountpoint="/run/lock", netbox_type="device"} => 5242880 @[1583914742.097]

...

This can easily be filtered on device and/or fstype.