Cherwell IT Service Management Blog
Resources, Best Practices, and Solutions for ITSM Pros

August’s Technical Tip: Using the Express Query Builder

Posted by

Let’s suppose you are planning on upgrading to a new version of a critical software package that has steeper system requirements than previous versions. Wouldn’t it be nice to generate a list of all the machines in your organization that already meet those system requirements, or perhaps more importantly, a list of the machines that don’t?

You know that Express Software Manager can provide information about the software and hardware installed on machines, but there is a special filter—the “Express Query Builder”—that you can use to generate a list of all the computers that meet the hardware and software requirements that you specify.

For example, let’s say you want to upgrade all existing installations of Microsoft Windows XP to Microsoft Windows 7, but you also want to make sure that all machines being upgraded have at least four gigabytes of RAM. This is the kind of problem the Query Builder was designed to solve.

The Query Builder is a machine filter you can use to build and save simple queries for selecting machines to include in reports. You can build queries that include or exclude machines based on hardware properties, or the presence or absence of specified license units or applications.

The Query Builder allows you to select expression types, related options, and boolean operators to create your desired search criteria.

To use the example from earlier in this post, let’s say you decide to create a query filter to show those computers with Microsoft Windows XP Professional installed AND with less than 4 GB RAM. Here’s how you would do it using the Query Builder:

1. In the Reports Console’s Filters tab, select the Query Builder filter within Machine filters.

2. Create an expression that returns machines with Microsoft Windows XP Professional installed.

– For Expression type, select License Unit from the drop down menu.
– For Name, select Microsoft Windows XP Professional from the drop-down list of license units.
– For Operator, select the option is installed from the operator drop-down list.
– Click on the green Add button to add that expression to the query builder

3. Add an expression that displays machines with fewer than 4 GB RAM.

– Select the blue “And” button
– For Expression type select Machine property from the drop-down menu.
– For Property, select RAM from the drop-down menu.
– For Operator, select the less than [<] symbol from the drop-down menu.
– For Numeric Value, enter 4.
– Click on the green Add button to add this expression to the query builder.

4) Use the green Validate button to confirm that the syntax you have used for your query is valid.

5) Click on the Save As button to save your query as a filter.

Once you have saved the query, it functions as a machine filter—no different than filtering on individual machines or machine groups—showing only those machines that meet the criteria you have defined in the query builder. You can apply this filter to any report that allows you to filter by machine, such as the Software Inventory Summary report, or the Custom Hardware Inventory report. There is, however, a report that is designed to work specifically with the Query Builder: the Machine List report.

The Machine List report will provide a list of all the computers that meet the requirements of the query you have created. The report lists machine name, domain, IP address, user name of the last user, last name and first name of the user (if this information is gathered as part of discovery), serial number, and platform.

The Query Builder is a powerful tool for selecting machines based on a variety of criteria, whether simple or complex. You can create expressions based on license units, applications, a host of hardware properties, or platform. You can use the And and Or properties to combine expressions, and use parentheses to control the order in which expressions are evaluated, allowing you to build more complex queries to find things like the list of machines were X is true, and either Y or Z is also true. (For more on Boolean operators, see this guide on the University of Nevada’s web site.)

To learn more about the Query Builder filter, use the Reports Console Help file, go to Help Navigation and search for the help topic “how to use the Query Builder.”

Query Builder

Topics: IT Asset Management