Is there a common column in Fleet to join different tables o

Question

Is there a common column in Fleet to join different tables on I want to run a policy query which works but I d like to do a JOIN with the os version table and add a WHERE so that only certain OS versions are checked

zwass · Answer

Can you tell us a bit more about exactly what you d like to check with the policy This would help us point you in the right direction

David J Davis · Answer

I m using the programs table doing a WHERE to catch a single program and WHERE NOT to indentify if the version isn t current The policy basically checks if the machine has the current version of an agent on it

David J Davis · Answer

```SELECT name version FROM programs WHERE name LIKE Ivanti Environment Manager Agent AND version = 10 6 16 0 ```

David J Davis · Answer

Sorry I m not using a NOT WHERE there is the SQL Anyway this agent is only on our Windows workstations and not our servers So if I could join the os version table and filter with it I could only return results for workstation OSs

David J Davis · Answer

I wanted to join on hostname but it doesn t appear to actually be part of the tables so it errors when I try to use it

Gavin · Answer

You can use host labels via a query to filter to Windows Desktops for example then set the policy to only apply to hosts of that label This approach avoids having to pollute your query and allows re use

David J Davis · Answer

I have a label built for Windows Desktops how do I use that in a policy

Gavin · Answer

Ohh snap sorry that s not a feature I was sure it was looks like it s only applicable to queries

David J Davis · Answer

OK yeah I looked for a way to use labels on the Policy creation page that would be a nice option to have

Gavin · Answer

Anyway this is sqllite so you don t actually have to join for example ```select FROM apps os version where os version version = 12 0 1 ```

zwass · Answer

Currently you can target policies per platform You can also set different policies per Team if you re on Fleet Premium

zwass · Answer

Would targeting to all Windows work for this or would you want to skip Windows Servers

David J Davis · Answer

I need to skip servers they don t have the agent installed and will always fail

David J Davis · Answer

This doesn t work seems to return results for machines that are in compliance ```SELECT 1 FROM programs os version WHERE programs name LIKE Ivanti Environment Manager Agent AND programs version = 10 6 16 0 AND os version name NOT LIKE Microsoft Windows Server ```

David J Davis · Answer

My SQL sucks though so maybe a syntax error

Gavin · Answer

Double quote your likes for expansion vs literal

David J Davis · Answer

```SELECT programs version programs name os version name FROM programs os version WHERE programs name LIKE Ivanti Environment Manager Agent AND programs version lt gt 10 6 16 0 AND os version name NOT LIKE Microsoft Windows Server ```

David J Davis · Answer

That works it doesn t return the programs name from the select statement which isn t important for policy just playing around with it

zwass · Answer

Maybe something like `SELECT 1 WHERE select count from os version where name LIKE Microsoft Windows Server gt 0 OR select count from programs WHERE name LIKE Ivanti Environment Manager Agent AND version = 10 6 16 0 gt 0 `

zwass · Answer

Essentially it s in compliance if it s a server OR it has Ivanti 10 6 16 0 installed

David J Davis · Answer

Exactly

David J Davis · Answer

< zwass> On pricing that is $12 year per device that has osquery reporting to FleetDM for Premium

zwass · Answer

Yep that s right I know that our sales team has been making discounts for large early customers let me know if you d like me to connect you

David J Davis · Answer

We aren t a huge organization so would be looking at about 2000 endpoints I spoke to our director today and will follow up with my supervisor when he returns from PTO on Monday I appreciate your help as always

zwass · Answer

Always happy to help slightly smiling face