Title
#fleet
David J Davis

David J Davis

01/07/2022, 5:34 PM
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

zwass

01/07/2022, 5:46 PM
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

David J Davis

01/07/2022, 5:58 PM
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.
5:58 PM
SELECT name,version FROM programs WHERE name LIKE '%Ivanti Environment Manager Agent%' AND version = '10.6.16.0';
6:00 PM
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.
6:06 PM
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

Gavin

01/07/2022, 6:21 PM
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

David J Davis

01/07/2022, 6:22 PM
I have a label built for Windows Desktops, how do I use that in a policy?
Gavin

Gavin

01/07/2022, 6:23 PM
Ohh snap sorry that’s not a feature I was sure it was , looks like it’s only applicable to queries.
David J Davis

David J Davis

01/07/2022, 6:25 PM
OK, yeah I looked for a way to use labels on the Policy creation page, that would be a nice option to have.
Gavin

Gavin

01/07/2022, 6:29 PM
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

zwass

01/07/2022, 6:37 PM
Currently, you can target policies per platform. You can also set different policies per Team if you're on Fleet Premium.
6:37 PM
Would targeting to all Windows work for this or would you want to skip Windows Servers?
David J Davis

David J Davis

01/07/2022, 6:40 PM
I need to skip servers, they don't have the agent installed and will always fail.
6:41 PM
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%';
6:42 PM
My SQL sucks though, so maybe a syntax error.
Gavin

Gavin

01/07/2022, 6:49 PM
Double quote your likes for expansion vs literal.
David J Davis

David J Davis

01/07/2022, 7:12 PM
SELECT programs.version, programs.name, os_version.name 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%";
7:13 PM
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

zwass

01/07/2022, 7:31 PM
Maybe something like
SELECT 1 WHERE (select count(*) from os_version where name LIKE  "Microsoft Windows Server%") > 0 OR (select count(*) from programs WHERE name LIKE '%Ivanti Environment Manager Agent%' AND version = '10.6.16.0') > 0;
?
7:32 PM
Essentially, "it's in compliance if it's a server OR it has Ivanti 10.6.16.0 installed"
David J Davis

David J Davis

01/07/2022, 8:30 PM
Exactly
8:33 PM
@zwass On pricing, that is $12/year per device that has osquery reporting to FleetDM for Premium?
zwass

zwass

01/07/2022, 9:02 PM
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

David J Davis

01/07/2022, 9:40 PM
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

zwass

01/07/2022, 11:32 PM
Always happy to help 🙂