K1000 Patching - Setup, Tips & Things I Have Learned (LDAP, Smart Labels, SQL Reports)
________________________________________________________
________________________________________________________

Contents

(01) Getting LDAP queries working from the KBOX
(02) Planning LDAP Labels and ADUC account description tags
(03) Tagging computer (and user) accounts in ADUC
(04) Creating LDAP Labels on the K1000
(05) Patching-specific LDAP labels (examples)
(06) Applying LDAP labels
(07) Setting up patch subscriptions
(08) Creating smart labels for patches
(09) Creating patch schedules (putting it all together)
(10) Troubleshooting patching and other footnotes
(11) SQL Reports
________________________________________________________
________________________________________________________

Preface

This is a *major* revision of the LDAP, Patching & SQL Reports post I did a while back and reference often. I've completely redone my patching setup twice since then and although the main concepts are basically the same, there have been some major changes in details.

The first revision was done to address the Release Date filter I had been using in the patch smart labels. After getting patching and K2000 scripted installs working, I'd moved on to standardizing applications with managed installs. Third party apps like Adobe Reader weren't much of an issue in regards to the patch config, but when I started planning an IE8 managed install, the realization that my patches only went back about a year due to the Release Date filter forced me to revisit my setup as I can't risk missing old IE8 security updates.

I found what I truly believed was the perfect answer - using Status = Active for the patch smart labels, something I've recommended several times in my answers. And it did work for several months, however this past week while reviewing things I saw that all of the patches had dropped out of my patch smart labels. Researching while troubleshooting my issue, I ran across this relatively recent KB article:

Examples of Smart Labels when configuring Patch Labels for OS-specific patches
https://www.kace.com/support/resources/kb/article/Examples-of-Smart-Labels-when-configuring

The most pertinent point being this:

"IMPORTANT NOTE: When using 'Status' of 'Active' patches in the patch smart label, be sure NOT to use this on the subscription page, this is used for Patch Schedules only. It will cause the patches not to download correctly if used in the subscription page."

Since I still have a long list of OSes and applications to patch, as well as a limited amount of drive space on the K1000 to download patches, I worked out a more involved set of smart labels for app patches, which are driven by several layers of smart labels. With my current setup, I'm using ~44GB of disk space for patching, despite all of the OSes and applications being supported. The rest of this guide will be a revision based around the changes necessary to use this setup (as well as other tweaks I've done since the initial guide).
________________________________________________________

I wrote this guide simply to help flesh out and tie together information that I found through my research and support calls, which was used to setup my K1000 to meet the needs of my environment; as such, I would not claim it to be a one-size-fits-all or be-all-end-all authority on things. After having done (and after learning more, redone...) things and taken copious notes, my hope is that this will be a useful resource for anyone who needs guidance, wants to cross-check what they're doing against someone else's processes, or just likes reading AppDeploy posts.

I should begin by saying that a lot of what I will cover regarding LDAP labels can also be done using smart labels. However, I believe that each has its place and that if you are already using Windows Active Directory to store information, then it makes sense to leverage it. Personally, I'm a bit of a control freak and prefer the absolute control that I have with LDAP labels - that is to say I know *exactly* what my LDAP queries are pulling and don't need to worry too much about a potentially open-ended smart label giving me grief later. That being said, if you don't have Active Directory in your environment, don't have (or want to spend) the time populating computer and user objects with data, or just prefer not to use LDAP, I strongly encourage you to look into smart labels (particularly the REGEX functionality for using extended criteria; for example, a smart label to target laptops might use something like this for one of the search criteria: System Model - matches REGEX - Latitude|Thinkpad|Pavilion).

Also, I make mention of user LDAP labels but don't go into a lot of detail beyond what I think may be useful for computer LDAP labels and patching. If there is enough interest, I'll be happy to create a similar guide for setting up automated LDAP user imports so that new users will automatically be added to the K1000 or any of the other subjects that I briefly touch upon. However, I feel that to go into elaborate detail on these would detract from the main focus of this guide.

As for patching... personally, I regard patching hundreds of heterogeneous machines to be more of an art than a science. It is an ongoing collaboration of best practices, reports pulled from machines on effectiveness, and feedback from users on process disruptiveness. I consider it to be one part herding cats and the other part a never-ending relay race (with each new set of exploits and their subsequent patches acting as the runners). As such, there are no absolutes. Any success is a matter of finding the right balance (or recipe, if you will) for each environment and making adjustments as feedback from reports, machines and users deem them necessary.

Finally, there is a good deal of planning necessary to get LDAP labels and patch schedules setup so that they become useful and time-saving tools. A moderate amount of work on the front end will saving a huge amount of time on the back end, particularly if everything is tested to ensure minimal reworking and ongoing maintenance. I have already uncovered and dealt with a number of "gotchas" that required revisions, high levels of maintenance, or an unsatisfactory amount of client machine time, and this guide is intended to help save you from most of that unpleasantness.
________________________________________________________
________________________________________________________

(01) Getting LDAP queries working from the KBOX
________________________________________________________

This took a bit of digging and research to get working, as I'd never used LDAP previously - I knew what the acronym stood for and that was about it. The hardest things I had trouble with were:

1) Which account to use for the LDAP login?
2) What to use for the Search Base DN?
3) How to setup the Search Filter?

One tip if you are also trying to figure out which account to use for the LDAP login - if you are using an enterprise spam filter (appliance or software), the account you need to use may be specified in its config, as these often make use of LDAP queries (that's where I found mine when the IT Director didn't know and I started digging around).

After much trial & error, a call to Kace support, and more trial & error, I finally ended up with a recipe that works. Depending on how you have things setup in your environment, you may decide to tweak accordingly (i.e. not everyone will want to set the root of the domain as the Search Base DN) but these guidelines should help with getting things working and familiar enough to tune to your needs.
_________________

To test LDAP lookups (and base functionality) from the K1000, click on Home > Label > LDAP Browser.

LDAP Server * the IP address of your Domain Controller (used for LDAP lookups)
LDAP Port * probably 389, if standard ports are used in your environment
LDAP Login * the account you use for LDAP lookups (i.e. ldap@company.com)
LDAP Password * the password for the account specified in LDAP Login

Hopefully when you hit Test, you get a green "Connected" - if not, double-check your server and account name & password. Once you do get a green "Connected", hit the Next button and you are ready for an LDAP query.

Search Base DN: DC=CompanyName,DC=Com
Search Filter: (description=Pittsburgh*)

For the Search Base DN, I target the root of the domain since I tag and query my computer accounts' description fields in Active Directory Users and Computers (ADUC) as documented below. If you want to target specific OUs, you would want to list them here (i.e. add OU=Sales, etc). I started with targeting OUs, but found adding search criteria to the description fields works very well for my purposes.

For the Search Filter, specify something present in the ADUC computer or user accounts you can search on. For example, location is the very first thing I've listed in the description field for all of my computer accounts, so by specifying a location name (i.e. Pittsburgh* - note the wildcard character), I can pull up everything with that name in the description field. For full examples of what I use in my computer description fields, skip ahead to part 3. Whatever you decide to use, document your results and what you used for your search filters, as later on you'll use these when building your LDAP labels.
________________________________________________________
________________________________________________________

