https://github.com/osquery/osquery logo
Join Slack
Powered by
Is there a common column in Fleet to join differen...
# fleet
d
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.
z
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.
d
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.
Copy code
SELECT name,version FROM programs WHERE name LIKE '%Ivanti Environment Manager Agent%' AND version = '10.6.16.0';
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.
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.
g
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.
d
I have a label built for Windows Desktops, how do I use that in a policy?
g
Ohh snap sorry that’s not a feature I was sure it was , looks like it’s only applicable to queries.
d
OK, yeah I looked for a way to use labels on the Policy creation page, that would be a nice option to have.
g
Anyway this is sqllite so you don’t actually have to join for example.
Copy code
select * FROM apps, os_version where os_version.version = '12.0.1'
z
Currently, you can target policies per platform. You can also set different policies per Team if you're on Fleet Premium.
Would targeting to all Windows work for this or would you want to skip Windows Servers?
d
I need to skip servers, they don't have the agent installed and will always fail.
This doesn't work, seems to return results for machines that are in compliance:
Copy code
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%';
My SQL sucks though, so maybe a syntax error.
g
Double quote your likes for expansion vs literal.
d
Copy code
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%";
That works, it doesn't return the programs.name from the select statement, which isn't important for policy, just playing around with it.
z
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;
?
Essentially, "it's in compliance if it's a server OR it has Ivanti 10.6.16.0 installed"
d
Exactly
@zwass On pricing, that is $12/year per device that has osquery reporting to FleetDM for Premium?
z
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.
d
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.
z
Always happy to help 🙂
3 Views
Open in Slack
PreviousNext
Powered by