Example Filter Expressions

You can use the following examples of Filter Expressions to create dynamic groups. Note that an Update Software inventory or an Update Hardware Inventory operation must be performed before you can use some of these Filter Expressions.

All Clients

SELECT * FROM NODES;

All Clients in the Administration Department

SELECT * FROM NODES WHERE DEPT LIKE 'admin%';

All Clients in the Sales Department

SELECT * FROM NODES WHERE DEPT LIKE 'sales%';

Clients belonging to the Engineering and Sales Domains

SELECT * FROM NODES WHERE DOMAINNAME IN ('ENGINEERING', 'SALES');

Clients in Departments beginning with a letter within the range A - F

SELECT * FROM NODES WHERE DEPT BETWEEN 'a%' AND 'f%';

All Pentium PCs

SELECT * FROM NODES WHERE CPU LIKE 'Pentium%';

Unknown CPU types

SELECT * FROM NODES WHERE CPU IS NULL;

Clients where a Hardware Inventory is required

SELECT * FROM NODES INNER JOIN NODEPROP ON NODEPROP.NODENAME = NODES.NODENAME WHERE NODEPROP.PROPNAME = '$HWDATE' AND NODEPROP.PROPVALUE is NULL;

Clients where a Software Inventory is required

SELECT * FROM NODES INNER JOIN NODEPROP ON NODEPROP.NODENAME = NODES.NODENAME WHERE NODEPROP.PROPNAME = '$SWDATE' AND NODEPROP.PROPVALUE is NULL;

Clients for which Hardware Inventory data has not been collected this month (amend the date value at the end of this expression)

SELECT * FROM NODES INNER JOIN NODEPROP ON NODEPROP.NODENAME = NODES.NODENAME WHERE NODEPROP.PROPNAME = '$HWDATE' AND NODEPROP.PROPVALUE NOT LIKE '200407%';

Clients for which Software Inventory data has not been collected this month (amend the date value at the end of this expression)

SELECT * FROM NODES INNER JOIN NODEPROP ON NODEPROP.NODENAME = NODES.NODENAME WHERE NODEPROP.PROPNAME = '$SWDATE' AND NODEPROP.PROPVALUE NOT LIKE '2000407%';

All WinZip users

SELECT * FROM NODES INNER JOIN PKGRPTDB ON PKGRPTDB.NODENAME = NODES.NODENAME WHERE PKGRPTDB.PACKAGE LIKE 'WinZip%' AND PKGRPTDB.PKGSTATUS NOT LIKE 'Removed';

Clients with an S3 video driver

SELECT * FROM HWUPDATE INNER JOIN HWMAIN ON HWUPDATE.NODENAME = HWMAIN.NODENAME AND HWUPDATE.HWDATE = HWMAIN.HWDATE WHERE HWMAIN.SCREENTYPE LIKE 'S3%';