(02) Planning LDAP Labels and ADUC account description tags
________________________________________________________

This part can be fairly time consuming depending on what you already have setup in ADUC. There was not much setup in my environment, so populating things was handled in two different ways:

1) User accounts - used Dovestone AD Bulk Users to import a spreadsheet with all necessary info
2) Computer accounts - documented in a spreadsheet, then lots of copy & paste into the Description fields

The main thing is to plan ahead with which fields you will be using (if you aren't already) and what you will need to add to make sure the LDAP labels will meet your needs. I'm currently using LDAP labels for the following:

User location (i.e. Pittsburgh, Remote)
User department (i.e. sales, hr)
User category (i.e. restricted, test)

Machine location (i.e. Pittsburgh, Remote)
Machine type (i.e. laptop, desktop, server)
Machine language (i.e. french)
Machine category (i.e. restricted, virtual, workgroup)

Machine role & OS for patching (i.e. server - 2K3sp2, roaming - Win7sp1x64)

Note that where there's overlap of user and machine label names, I specify type in the actual LDAP label name (i.e. Pittsburgh computers, Pittsburgh users). You can't have two LDAP labels with the same name, even if the types are different. Also, you probably noticed some overlap between Machine type and patching; for type I focus on a specific (*server*) and for patching I focus on a string (*server - 2K3sp2*) - in other words, kill two birds with one stone. It all depends on what you target in your LDAP queries and there are a lot of possibilities for multi-purpose (i.e. multi-label) descriptions, as I'll touch upon later.

One goal is to make certain queries as quick as possible - how many laptops? how many French language PCs? In Inventory - Computers, I select View by - label - french and see I currently have 12 French language computers. Combine this with the Advanced Search and it becomes a very powerful tool. For example, if I select the Advanced Search tab, specify "Label Names = Pittsburgh computers" and "Label Names = laptop", I can quickly determine how many laptops I have in my Pittsburgh site. It should also be mentioned that some things (like machine type) could be setup with a Smart Label (i.e. laptop = system models), but I personally prefer setting up certain things in ADUC as I deal with computer accounts every time there's a computer add/change anyways, and it's a quick reference for others who may still use ADUC more than the K1000 (I *still* do from time to time - call it habit).

Another goal is to facilitate efficient patching groups. The best practices guide for K1000 patching says to separate desktops & laptops and OS & applications. Other considerations include what level of patching you will include - i.e. all patches, only critical or somewhere in between. This is definitely prudent advice, as just hitting the subscription lists for your supported OSes and creating a broad "patch all" will result in a scan of thousands of patches on a machine - something that will take hours just to complete the detect phase, and most of which will not be applicable (i.e. scanning for Server 2008 patches on a Windows XP machine).
________________________________________________________
________________________________________________________

(03) Tagging computer (and user) accounts in ADUC
________________________________________________________

Here's the fun "list all of the variations in a spreadsheet and then copy & paste" step I mentioned above. On the bright side, once all of the existing computer accounts have been updated, maintenance is very light and only needs done when new machines are added.

Be sure to double-check (even triple-check) the descriptions once updated. One method I found to be very helpful for verifying that the location was tagged correctly in ADUC (I use location since my OUs are based on site location) was to run an LDAP query from the K1000 (as in step 1) against a specified location name and then cross-check the number of hits with the number of computers listed in the corresponding OU. Another method for objects such as OS version or machine type is to use the Advanced Search in the Inventory - Computers screen to search for like items and see if the numbers match up with the LDAP queries.

Examples of ADUC computer account descriptions

JDOE Pittsburgh (desktop) (stationary - XPsp3)
JSMITH Pittsburgh (laptop) (roaming - 7sp1x64) (sales)
CFO Pittsburgh (laptop) (roaming.nr - XPsp3) *nr denotes "no reboot after patching"
PGH-KILN Pittsburgh (desktop) (control - XPsp3)
PGH-DC Pittsburgh (primary DC) (server - 2K3sp2)
PGH-BACKUP Pittsburgh (backup) (server - 2K8sp2)

I should point out the "roaming/stationary/control/server - OS" description items are for use with the patching system. I will get into this further below when discussing setting up granular, efficient and (largely) automated patch schedules, but for time being let me explain that for patching I have broken up my systems into four main categories:

stationary - all desktops at sites with dedicated network connections (MPLS, IPSec VPN tunnels)
roaming - all laptops, remote desktops that only connect to network via VPN clients (i.e. non-24/7)
control - all sensitive computers that can't (or shouldn't be) patched & rebooted automatically
server - all servers

The reason for doing this is because I try to run as many patching tasks at night as possible to avoid bandwidth issues during the day. Stationary machines are left running 24/7 and are scanned and patched at night, when most systems are idle. I target roaming computers during business hours, since they are frequently off (or off the network) at night - these are done with a two-fold approach, scan one day and deploy another. Servers and control PCs are only submitted to patch scans (not deploys), with servers being scanned on the weekends so any potential hangs on clients scans don't preclude server scans.

In addition to this breakdown, I have defined the OS version so patch scans only target the applicable OS (i.e. so XPsp3en machines are only scanned for WinXP SP3 English patches).

Hopefully this all will become clearer from step 6 as I go into further detail on patching.

________________________________________________________
________________________________________________________

(04) Creating LDAP Labels on the K1000
________________________________________________________

First, create your "parent" label. All LDAP (and smart) labels use these to contain the actual LDAP (or smart) label query.

From the K1000, select Home > Label > Label Management > Choose Action > Add New Label

All you really need to define are the following:

Name * how the label will appear in the K1000
Restrict Label Usage To * Computer Inventory for machines, Users for users
_________________

Second, create your LDAP Label. This will reference the "parent" label and contain your LDAP query (similar to what you used in the LDAP query in step 1, but with required additions).

From the K1000, select Home > Label > LDAP Labels > Choose Action > Add New Item

Enabled * check this box if you want it to work
Filter Type Machine
Associated Label Name * whatever you named your "parent" label (i.e. Pittsburgh computers), see Note 1
Server Hostname * the IP address of your Domain Controller (used for LDAP lookups)
LDAP Port Number * probably 389, if standard
Search Base DN DC=CompanyName,DC=Com *you can drill down to OUs here if necessary
Search Filter (&(description=Pittsburgh*)(name=KBOX_COMPUTER_NAME)) * see Notes 2 & 3
LDAP Login * the account you use for LDAP lookups
LDAP Password * the password for the account specified in LDAP Login
_________________

Labels won't show up in the Associated Label Name list if already assigned to something (i.e. a smart label or another LDAP label).

For the LDAP labels, I use the wildcard (*) character (once or twice depending on where the target string is located - see step 5 for examples) to include any accounts that contain the specified string in the account's Description field (in ADUC).

You'll notice below that (name=KBOX_COMPUTER_NAME) is added to machine search filters and (samaccountname=KBOX_USER_NAME) to user search filters. These are necessary in order for the LDAP labels to work (i.e. get added to the appropriate machines and users in the K1000), however if you include these as listed below while doing a LDAP query (as in step 1), there will be no results. Just be aware of this and you'll be fine.

