Client Management: How to find devices on which a certain software is not installed

Knowledge Article

Article Number

000367396

Old Article Number

000130370

Article Type

FAQ/Procedural

Title

Client Management: How to find devices on which a certain software is not installed

Summary

Using the power of free query the BCM administrators will be able to find the devices in BCM with a certain software not installed and create a dynamic device group which will auto update.

Product

BMC Client Management

Component

Client Management

Applies to

All BMC Client Management (BCM) Versions

Question

How to find the devices with certain software not installed?
How can we find laptops or servers on which 'X' software is not installed using SQL query?

Answer

The following example explains how to create a free query to find the devices with the device type 'Server' on which the application 'skype' is NOT installed. The query can be modified for one's purpose (e.g take the clause on "Server" to list all device types in the results). One more example can be found at the bottom of this knowledge article.

Go to Queries node and create a new query using the icon in toolbar

Give a meaningful name to the query, keep the Type set to Devices and enable 'Free Query' checkbox. Click OK. Once the query is created, double click on it to open the query parameters.

Copy and paste below query under the SQL Query section available under SQL Tab. The below query will show the list of devices with Type 'Servers' which don't have 'Skype' application/software installed. Edit the query and update the name of the application/software as per the requirement.

SELECT Devices.DeviceName from Devices
WHERE Devices.DeviceType ='Server' and devices.DeviceName
not in (select Devices.DeviceName from  Devices
LEFT outer JOIN SwInventories si
ON Devices.DeviceID=si.DeviceID LEFT outer JOIN
SwInvApps sa  ON si.AppID=sa.AppID
WHERE ((Devices.DeviceType ='Server') AND  (sa.Name like '%skype%')))

Click on the toolbar icon to verify the SQL query and check the SQL Result at the bottom of the screen. If the SQL query is successfully verified, then click Save icon on the toolbar to save the query. Set the Query status from Inactive to Active.

Right click on the query and click 'Create Device Group'. This will create a new dynamic device group that will appear under the 'Device Groups' node.

Select 'Only Devices with an Agent' from the 'Device Type' dropdown and click OK. This will display the result for the managed devices. If 'All Devices' is set, then the unmanaged devices will also be a part of the device group.

Go to Device Groups node and refresh the console. The newly created dynamic device group will appear there.

Click on the device group to see the list of devices meeting the criteria as per the query.

The below query will list all devices:

with an agent installed, including the master and relays (no unmanaged device will be listed)
excluding the devices NGXXX1 and NGXXX2
that do NOT have the application "KeyView OOP APP" installed

SELECT Devices.DeviceName from Devices
WHERE devices.DeviceName
not in (select Devices.DeviceName from Devices
LEFT outer JOIN SwInventories si
ON Devices.DeviceID=si.DeviceID LEFT outer JOIN
SwInvApps sa ON si.AppID=sa.AppID
WHERE sa.Name='KeyView OOP APP')
AND Devices.TopologyType in ('_DB_DEVTYPE_CLIENT_', '_DB_DEVTYPE_RELAY_','_DB_DEVTYPE_MASTER_')
AND (Devicename like 'NB%' OR Devicename like 'PC%')
AND DeviceName not in ('NGXXX1','NGXXX2');

Additionally, this KA 000115753 covers creating dynamic device groups using queries, which will be helpful in such scenarios.

Attachment(s):