join and joinquery

Query JOINS help you join data from another WMI Class to the base query data. This provides you with a larger more rich data set to obtain check data from. If you are familiar with SQL then you will understand this immediately. This implementation is a very basic implementation (when compared to what is in SQL). WQL (WMI Query Language) does have joins but wmic does not seem to implement it.

You can specify multiple joins in the order that you wish them to happen so that you can join multiple classes together to a single data set. For example, checknetwork joins up to 4 classes together to get the required information to allow users to specify the network connection by MAC address or Display Name.

The full join specification is made up of a mtching pair of join= and joinquery= settings in the following format
join=ID,INDEX,BASEFIELD,BASEREGEX,BASEREPLACEMENT,EXTRAFIELD,EXTRAREGEX,EXTRAREPLACEMENT,NUMQUERIES,WMINAMESPACE
joinquery=QUERY

where
ID=an identifier. If specified this signals to use a join state query file (ie cache the join result). If specified it is a short text String that is used in the join state file name to make sure it is unique
INDEX= not used – the WMI query number in the base array that we will be joining data to
BASEFIELD=the field in base query that we will be looking at for a match
BASEREGEX=regex to apply to the value in the BASEFIELD – allows matching when the fields are not identical – we extract $1$2$3$4$5 from the regex and use that
BASEREPLACEMENT=if this is specified it is used to replace whatever is found by the regex – use for replacing # by _ etc. Set to undef if not to be used
EXTRAFIELD=the field in joining query that we will be looking at for a match
EXTRAREGEX=regex to apply to the value in the EXTRAFIELD – allows matching when the fields are not identical – we extract $1$2$3$4$5 from the regex and use that
EXTRAREPLACEMENT=if this is specified it is used to replace whatever is found by the regex – use for replacing # by _ etc. Set to undef if not to be used
NUMQUERIES=Set to 1 for now
WMINAMESPACE=The WMI name space if not the default

QUERY=a WMI query. It should return at least EXTRAFIELD so a match can be performed

Duplicates fields in the joinquery are lost as the original WMI query fields are not overwritten

For a sample query using joins see checkprint test
join=PrintConfig,,Name,,,Name,,,1,
joinquery=Select Name,HorizontalResolution,VerticalResolution,PrintProcessor from Win32_Printer

Worked Example

Lets say we have 2 WMI classes which have the information we need to make a single check. One class has the Process ID and the other class has the RAM Utilisation of that process.

Lets assume the first WMI class returns some results like this:
Select ProcID,Name from Example1

Now lets assume that the second WMI class returns results like this:
Select RAMUsed,ProcName from Example2

Now we notice the the Name fields looks like the common field between these 2 classes, apart from the case, the # and the extra _1 on the end of one of them. The join statement allows you to define the common fields and how they need to be manipulated to match. In this case, the Name field from Example1 needs to be lower case and ignore the # (or convert it to _) and the ProcName field from Example2 needs to have the _1 part ignored. We can do this.

Example1 – making it lower case and fixing the #

In all cases the comparison between fields we want to join is made in a case insensitive way, so we don’t need to worry about that.
We can either remove the the # or convert it to an _ (we choose this option for demonstration)
The format for the join statement allows us to specify a regular expression for extracting only parts of the field we want to compare or another one for simply converting the field. We want to convert the field. So we need a regular expression for the search and one for the replacement. They are very easy in this case:
Search: #
and
Replace: _

Example2 – ignoring the _1

In order to ignore the _1 we actually just specify that we want to look at the other part of the field by enclosing it in ().
So if we have process_c_1 our regular expression to select all the parts before _1 is
^(.*)_1$
or if there is a chance _1 could become _2 or _23 etc then we need to write it more generically to ignore the last _ followed by any digits, like this
^(.*)_\d+$

Putting it all together

We have now worked out most of what we need to build our join statement.
For this example we will assume:
ID – will not be specified since we do not want to cache our results
INDEX – is not used anyway
NUMQUERIES – 1
WMINAMESPACE – is the default anyway

So our join statement goes from the format of
join=ID,INDEX,BASEFIELD,BASEREGEX,BASEREPLACEMENT,EXTRAFIELD,EXTRAREGEX,EXTRAREPLACEMENT,NUMQUERIES,WMINAMESPACE
to
join=,,Name,#,_,ProcName,^(.*)_1$,undef,1,

We already have our joinquery statement details, they are just the second WMI query from above
joinquery=Select RAMUsed,ProcName from Example2

Put all this together with the first WMI query
query=Select ProcID,Name from Example1

and your resulting joined data should look like:

Now there is a single data set that you can use for your checks.

Oh, if you haven’t worked it out yet, these queries and fields are imaginary but the principle is the same for real ones.