Hi @ksnihur thank you for your contribution! We'll vet this submission and if it runs as expected we'll change this status of your query from Under Review to CB Approved.
Is there an approved query to join on the os_version table and filter results to endpoints where os_version.platform = 'windows' and os_version.name NOT LIKE '%SERVER%'?
IE I only want the bitlocker status of non-server Windows systems.
@mjw when you run a query it will run against all of the endpoints you have selected, per policy or individually, regardless of what the query is.
In the case of Bitlocker, this table is Windows only so it will error out on all other OSs. You can use the filters in the UI to hide the errors in your results.
I am trying to do something similar, but want to try to pull from the system_info table to help determine if the machine is a laptop, desktop, or server. The issue I am having is that the "bitlocker_info" table has a "device_id" field, but the "system_info" table does not. Any ideas on how I could possibly run a query on the system_info table which would populate the output with the device_id and device_name, then use that temp output to JOIN with on a bitlocker query? Im a SQL newb so I appreciate the help.
I was trying to do this, but need the query against all systems with the system_info query to get the device_id's:
SELECT b.drive_letter,
CASE b.protection_status
WHEN 0 THEN "OFF"
WHEN 1 THEN "ON (Unlocked)"
WHEN 2 THEN "ON (Locked)"
END "Bitlocker Status",
s.hostname,
s.cpu_type,
s.cpu_brand,
s.cpu_physical_cores,
s.cpu_logical_cores,
s.hardware_vendor,
s.hardware_model,
s.hardware_version,
s.hardware_serial
FROM bitlocker_info b JOIN system_info s on b.device_id=s.device_id;
@bglaze Those two tables do not have a column that you can do a JOIN on, but you can "add" to make the JOIN work. This fix only works because these tables only return one row.
WITH b AS (
SELECT 1 AS 'one', *
FROM bitlocker_info b),
s AS (
SELECT 1 AS 'one', *
FROM system_info)
SELECT b.drive_letter,
CASE b.protection_status
WHEN 0 THEN "OFF"
WHEN 1 THEN "ON (Unlocked)"
WHEN 2 THEN "ON (Locked)"
END "Bitlocker Status",
s.hostname,
s.cpu_type,
s.cpu_brand,
s.cpu_physical_cores,
s.cpu_logical_cores,
s.hardware_vendor,
s.hardware_model,
s.hardware_version,
s.hardware_serial
FROM b
JOIN s USING(one);
@vdagostino1 This would be on the Live Query tab in either the VMware Carbon Black Cloud or VMware Carbon Black EDR (formerly CB Response). Then on that page, you would go to the SQL tab to enter the raw SQL.
If you don't see the tab in the VMware Carbon Black Cloud, you would need to add VMware Carbon Black Audit and Remediation to your portfolio. If you don't see the tab in VMware Carbon Black EDR, then you would need to upgrade to a supported version.
Valid.Please fill out this
field.
InValid.Please fill out valid email.
Valid.Please fill out this
field.
Please do not send sensitive data through this form
Valid.Please fill out this
field.Description length should be less 30K