Custom Inventory for Windows cannot process a WMI query with a "Where...like '%...%'" clause. The percent sign (%) sign is the standard multi-character wildcard in SQL and WMI queries. No results are returned by custom inventory in the resulting NSI file with such a where clause. How can a "where ... like" clause with two percent sign (%) wildcards be used in custom inventory?
Custom Inventory for Windows uses a pair of percent signs (%) to determine if a string is a variable to be parsed and replaced with a literal value and to identify other custom inventory logic. For example:
<%if %sCheckProperty% != "" %>
When custom inventory finds a wmi query with a "where" clause, such as the following:
<%foreach wmiobject="o" namespace="root\CIMV2" wql="SELECT * FROM Win32_ShortcutFile where name like '%desktop%' "%>
it attempts to replace "%desktop%" with a literal value. Since there is no "desktop" variable that has been set, in this case, it replaces "%desktop%" with null or blank. The resulting query that is processed is:
<%foreach wmiobject="o" namespace="root\CIMV2" wql="SELECT * FROM Win32_ShortcutFile where name like '' "%>
Note: This only occurs when two % wildcards are used in the string value. A single % in the value will not cause this to occur.
To get around this, you can use a wmi query without a "where" clause and add a string function in the custom inventory source to determine if the desired string occurs in the resultset, as shown below. See article 4265 for more information on string functions.
In this example, the goal is to find all shortcuts that exist on the desktop. We will use the "find" function to find all rows that have '\desktop\' in the Name field of the resultset. The Name field is a path. A result of '-1' means the string is not found or another error occurred. Any other value indicates the beginning position in the string.
The custom inventory source logic will be: (Note that fields c2 and c3 are for debugging purposes.)
<%foreach wmiobject="o" namespace="root\CIMV2" wql="SELECT * FROM Win32_ShortcutFile" %>
<%set cur_name = "wmi:o.Name"%>
<%set results="%string str1='%cur_name%' function='find' str2='\desktop\'%" %>
<%if %results%!= "-1"%>
c0 ="<%writexml "wmi:o.Name"%>"
c1 ="<%writexml "wmi:o.Target"%>"
c2 ="<%writexml "%cur_name%"%>"
c3 ="<%writexml "%results%"%>"
The "name" field in the resultset will be:
The results in the NSI file will be:
"c:\documents and settings\all users\desktop\mozilla firefox.lnk"
c0 ="c:\documents and settings\all users\desktop\mozilla firefox.lnk"
c1 ="C:\Program Files\Mozilla Firefox\firefox.exe"
c2 ="c:\documents and settings\all users\desktop\mozilla firefox.lnk"
We have now filtered our WMI query results, as desired.
We could also dispense with the custom inventory logic to filter the results, load the entire resultset and filter the results in a report using SQL logic. This isn't always desirable due to the larger table size that would result.