{"id":102,"date":"2019-05-29T04:58:26","date_gmt":"2019-05-29T04:58:26","guid":{"rendered":"http:\/\/edcint.co.nz\/checkwmiplus\/?page_id=102"},"modified":"2019-05-30T10:14:10","modified_gmt":"2019-05-30T10:14:10","slug":"join-and-joinquery","status":"publish","type":"page","link":"https:\/\/edcint.co.nz\/checkwmiplus\/ini-file-documentation\/join-and-joinquery\/","title":{"rendered":"join and joinquery"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-69e1764aec371\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-69e1764aec371\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/edcint.co.nz\/checkwmiplus\/ini-file-documentation\/join-and-joinquery\/#Worked_Example\" >Worked Example<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/edcint.co.nz\/checkwmiplus\/ini-file-documentation\/join-and-joinquery\/#Example1_%E2%80%93_making_it_lower_case_and_fixing_the\" >Example1 &#8211; making it lower case and fixing the #<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/edcint.co.nz\/checkwmiplus\/ini-file-documentation\/join-and-joinquery\/#Example2_%E2%80%93_ignoring_the_1\" >Example2 &#8211; ignoring the _1<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/edcint.co.nz\/checkwmiplus\/ini-file-documentation\/join-and-joinquery\/#Putting_it_all_together\" >Putting it all together<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n\r\n<p>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.<\/p>\r\n\r\n\r\n\r\n<p>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.<\/p>\r\n\r\n\r\n\r\n<p>The full join specification is made up of a mtching pair of join= and joinquery= settings in the following format<br \/>join=ID,INDEX,BASEFIELD,BASEREGEX,BASEREPLACEMENT,EXTRAFIELD,EXTRAREGEX,EXTRAREPLACEMENT,NUMQUERIES,WMINAMESPACE<br \/>joinquery=QUERY<\/p>\r\n\r\n\r\n\r\n<p>where<br \/>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<br \/>INDEX= not used &#8211; the WMI query number in the base array that we will be joining data to<br \/>BASEFIELD=the field in base query that we will be looking at for a match<br \/>BASEREGEX=regex to apply to the value in the BASEFIELD &#8211; allows matching when the fields are not identical &#8211; we extract $1$2$3$4$5 from the regex and use that<br \/>BASEREPLACEMENT=if this is specified it is used to replace whatever is found by the regex &#8211; use for replacing # by _ etc. Set to undef if not to be used<br \/>EXTRAFIELD=the field in joining query that we will be looking at for a match<br \/>EXTRAREGEX=regex to apply to the value in the EXTRAFIELD &#8211; allows matching when the fields are not identical &#8211; we extract $1$2$3$4$5 from the regex and use that<br \/>EXTRAREPLACEMENT=if this is specified it is used to replace whatever is found by the regex &#8211; use for replacing # by _ etc. Set to undef if not to be used<br \/>NUMQUERIES=Set to 1 for now<br \/>WMINAMESPACE=The WMI name space if not the default<\/p>\r\n\r\n\r\n\r\n<p>QUERY=a WMI query. It should return at least EXTRAFIELD so a match can be performed<\/p>\r\n\r\n\r\n\r\n<p>Duplicates fields in the joinquery are lost as the original WMI query fields are not overwritten<\/p>\r\n\r\n\r\n\r\n<p>For a sample query using joins see checkprint test<br \/>join=PrintConfig,,Name,,,Name,,,1,<br \/>joinquery=Select Name,HorizontalResolution,VerticalResolution,PrintProcessor from Win32_Printer<\/p>\r\n\r\n\r\n\r\n<h2><span class=\"ez-toc-section\" id=\"Worked_Example\"><\/span>Worked Example<span class=\"ez-toc-section-end\"><\/span><\/h2>\r\n\r\n\r\n<p>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.<\/p>\r\n\r\n\r\n\r\n<p>Lets assume the first WMI class returns some results like this:<br \/><code>Select ProcID,Name from Example1<\/code><\/p>\r\n\r\n\r\n\r\n<table class=\"wp-block-table\">\r\n<thead>\r\n<tr>\r\n<td>ProcID<\/td>\r\n<td>Name<\/td>\r\n<\/tr>\r\n<\/thead>\r\n<tbody>\r\n<tr>\r\n<td>123<\/td>\r\n<td>Process#A<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>456<\/td>\r\n<td>Process#B<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>789<\/td>\r\n<td>Process#C<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n\r\n\r\n\r\n<p>Now lets assume that the second WMI class returns results like this:<br \/><code>Select RAMUsed,ProcName from Example2<\/code><\/p>\r\n\r\n\r\n\r\n<table class=\"wp-block-table\">\r\n<thead>\r\n<tr>\r\n<td>RAMUsed<\/td>\r\n<td>ProcName<\/td>\r\n<\/tr>\r\n<\/thead>\r\n<tbody>\r\n<tr>\r\n<td>1GB<\/td>\r\n<td>process_a_1<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>2GB<\/td>\r\n<td>process_b_1<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>3GB<\/td>\r\n<td>process_c_1<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n\r\n\r\n\r\n<p>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.<\/p>\r\n\r\n<h3><span class=\"ez-toc-section\" id=\"Example1_%E2%80%93_making_it_lower_case_and_fixing_the\"><\/span>Example1 &#8211; making it lower case and fixing the #<span class=\"ez-toc-section-end\"><\/span><\/h3>\r\n\r\n<p><br \/>In all cases the comparison between fields we want to join is made in a case insensitive way, so we don&#8217;t need to worry about that.<br \/>We can either remove the the # or convert it to an _ (we choose this option for demonstration)<br \/>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:<br \/>Search: <code>#<\/code><br \/>and<br \/>Replace: <code>_<\/code><\/p>\r\n\r\n<h3><span class=\"ez-toc-section\" id=\"Example2_%E2%80%93_ignoring_the_1\"><\/span>Example2 &#8211; ignoring the _1<span class=\"ez-toc-section-end\"><\/span><\/h3>\r\n\r\n<p><br \/>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 ().<br \/>So if we have <code>process_c_1<\/code> our regular expression to select all the parts before _1 is<br \/><code>^(.*)_1$<\/code><br \/>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<br \/><code>^(.*)_\\d+$<\/code><\/p>\r\n\r\n<h3><span class=\"ez-toc-section\" id=\"Putting_it_all_together\"><\/span>Putting it all together<span class=\"ez-toc-section-end\"><\/span><\/h3>\r\n\r\n<p><br \/>We have now worked out most of what we need to build our join statement.<br \/>For this example we will assume:<br \/>ID &#8211; will not be specified since we do not want to cache our results<br \/>INDEX &#8211; is not used anyway<br \/>NUMQUERIES &#8211; 1<br \/>WMINAMESPACE &#8211; is the default anyway<\/p>\r\n\r\n\r\n\r\n<p>So our join statement goes from the format of<br \/>join=ID,INDEX,BASEFIELD,BASEREGEX,BASEREPLACEMENT,EXTRAFIELD,EXTRAREGEX,EXTRAREPLACEMENT,NUMQUERIES,WMINAMESPACE<br \/>to<br \/><code>join=,,Name,#,_,ProcName,^(.*)_1$,undef,1,<\/code><\/p>\r\n\r\n\r\n\r\n<p>We already have our joinquery statement details, they are just the second WMI query from above<br \/><code>joinquery=Select RAMUsed,ProcName from Example2<\/code><\/p>\r\n\r\n\r\n\r\n<p>Put all this together with the first WMI query<br \/><code>query=Select ProcID,Name from Example1<\/code><\/p>\r\n\r\n\r\n\r\n<p>and your resulting joined data should look like:<\/p>\r\n\r\n\r\n\r\n<table class=\"wp-block-table\">\r\n<thead>\r\n<tr>\r\n<td>ProcID<\/td>\r\n<td>Name<\/td>\r\n<td>RAMUsed<\/td>\r\n<td>ProcName<\/td>\r\n<\/tr>\r\n<\/thead>\r\n<tbody>\r\n<tr>\r\n<td>123<\/td>\r\n<td>Process#A<\/td>\r\n<td>1GB<\/td>\r\n<td>process_a_1<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>456<\/td>\r\n<td>Process#B<\/td>\r\n<td>2GB<\/td>\r\n<td>process_b_1<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>789<\/td>\r\n<td>Process#C<\/td>\r\n<td>3GB<\/td>\r\n<td>process_c_1<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n\r\n\r\n\r\n<p>Now there is a single data set that you can use for your checks.<\/p>\r\n\r\n\r\n\r\n<p>Oh, if you haven&#8217;t worked it out yet, these queries and fields are imaginary but the principle is the same for real ones.<\/p>\r\n","protected":false},"excerpt":{"rendered":"<p>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&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":79,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[15,7],"tags":[],"class_list":["post-102","page","type-page","status-publish","hentry","category-documentation","category-ini-file-field-doc"],"_links":{"self":[{"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/pages\/102","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/comments?post=102"}],"version-history":[{"count":4,"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/pages\/102\/revisions"}],"predecessor-version":[{"id":247,"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/pages\/102\/revisions\/247"}],"up":[{"embeddable":true,"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/pages\/79"}],"wp:attachment":[{"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/media?parent=102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/categories?post=102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/edcint.co.nz\/checkwmiplus\/wp-json\/wp\/v2\/tags?post=102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}