hide "hostname" column in query

[oilian]

Is there a way to write a query and not get the "hostname" column returned?

I am trying to get a list of all OSes and the number it is showing in the database. I was using this query:

select name || ' ' || version as OS, count(version) as Count from os_version group by version

to get something like a pivot table, that has the OS as the first column and the number the OS is showing up in the second column:

Fedora 30 10 Fedora 29 15 Windows 10 2

However, the "hostname" column is always showing up and I get:

host1 1 Fedora 30 host2 1 Fedora 30 host3 1 Windows 10 host4 1 Fedora 29 host5 1 Windows 10

Any idea how to get the first version?

Thanks Oliver

[zwass]

This is a very interesting problem...

When Fleet runs a query, it runs that query on each targeted host and provides the union of the results from those hosts. There's not a mechanism exposed that allows you to do a "meta" query on the results returned by those hosts.

I've always been interested in trying to find a way to do such a query, but it's tricky from a technical and UX perspective.

There may be a point of confusion in how the system works above. I see you mentioned "the number it is showing in the database". When you run a query in Fleet you aren't accessing a database, you're reaching out to each individual host for the results of that query. You could run such a query in the Fleet database, but that's not exposed through any UI.

In this particular case you can totally achieve the goal with the tools you have (might need to install jq):

fleetctl query --labels 'All Hosts' --query "select name || ' ' || version as OS from os_version" > oses.txt
cat oses.txt | jq '.rows[0].OS' | sort | uniq -c | sort -nr

Results look like:

   7 "Ubuntu 16.04.2 LTS (Xenial Xerus)"
   4 "CentOS CentOS release 6.8 (Final)"
   3 "Ubuntu 14.04.5 LTS, Trusty Tahr"
   2 "CentOS Linux 7 (Core)"