You can save time creating LDAP Labels by using the Duplicate button in the LDAP Label : Edit Detail screen. This will preserve everything, so all you will have to do is check the Enabled box, change the Associated Label Name and update the Search Filter when creating additional LDAP labels. Just make sure you have built out the "parent" labels beforehand so they will be available in the Associated Label Name field.
_________________

Example LDAP Label (machine)
_________________

Filter Type: Machine
Associated Label Name: Pittsburgh computers
Server Hostname: 192.168.1.20 *IP address of your Domain Controller
LDAP Port Number: 389
Search Base DN: DC=CompanyName,DC=Com
Search Filter: (&(description=Pittsburgh*)(name=KBOX_COMPUTER_NAME))
LDAP Login: ldapadminaccount@companyname.com

* note that this label requires that "Pittsburgh" be the first item in the Description field of the targeted computer account in ADUC.
____________________

Example LDAP Label (user)
____________________

Filter Type: User
Associated Label Name: sales
Server Hostname: 192.168.1.20 *IP address of your Domain Controller
LDAP Port Number: 389
Search Base DN: DC=CompanyName,DC=Com
Search Filter: (&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(department=Sales))
LDAP Login: ldapadminaccount@companyname.com

* note that this label requires that "Sales" be specified in the Department field of the targeted user account in ADUC.
_________________

Other variations:

I included the following as I have two different locations that have the same name for the first part (Warren, Warren Warehouse). I used the following search filters for each (although the converse would also work):

Warren
(&(&(description=Warren*)(!(description=Warren Warehouse*)))(name=KBOX_COMPUTER_NAME))

Warren Warehouse
(&(description=Warren Warehouse*)(name=KBOX_COMPUTER_NAME))

I found that including certain other ADUC criteria to be helpful in limiting results when they were too wide during the initial LDAP lookup. In particular, some queries may pull up items that are not computer or user accounts, so using things that are *unique* to computer or user accounts can help with filtering these out. The examples below filter using "homeDirectory" and "givenName" items that are populated only in user accounts.

restricted
(&(&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(description=*restricted*))(homeDirectory=*))

industrial
(&(&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(description=*industrial*))(givenName=*))
________________________________________________________
________________________________________________________

(05) Patching-specific LDAP labels (examples)
________________________________________________________

In my environment, I have quite the mix of Windows versions to keep patched. Since one of my goals is to keep the amount of machine time spent detecting patches to a minimum, I've created very granular (targeted) groups to ensure that extraneous OS patches are not included in the detect patch list. I have also tried to match the K1000's OS labels as much as possible (with a few slight differences) to make the LDAP queries as simple as possible. For example, if I had left the OS name as 2K3sp2x64 in the ADUC description, it would overlap with the LDAP query of 2K3sp2 and would require an extra statement in the 2K3sp2 query to avoid including the 2K3sp2x64 machines. This is ultimately a matter of personal preference, as either approach works fine (i.e. delineate in the ADUC description or write a more elaborate LDAP statement - personally, I'm a fan of simplicity).
____________________

Machine category guidelines

As discussed earlier, aside from flavors of OS, I also have my machines further broken down into groups that reflect the different patching tasks for each. Part of this follows the Kace patching best practices guide (i.e. patching laptops) and the rest follow guidelines set in my environment. I won't imagine that everyone will have this much diversity, but hopefully this will give you an example of how to handle it should the need arise.

stationary - "detect & deploy" in one task, at night
roaming - detect in one task, deploy in a separate task
control - "detect only" in one task, at night
server - "detect only" in one task, run separately from other tasks for quickest completion
____________________

ADUC computer account descriptions (for patching)

I should note here that although I had separated critical servers using a separate label (in the previous version of this post), I'm only using the server smart labels for Detect patch schedules. Deploy runs for servers are handled with separate Deploy patch schedules.

control - 2Ksp4
control - XPsp3
control - 7sp1x64

roaming - WinXPsp3
roaming - Win7sp1x64

roaming.nr - WinXPsp3
roaming.nr - Win7sp1x64

server - 2Ksp4
server - 2K3sp2
server - 2K3.sp2x64
server - 2K8sp2
server - 2K8.sp2x64
server - 2K8.r2sp1x64

stationary - WinXPsp3
stationary - Win7sp1x64
___________________________________

LDAP Machine Labels (for patching)

These are the LDAP machine labels that correspond to the ADUC computer account descriptions above - note how the description query target matches. I use the wildcard character (*) before and after to ensure anything before and after gets ignored.

patch (stationary - XPsp3)
(&(&(description=*stationary - XPsp3*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (stationary - 7sp1x64)
(&(&(description=*stationary - 7sp1x64*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (roaming - XPsp3)
(&(&(description=*roaming - XPsp3*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (roaming.nr - XPsp3)
(&(&(description=*roaming.nr - XPsp3*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (roaming - 7sp1x64)
(&(&(description=*roaming - 7sp1x64*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (roaming.nr - 7sp1x64)
(&(&(description=*roaming.nr - 7sp1x64*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (control - 2Ksp4)
(&(&(description=*control - 2Ksp4*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (control - XPsp3)
(&(&(description=*control - XPsp3*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (control - 7sp1x64)
(&(&(description=*control - 7sp1x64*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (server - 2Ksp4)
(&(&(description=*server - 2Ksp4*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (server - 2K3sp2)
(&(&(description=*server - 2K3sp2*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (server - 2K3.sp2x64)
(&(&(description=*server - 2K3.sp2x64*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (server - 2K8sp2)
(&(&(description=*server - 2K8sp2*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (server - 2K8.sp2x64)
(&(&(description=*server - 2K8.sp2x64*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

patch (server - 2K8.r2sp1x64)
(&(&(description=*server - 2K8.r2sp1x64*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
________________________________________________________
________________________________________________________

(06) Applying LDAP labels
________________________________________________________

For machine LDAP labels to be applied to the Inventory > Computer items, the machine will need to check into the K1000 (i.e. run inventory). Hopefully your machines are already regularly checking into the K1000 as scheduled, so this should take care of itself given time. In my environment, a weekend was sufficient to get enough machines using LDAP labels to start utilizing them. If you want to see results sooner, you can use the K1000 Agent Inventory & Check-In script under Scripting - Scripts (just heed the warning about not running this on 50+ machines in one go).

For user LDAP labels to be applied to the Helpdesk - Users items, the user will need to log into the K1000. At this point I haven't rolled out the Helpdesk (Service Desk) portal to users, so it's pretty bare. But I have tested the functionality and it works like a charm (and will probably be very useful once the users are labeled...).
________________________________________________________
________________________________________________________

(07) Setting up patch subscriptions
________________________________________________________

The following may already have been done during your JumpStart, but I want to include it just for the sake of being comprehensive.

The first step is to make sure that the appropriate patch download settings are in place. To do this, from the K1000 click on Settings - Control Panel - Patch Settings. Here you can specify how often you want the K1000 to download patches and how you want to handle patch downloads. For point of reference, I have mine set to download every day at 1AM, with "allow download of patch definitions to complete" selected and Offline Update Options not enabled. These last two options will depend on how tightly you control bandwidth in your environment and whether or not your K1000 has Internet access.

The next step is to make sure the appropriate patch subscriptions are in place, so click on Security - Patching - Subscription Settings. Here is where you need to specify which OS subscription feeds you want to use, and (in case it's not obvious) these include OS and application patches for each OS (just be sure to check the box for Download Application Patches to enable them).

One tip - If you aren't positive about what you have in your environment, you can use the Reporting wizard to create a quick report to find out (assuming your K1000 agents are already deployed). Click on Reporting > Choose Action > Add New SQL Report > fill out the fields as follows > click Save:

*Report Title*
Windows Installs (with Count)

*Report Category*
Software (Custom)

*Description*
Lists all versions of Windows and corresponding machines with total install count.

*SQL Select Statement*
SELECT OS_NAME, SERVICE_PACK, OS_VERSION, COUNT(MACHINE.ID) AS 'Total',
GROUP_CONCAT(DISTINCT MACHINE.NAME ORDER BY 1 SEPARATOR '\n') AS 'Machines'
FROM MACHINE
GROUP BY OS_NAME
ORDER BY OS_VERSION

Run the report by clicking on the corresponding HTML, CSV or TXT button, and you should have your list.

In my environment (corresponding to what I have listed previously for ADUC and LDAP labels), I have the following in my patch subscription OS List:

Win XP SP3
Win7 SP1 x64
Win 2K SP4
Win 2K3 SP2
Win 2k3 SP2 x64
Win 2K8 SP2
Win 2K8 SP2 x64
Win 2K8.R2 SP1 x64

For point of reference, I have Limit Patch Download to Selected Labels setup with the following smart labels. These will be discussed further in the next section.

patch-apps
patch-os-critical-2k3sp2
patch-os-critical-2k3sp2x64
patch-os-critical-2k8r2sp1x64
patch-os-critical-2k8sp2
patch-os-critical-2k8sp2x64
patch-os-critical-2ksp4
patch-os-critical-7sp1x64
patch-os-critical-xpsp3

I also have enabled "Automatically Inactivate Superseded Patches" under Default Patch Status. By all means, choose what is right for your environment - for example, you may want to select "Automatically Inactivate All New Patches" if you have a routine and test machines in place for reviewing and testing patches before they are rolled out.
________________________________________________________
________________________________________________________

(08) Creating smart labels for patches
________________________________________________________

Smart labels are extremely useful for specifying (1) exactly which patches will be downloaded and (2) which patches will be used during the detect & deploy phases of patching, and the (2) labels can be the same (as I do for OS patching) or a subset of (1) for more targeted patch runs (as I do for app patching). To create smart labels for patches, click on Security > Patching > Patch Listing and then select the Create Smart Label tab on the right.

In my environment, I only address critical patches so Impact is one criteria to consider. Another filter that will greatly reduce the number of "garbage" patches you download is Superseded. The patching best practices guide suggests using the Patch Type filter (OS & Application). As I mentioned during the preface, I also no longer use the Release Date filter, as I want to make sure my patches will cover any old applications or supporting packages that get installed.

Case in point on number of patches included based on criteria:

XP SP3 (OS & App) 4,855 patches
XP SP3 (OS only) 316 patches
XP SP3 (OS only, Critical) 305 patches
XP SP3 (OS only, Critical, Non-Superseded) 169 patches
____________________

OS Patch Smart Labels

Here are the criteria I use for setting up my OS patch smart labels (as opposed to App patch smart labels, which are covered in detail next) and the smart labels themselves. These are very straightforward and the only criteria that changes for each label is the Operating System.

-Patch Type = OS
-Impact = Critical
-Superseded = No
-Operating System = specify OS

patch-os-critical-xpsp3
patch-os-critical-7sp1x64
patch-os-critical-2ksp4
patch-os-critical-2k3sp2
patch-os-critical-2k3sp2x64
patch-os-critical-2k8sp2
patch-os-critical-2k8sp2x64
patch-os-critical-2k8r2sp1x64
____________________

App Patch Smart Labels

Note - I don't suggest you use this setup "as is" in your own environment, as your applications will not be the same as mine. However, hopefully my approach can give you some ideas and/or a structure for work with, which should make things proceed more smoothly.

I tried a variety of approaches for app patches and the only workable approach I found (at least for my K1100, with *only* 225GB of main disk space available) is a multi-layered, "blacklisting" smart label approach. I take the exclusion approach since the patch names vary greatly and it's fairly easy to test for what I'm excluding (compared to what I might be missing with the alternate approach).

For the Patch Subscription Settings screen, I use a "parent" label (patch-apps) that is made up of four "children" labels (patch-apps-main, patch-apps-regex, patch-apps-adobe, patch-apps-id-title) that actually specify the exclusions. For the patch schedules (Security > Patching > Detect and Deploy Patches), I use another set of higher order "parent labels" (patch-apps-client, patch-apps-server) that are made up of the patch-apps label (a parent itself) and labels that exclude non-client and non-server application patches respectively. Keep reading and I promise what you just read will make sense.

To determine which patches I needed to exclude, I started with the patch-apps-main label as a baseline (using Patch Type = Application, Impact = Critical & Superseded = No criteria). This left me with ~2,400 patches to go through and figure out what to remove, which the subsequent three "children" labels (patch-apps-regex, patch-apps-adobe, patch-apps-id-title) address. This was just a (slightly tedious) exercise of exporting the patch-apps-main patch list to Excel, sorting by title, making a list of what I knew wasn't in my environment, and cross-checking software I wasn't 100% certain of against the Inventory > Software list. I'm aware that some things I may not have technically needed to filter out (like different languages, Apple-specific apps, etc), but personally I'd rather err on the side of caution and know that these are absolutely not going to be downloaded.

The REGEX statements in the "children" labels are fairly abbreviated (especially in the patch-apps-regex label) so that I can get the most mileage out of them (there is a character limit to REGEX statements at ~256). For example, "roov" is short for "Microsoft Office Groove Server" (something that's not in my environment), and running a "Title matches REGEX roov" search against the Patch Listing will confirm this. In case any of my REGEX abbreviations aren't clear, use this approach to clarify - or to create your own abbreviations, just try to find unique strings of characters and run a "matches REGEX" search. To filter these out, just use "Title does not match REGEX" as one the smart label's criteria.

One important note on the REGEX filters - they do not handle preceding parenthesis characters well, which means the K1000 will just go to a blank white screen when the smart label is tested or will not return desired results. For these, I used individual "Title does not contain _____" statements (as in the patch-apps-id-title label).

Finally, here is the KACE Support note that led to my approach below. Pay particular notice to the comment about changing the order.

"If you run out of criteria fields, you can always create an additional patch label and reference that label by using AND Label Names = criteria. Note that if you are using a patch label within a label, the order should be changed so that the dependency label is processed first. This can be accomplished by going to "Home" > Label > Smart Labels > Choose Action and select Order Patch Smart Labels. Change the main label to an order number higher that the dependent patch label."

For example, patch-apps-main has an order of 10, while patch-apps (which contains patch-apps-main) has an order of 60.

1) patch-apps-main
* high-level patch filters
* order = 10

-Patch Type = Application *and*
-Impact = Critical *and*
-Superseded = No

2) patch-apps-regex
* misc REGEX exclusions
* order = 20

-Title does not match REGEX
sve|ptb|zhh|nld|jap|portu|czec|poli|norw|danis|wedi|finn|dutc|spani|itali|germ|chines|mac|os x|lion|leop|tiger|panth|iwor|iphot|expressi|gara|trend|mcafe|syma|foref|vmwa|hotos|llus|indes|accoun|forms|lync|tablet|sual s|ver 2010|isa s|isa) s|harep|roov

*and*

-Title does not match REGEX
2526299|2553006|amw|CS3|scap|novell|KB968012|KB2553194|KB2708980|KB2680317|KB2579598|MS11-082|io 2002|io 2003|h) Visi|tvp|virt|ge 2000|ge 2003|ge server 2003|KB957324|ce xp|integr|em servers|2002|rer 5.01|ect 200|ote 200|ms07-018|interc|th 2003|ect mu

*and*

-Title does not match REGEX
ing ess|kore|l for Windows V|ks sui|em cen|l server 7|en-za|en-gb|nch) microsoft sql|nch) microsoft vis|nch) office 2007|ple rem|itunes 8|itunes 9

3) patch-apps-adobe
* old Adobe Reader/Pro & Flash exclusions
* order = 30

-Identifier does not match REGEX
APSB|AdbeRdr91|ReaderWin8.1.3

*and*

Title does not match REGEX
l 8.2|r 8.2|l 8.3|r 8.3|l 9.0|r 9.0|l 9.1|r 9.1|l 9.3|r 9.3|3D

4) patch-apps-id-title
* Apple, Firefox & non-REGEX-able exclusions
* order = 40

-Identifier does not match REGEX
apple|x1.5.0.7|x10.0.2|x11.0|x2.0.0.20|x3.0.19|x3.5.19|x4.0.1|x5.0|x6.0.2|x7.0|x8.0|x1.5.0.10|x1.5.0.9|x2.0.0.20|xESR10.0.6|xESR10.0.6

*and*

-Title does not contain 952580 (F *and*
-Title does not contain (Vista) *and*
-Title does not contain Windows 2000 (KB978542)

5) patch-apps
* combination of all other app patch smart labels
* must have a higher order number than included labels
* order = 60
* use in Patch Subscription list
* 551 patches as of 2012/07/25

-Label Names = patch-apps-main *and*
-Label Names = patch-apps-regex *and*
-Label Names = patch-apps-adobe *and*
-Label Names = patch-apps-id-title

6) patch-apps-cfilter
* non-client app exclusions
* order = 80

-Title does not match REGEX
exchange|sql server 2005 s|sql server 2000 s|sql server 2008 r|sql server r|search 4

7) patch-apps-client
* combination of patch-apps and client filter labels
* must have a higher order number than included labels
* order = 100
* use in Patch Schedules
* 513 patches as of 2012/07/25

-Label Names = patch-apps *and*
-Label Names = patch-apps-cfilter

8) patch-apps-sfilter
* non-server app exclusions
* Order = 80

-Title does not match REGEX
office 2010|office 2007|excel viewer|visio 2007|visio 2010|project|itunes|firefox|itunes|quicktime|realplayer|safari|winzip

*and*

-Title does not contain (french)

9) patch-apps-server
* combination of patch-apps and server filter labels
* must have a higher order number than included labels
* order = 100
* use in Patch Schedules
* 441 patches as of 2012/07/25

-Label Names = patch-apps *and*
-Label Names = patch-apps-sfilter
___________________________________

Report to Determine Software Installed on Machines by Machine Label

Something else that I found useful when setting up machine-targeted patch smart labels was a report I made that let me see if specific software was installed or not on specific machines. For example, I wanted to know if any of my servers were running Adobe Reader, Java, Firefox, etc so I could potentially exclude them from my patch-apps-server smart label, and used variations of the following report to determine this.

The only prerequisite for this report is to have a machine label to target (as discussed earlier in the LDAP labels section, although this could also be a smart label or manual label - any type will do). I'm using my "server" machine label here in the last part of the WHERE statement (LABEL.NAME = 'server'). To target various software, just specify the name in the REGEX statement like this: (DISPLAY_NAME rlike 'software') or for multiple software, like this: (DISPLAY_NAME rlike 'software1|software2|etc').

As before, to create this SQL Report, click on Reporting > Choose Action > Add New SQL Report > fill out the fields as follows > click Save, then run the report by clicking on the corresponding HTML, CSV or TXT button, and you should have your list.

*Title*
Server Software - Adobe

*Category*
Servers (Custom)

*Description*
Lists all Adobe Acrobat & Reader installs per server

*SQL Select Statement*
SELECT M.NAME AS SYSTEM_NAME, DISPLAY_NAME, DISPLAY_VERSION
FROM SOFTWARE S
LEFT JOIN MACHINE_SOFTWARE_JT MS ON (MS.SOFTWARE_ID = S.ID)
LEFT JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE NOT S.IS_PATCH
AND L.NAME = 'server'
AND DISPLAY_NAME rlike 'adobe reader|acrobat'
ORDER BY SYSTEM_NAME

*Break on Columns*
SYSTEM_NAME
________________________________________________________
________________________________________________________

(09) Creating patch schedules (putting it all together)
________________________________________________________

Patch schedules bring everything discussed together, so if there's any satisfaction to be had from all of the work done thus far, it's here. To set these up, go into Security > Patching > Detect and Deploy Patches > Choose Action > Add New Item. I feel there's benefits in (1) seeing a completed patch schedule (to help with getting an idea of how everything comes together) and (2) seeing how different options for different groups of machines can be handled, so I'll start with the first and get into the next afterward. Hopefully reviewing the example below and what has been discussed up to this point will prompt a few smiles - I know I was once I saw how everything fell into place.
____________________

Example Patch Schedule configuration

*Schedule Description*
Stationary - WinXPsp3 (Detect & Deploy - OS-Critical)

*Patch Action*
Detect and Deploy

*Machine Selection - Limit Run To Selected Machine Labels*
patch (stationary - XPsp3)

*Limit Run To Machines With Selected Operating Systems*
Win XP SP3

*Detect Patch Label Selection - Limit Detect To Selected Patch Labels*
patch-os-critical-xpsp3

*Deploy Patch Label Selection - Limit Patches To Selected Patch Labels*
patch-os-critical-xpsp3

*Max Deploy Attempts*
3

*Alerts*
none used

*Reboot Options - Reboot Mode*
Prompt User

*Reboot Message*
Company IT - Reboot Required for Patching

*Message Timeout*
60 minutes

*Timeout Action*
Reboot now

*Reprompt Interval*
120 minutes

*Patch Schedule*
Run custom - 0 21 * * 1

*Run on next connection if offline (checked)*
Delay Schedule by 15 minutes

*Suspend pending tasks after (720) minutes from scheduled start*
checked
* only used for Stationary machines
____________________

I should mention that I handle certain options differently depending on machine roles and that none of these are set in stone. As circumstances have arisen based on patching success (or failure) and user feedback, I've adjusted accordingly.

The example above is what I have found to be the least intrusive for the stationary computers (patch at night, prompt to reboot, run on next connection, suspend tasks before day shift starts). I also have afternoon and night shift users to take into account, which is why I prompt before rebooting - after patching has been completed. Also, I've found the "suspend pending tasks" option useful for ensuring that machines aren't still grinding away when users come into the office after a scheduled patch night (and yes, before I set this option, I had some early morning users calling in and asking why their machines were so slow or why they were getting reboot prompts).

Per the patching best practices guide, the roaming groups get individual patch schedule for detect and deploy tasks. I found this helped considerably with having the tasks complete, something I'll discuss later when I touch upon troubleshooting patching.

Also, regarding Detect & Deploy vs the Deploy option (something I learned from a support call) - Detect & Deploy = reboot PC and patch continuously until machine 100% patched, which is ideal for builds and overnight deploys, but not working hours patch runs (like on my laptops, as I found out). Deploy = patch & reboot once, then done (a more ideal solution for my roaming systems).

One side note: I found that warning users about impending patching tasks didn't have the intended effect - they found it more annoying to get a pop-up than anything. So away went all warnings except the final reboot warning (which I found was needed, as well as a longer window for users who "couldn't reboot because they have 10 files open that they need to update, save and close" - trust me, I can relate).
____________________

Patch Schedule Times

Here are examples of how I have my patch times scheduled for different machine types. If you click on the little yellow Help button icon next to "Run custom", you'll get the full syntax explanation and usage examples. I personally prefer to run a scan once a week, as there's still a lot of variation as to when patches get released.

stationary (OS detect & deploy)
0 21 * * 1 (9:00PM every Monday)

stationary (App detect & deploy)
0 21 * * 4 (9:00PM every Thursday)

roaming (OS detect)
30 11 * * 1 (11:30AM every Monday)

roaming (OS deploy)
30 11 * * 2 (11:30AM every Tuesday)

roaming (App detect)
30 11 * * 4 (11:30AM every Thursday)

roaming ((App deploy)
30 11 * * 5 (11:30AM every Friday)

control (OS detect)
0 21 * * 5 (9:00PM every Friday)

control (App detect)
0 21 * * 6 (9:00PM every Saturday)

server (OS detect)
0 21 * * 5 (9:00PM every Friday)

server (App detect)
0 21 * * 6 (9:00PM every Saturday)
____________________

Patch Schedules

Once all is said and done, I end up with the following patch schedules. I've found that aside from the patch schedules that directly correspond to the patch smart labels, it's useful having some "general purpose" ones (i.e. the All patch schedules) for detecting & deploying on demand. A little slower than the scheduled ones, but they work just fine.

Also of note - after a very successful testing phase, we are now patching all of our control PCs and servers with the K1000 using targeted patch schedules (which are fine-tuned as necessary). Simply setting up a patch schedule to "deploy all" patches will deploy all of the patches that came up missing during the detect scans. For the most sensitive and critical servers, we manually deploy the missing patches using the Server - Manual Patch Updates (Deploy All) patch schedule so that the processes can be monitored and the machine reviewed after patching (and rebooting) completes.

All (Deploy Only)
All (Detect Only)
All (Force Reboot 1)
All (Force Reboot 2)
All (Force Reboot 3)
All (Prompt Reboot 1)
All (Prompt Reboot 2)
All (Prompt Reboot 3)

Control - Manual Patch Updates (Deploy All)
Control - Win2Ksp4 (Detect - OS-Critical)
Control - WinXPsp3 (Detect - OS-Critical)
Control - Win7sp1x64 (Detect - OS-Critical)
Control - Win2Ksp4/WinXPsp3/Win7sp1x64 (Detect - App-Critical)

Roaming - Win7sp1x64 (Deploy - OS-Critical)
Roaming - Win7sp1x64 (Deploy.NR - OS-Critical)
Roaming - Win7sp1x64 (Detect - OS-Critical)
Roaming - WinXPsp3 (Deploy - OS-Critical)
Roaming - WinXPsp3 (Deploy.NR - OS-Critical)
Roaming - WinXPsp3 (Detect - OS-Critical)
Roaming - WinXPsp3/Win7sp1x64 (Deploy - App-Critical)
Roaming - WinXPsp3/Win7sp1x64 (Deploy.NR - App-Critical)
Roaming - WinXPsp3/Win7sp1x64 (Detect - App-Critical)

Server - Branch Sites (Deploy All)
Server - Manual Patch Updates (Deploy All)
Server - Wednesday 6AM (Deploy All)
Server - Weekday Night (Deploy All)
Server - Weekend Night (Deploy All)
Server - Win2K3sp2 (Detect - OS-Critical)
Server - Win2K3sp2x64 (Detect - OS-Critical)
Server - Win2K8r2sp1x64 (Detect - OS-Critical)
Server - Win2K8sp2 (Detect - OS-Critical)
Server - Win2K8sp2x64 (Detect - OS-Critical)
Server - Win2Ksp4 (Detect - OS-Critical)
Server - Win2Ksp4/Win2K3sp2/x64/Win2k8sp2/x64/win2k8r2sp1x64 (Detect - App-Critical)

Stationary - Win7sp1x64 (Detect & Deploy - OS-Critical)
Stationary - WinXPsp3 (Detect & Deploy - OS-Critical)
Stationary - WinXPsp3/Win7sp1x64 (Detect & Deploy - App-Critical)
____________________

Patch Schedule Notes:

The All... patch schedules are used for manually targeting machines and are OS agnostic.

The Roaming - XPsp3 (Detect...) patch schedules include the following machine labels:

patch (roaming - XPsp3)
patch (roaming.nr - XPsp3)

Similarly, the Roaming - 7sp1x64 (Detect...) patch schedules include the following machine labels:

patch (roaming - 7sp1x64)
patch (roaming.nr - 7sp1x64)

The Server (Deploy All) patch schedules are manually populated with machines and have schedules set or manually run by the server administrator.

________________________________________________________
________________________________________________________

(10) Troubleshooting patching and other footnotes
________________________________________________________

One thing I have learned about patching from the K1000 - give it time and give clients a few runs before digging too hard into the results (which can be cryptic at times). I uncovered quite a few different error codes (FAIL 18, FAIL 80, FAIL 98, FAIL 206) after my first patch run and didn't get much help from KBs or support for some, but found that as the days went by the errors typically resolved themselves (that is, the ones that didn't have good explanations, like the machine being off the network) and patches were installed. Also, if you have more than one site, I strongly encourage using replication shares. In addition to the troubleshooting notes below, here are some related articles which can help get your patching near 100% completion rates:

Tracking Processes on Machines during Patching, Managed Installs & Scripts (w/utility scripts)
http://www.itninja.com/blog/view/tracking-processes-on-machines-during-patching-managed-installs-scripts-w-utility-scripts

Patching via K1000 Slow? Lots of Errors/Failures? Try Replication Shares
http://www.itninja.com/blog/view/patching-via-k1000-slow-lots-of-errors-failures-try-replication-shares

Determining If Patches Pulled From Replication Share or K1000
http://www.itninja.com/blog/view/determining-if-patches-pulled-from-replication-share-or-k1000

K1000 Report to List Free Hard Drive Space on Replication Share Machines' Targeted Drives
http://www.itninja.com/blog/view/k1000-report-to-list-free-hard-drive-space-on-replication-share-machines-targeted-drives

____________________

Resetting patch retries

One typical situation to be aware of is needing to reset patch retries, as after 3 failed attempts (or whatever the retry count is set at) the K1000 will not try to deploy a patch to the same machine again. Unfortunately there is currently no global "reset all patches for all machines option" (although there is a UserVoice item for it - http://kace.uservoice.com/forums/82699-k1000/suggestions/2224904-global-reset-patch-tries-for-all-computers), but there are two ways currently of resetting patch retries:

1) Reset patch retries for all patches for 1 machine:

Inventory > Computers > click on target machine > click on Patching Detect/Deploy Status (under Security) > click the Reset Patch Tries button

2) Reset patch retries for all machines for 1 patch (confirmed by Kace support):

Security > Patching > Patch Listing > click on target patch > click the Save button
____________________

Patching task troubleshooting (non-completion)

During another support call, I learned about a couple of good places to check for patching (and other) tasks building up in queue and preventing other tasks from running. A good indicator might be patch tasks not running *and* agents not running inventory or syncing on schedule.

Settings > Support > Troubleshooting Tools > K1000 Agent Messaging

1) K1000 Agent Tasks - there should just be a handful of In Progress tasks, more and it could indicate a problem

2) AMP message queue - same for this one

One thing to be aware of is the ability to delete the tasks as a temporary measure, but this is more like treating the symptoms of a disease rather than curing it. That being said, it could assist with pinpointing just where the holdup is coming from, particularly if there are hundreds of tasks listed. If everything starts hanging after a certain scripting task or with the same group of PCs, that would probably be a good place to start looking.
____________________

Patching task troubleshooting (sluggish patching due to AV conflicts)

Another thing to be aware of if patching seems to be dragging is possible conflicts with the antivirus program. I found that my PC's AV process would spike the CPU hard during the entire patching task, which caused everything to drag out unsatisfactorily. Fortunately, a few exclusions and patching was back to a normal pace (with only a brief AV process spike). For anyone else who might find themselves in this situation, here's the exclusions I did:

XP
C:\Documents and Settings\All Users\Dell\KACE\*.*
C:\Program Files\Dell\KACE\*.*

Win7
C:\Program Files (x86)\Dell\KACE\*.*
C:\ProgramData\Dell\KACE\*.*

I also excluded the IP address of my KBOX in the AV's Web access protection section.
________________________________________________________
________________________________________________________

(11) SQL Reports
________________________________________________________

As the patch schedules tied all of the previous LDAP and label work together, so the SQL reports do the same for the patch results. The main problem with the reports (in my environment, at least) was that the out-of-the-box reports didn't give me what I needed (particularly for patching), so I had to find another way - and the answer is SQL reports. I'll be honest and admit that I had absolutely zero SQL experience/exposure prior to the K1000, but fortunately there are a lot of good examples out there (in KB articles and on AppDeploy) and probably one of the most useful tools for learning is the reporting wizard itself.

If an out-of-the-box or wizard-created report isn't giving you exactly what you need, chances are you can find other reports that come close with the exception of the variable names or JOIN/WHERE statements - and you can get these from the SQL view of the reports. To get to this, select any report from the Reporting - Reports screen (or build your own using whatever categories and/or criteria you need code for), and (if the SQL code isn't listed by default) click on the Edit SQL link in the Define a New Report screen.

Despite appearances (and feel free to correct me here if I'm wrong, since I'm no guru and still have plenty of trouble), the report statements are generally straightforward enough to figure out what can be cut-and-pasted into other reports (with a little testing and tweaking, of course). I'll try to break one of my reports down so hopefully the sections make a little more sense. Again, feel free to correct me on any point - I have a feel for this stuff, but am in no way an expert. At the same time, if I can give hope and/or direction to others who have no experience that they can create working SQL reports without needing to be DBAs, then mission accomplished.

The SELECT statement defines the "columns" (or topics, if you will) and how they will be displayed in the report's column headers (i.e. PP.TITLE AS "Patch Name"). This can include quite a few variables (or columns) and concludes with the FROM statement, which states which part of the K1000's database those variables are being called (i.e. PATCHLINK_MACHINE_STATUS). JOIN statements allow you to pull info from other tables and rely on a common column. I used to just look at other reports to figure out what to use, but have found the MySQL Query Browser to be an excellent tool for building/testing queries. The WHERE statement is the place most of the "code tricks" happen, and I've found the copy-someone-else's-work-and-tweak method to work very well here. There can be a whole string of AND statements following the initial WHERE statement that further refine what is included in the results. ORDER BY simply says how to arrange the results. I'll also add that in some reports, there are no JOIN or WHERE or ORDER BY statements, it pretty much depends on what is being reported - but hopefully that helps a bit. SQL gurus are probably shaking their heads right now, but so far this level of comprehension has served me well enough to get the job done.

If you would like a complete breakdown of a SQL Report, please see this post:

Primer for Writing SQL Select Statement Queries on the K1000 (w/Custom SQL Report example)
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

Anyways, on to the reports I've cobbled together that *do* give me the information I need - (1) missing (2) active (3) critical patches for specified machines (reports are paired, one sorted by patch, one sorted by machine name):
____________________

*Title*
All missing active critical patches (patch)

*Report Category*
Patching (Custom)

*Description*
Lists missing active critical patches for all computers, sorted by patch

*SQL Select Statement*
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY PP.TITLE, M.NAME

*Break on Columns*
PATCH_NAME
____________________

*Title*
All missing active critical patches (PC)

*Report Category*
Patching (Custom)

*Description*
Lists missing active critical patches for all computers, sorted by PC

*SQL Select Statement*
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE

*Break on Columns*
COMPUTER_NAME
____________________

Server Patch Reports

Note that the server reports may need tweaking on your end, as the first WHERE statement uses my "server" machine label. If you want to use this, just replace with your own machine label name.
____________________

*Title*
Server missing active critical patches (patch)

*Report Category*
Patching (Custom)

*Description*
Lists missing active critical patches for all computers in server machine label, sorted by patch

*SQL Select Statement*
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE L.NAME = 'server'
AND MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY PP.TITLE, M.NAME

*Break on Columns*
PATCH_NAME
____________________

*Title*
Server missing active critical patches (PC)

*Report Category*
Patching (Custom)

*Description*
Lists missing active critical patches for all computers in server machine label, sorted by PC

*SQL Select Statement*
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE L.NAME = 'server'
AND MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE

*Break on Columns*
COMPUTER_NAME
____________________

Client Patch Reports

The client machine reports take a different approach by using additional SELECT statements to exclude the control and server machine labels, which results in querying the remaining "stationary" and "roaming" machine labels. I initially created this when I had multiple server labels and was using the report wizard to help with the syntax for label exclusion, but the same result could also be accomplished by using a REGEX statement in the server reports above to match multiple label names (i.e edit the server reports and replace (L.NAME = 'server') with (L.NAME rlike 'stationary|roaming'). That being said, the existing approach works fine and should give you an idea of how you can handle excluding multiple labels.
____________________

*Title*
Client missing active critical patches (patch)

*Report Category*
Patching (Custom)

*Description*
Lists missing active critical patches for all computers in roaming and stationary machine labels, sorted by patch

*SQL Select Statement*
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE
(1 NOT in (select 1 from LABEL L, MACHINE_LABEL_JT ML
where M.ID = ML.MACHINE_ID AND ML.LABEL_ID = L.ID
AND L.TYPE <> 'hidden' and L.NAME = 'control'))
AND (1 NOT in (select 1 from LABEL L, MACHINE_LABEL_JT ML
where M.ID = ML.MACHINE_ID AND ML.LABEL_ID = L.ID
AND L.TYPE <> 'hidden' and L.NAME = 'server'))
AND MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY PP.TITLE, M.NAME

*Break on Columns*
PATCH_NAME
____________________

*Title*
Client missing active critical patches (PC)

*Report Category*
Patching (Custom)

*Description*
Lists missing active critical patches for all computers in roaming and stationary machine labels, sorted by PC

*SQL Select Statement*
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE
(1 NOT in (select 1 from LABEL L, MACHINE_LABEL_JT ML
where M.ID = ML.MACHINE_ID AND ML.LABEL_ID = L.ID
AND L.TYPE <> 'hidden' and L.NAME = 'control'))
AND (1 NOT in (select 1 from LABEL L, MACHINE_LABEL_JT ML
where M.ID = ML.MACHINE_ID AND ML.LABEL_ID = L.ID
AND L.TYPE <> 'hidden' and L.NAME = 'server'))
AND MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE

*Break on Columns*
COMPUTER_NAME
____________________

Patch Deployment Failure Reports

These reports list patches that failed to deploy, based on machine labels. Can be useful in determining if a certain patch may be having issues deploying. As with the others, adjust the L.NAME as desired. The server & control report works using machine label name inclusion (via a REGEX statement - L.NAME rlike 'server|control') and the client report works using machine label name exclusion (via additional SELECT statements).
____________________

*Title*
Server & control failed active critical patches (patch)

*Report Category*
Patching (Custom)

*Description*
Lists active critical patches that failed to install for all computers in server and control machine labels, sorted by patch

*SQL Select Statement*
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'server|control'
AND ((MS.DEPLOY_ATTEMPT_COUNT >= MS.MAX_DEPLOY_ATTEMPT and MS.STATUS != 'PATCHED')
or MS.STATUS = 'FAIL' or (MS.DEPLOY_STATUS = 'FAIL')AND MS.STATUS = 'NOTPATCHED'))
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY PP.RELEASEDATE, PP.TITLE, M.NAME

*Break on Columns*
PATCH_NAME
____________________

*Title*
Client failed active critical patches (patch)

*Report Category*
Patching (Custom)

*Description*
Lists active critical patches that failed to install for all computers in roaming and stationary machine labels, sorted by patch

*SQL Select Statement*
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE
((1 NOT in (select 1 from LABEL L, MACHINE_LABEL_JT ML
where M.ID = ML.MACHINE_ID AND ML.LABEL_ID = L.ID
AND L.TYPE <> 'hidden' and L.NAME = 'control'))
AND (1 NOT in (select 1 from LABEL L, MACHINE_LABEL_JT ML
where M.ID = ML.MACHINE_ID AND ML.LABEL_ID = L.ID
AND L.TYPE <> 'hidden' and L.NAME = 'server'))
AND ((MS.DEPLOY_ATTEMPT_COUNT >= MS.MAX_DEPLOY_ATTEMPT and MS.STATUS != 'PATCHED')
or MS.STATUS = 'FAIL' or (MS.DEPLOY_STATUS = 'FAIL')AND MS.STATUS = 'NOTPATCHED'))
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY PP.RELEASEDATE, PP.TITLE, M.NAME

*Break on Columns*
PATCH_NAME
____________________

I initially created all of these reports using the old reporting tool (no longer available in K1200+, from what I understand), however I am using them now with the new reporting tool and they still work fine. The reason I mention this is because if anyone else out there wants to use the old reporting tool for PDF output of these reports, the column widths will be... less than satisfactory. That being said, I found a workaround for fixing the column widths by editing the generated HTML code.

To do this, create the report by populating all of the fields (Title.... SQL Select Statement, etc), make sure the Auto-generate Layout checkbox is checked, then hit Save. This should take you back to the Classic Reports screen - click on the report again and you should see the XML Report Layout (if not, uncheck the Auto-generate Layout checkbox). Scroll about halfway down and do a search on "ColumnHeaderFooter", and you should hit a line similar to this:

<reportElement style="ColumnHeaderFooter" x="0" y="0" width="30" height="15" key="staticText "/>

Starting from here, change the values as in the table below for each of the three instances (there three columns in the reports above - Patch Name, Computer Name, Windows Version). Then do the same for "Detail", which will have the same type of layout. Click Save, run the report and you should be in good shape. Just keep in mind that if you do need to edit the SQL code, you'll need to re-check the Auto-generate Layout checkbox afterward and save the report, which will undo your XML code changes (i.e. you'll need to adjust them again).

Max = 782

(style="ColumnHeaderFooter")
(style="Detail")

x= width=
0 30
30 625
655 127
____________________

Classic reports "bug" (or Easter Egg?) that made this all possible...

One other thing I ran into while playing with the classic reports was a "happy bug" that allowed me to use the wizard to combine tables (i.e. create JOIN statements). If memory serves me correctly, that's how I was able to get the labels included in the above reports as initially they weren't available just doing wizard-based patching reports. The way this bug worked was if I specified everything I wanted in the report via the wizard up to the point of specifying rules (which is where the elaborate WHERE statement using label names came from), and then backed up in the report build process, I could retain what I had selected (i.e. "Label Names" from the Computer topic) and then select a different topic (i.e. the Patching topic). I didn't take this any further than this and obviously didn't notify support, but wanted to let someone know who might be able to find a good use for it. Also, I don't recall if I used the actual <Back button in the Reports wizard screen or the browser's back button, but regardless it worked. Consider that a bonus tip for reading (or skimming) to the end.
____________________

Well, hopefully something there will prove useful to someone. If so, feel free to leave a comment or say "hey" at the next Konference. And as usual, please throw in your $.02 - I'm by no means an expert on anything, this is just want I'm doing in my environment and I know there's *always* room for improvement (as evidenced by the two revisions I've done since originally writing this).

Hope that helps!

John