KACE Product Support

***PLEASE NOTE - THIS HAS BEED SUPERSEDED BY THE FOLLOWING POST***

http://www.itninja.com/blog/view/k1000-patching-setup-tips-things-i-have-learned-ldap-smart-labels-sql-reports

***PLEASE ONLY REFER TO THIS FOR HISTORICAL REFERENCE***

LDAP, Patching & SQL Reports - Using All Three For Efficient & Managed Patching (And Other Cool Tricks)
________________________________________________________
________________________________________________________

Preface

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.
________________________________________________________
________________________________________________________

1) Get 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.
________________________________________________________
________________________________________________________

2) Plan 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).
________________________________________________________
________________________________________________________

3) Tag 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 - WinXPsp3)
JSMITH ----------> Pittsburgh (laptop) (roaming - Win7sp1x64) (sales)
CFO -------------> Pittsburgh (laptop) (roaming.nr - WinXPsp3)
* I used ".nr" to denote a special patch deploy schedule with no reboot, per CFO's request
PGH-KILN ---------> Pittsburgh (desktop) (control - WinXPsp3)
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 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 WinXP SP3 machines are only scanned for WinXP SP3 patches).

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

________________________________________________________
________________________________________________________

4) Create 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

Note 1 - 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).

Note 2 - 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).

Note 3 - You'll notice below that we add (name=KBOX_COMPUTER_NAME) 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.
_________________

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=*))
________________________________________________________
________________________________________________________

5) 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)

The server2 tags listed below are for targeting critical servers that absolutely must be rebooted separately from other servers (i.e. cluster pairs, virtual server pairs, etc), so that in the event that patching goes badly, we still have machines that can carry on operations while the other pair members are being repaired or rolled back. Currently we are not deploying patches via the K1000, but the plan is to do so eventually so I have already put things in place to facilitate this.

stationary - WinXPsp3
stationary - Win7sp1x64
roaming - WinXPsp3
roaming - Win7sp1x64
roaming.nr - WinXPsp3
control - 2Ksp4
control - XPsp3
server - 2Ksp4
server - 2K3sp2
server - 2K3.sp2x64
server - 2K8sp2
server - 2K8.sp2x64
server - 2K8.r2sp1x64
server2 - 2K3sp2
server2 - 2K3.sp2x64
___________________________________

LDAP Machine Labels (for patching)

Note - Please notice how the description query target matches the ADUC computer account descriptions. I use the wildcard character (*) before and after to ensure anything before and after gets ignored.

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

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

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

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

patch (roaming - Win7sp1x64)
(&(&(description=*roaming - Win7sp1x64*)(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 (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))

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

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

6) Apply the 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 we 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...).
________________________________________________________
________________________________________________________

7) 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 Report, then:

Report Title -------------> OS Version
Report Category --------> OS
Report Topic ------------> Computer
click Next
Check boxes ------------> System Name, OS Name, Service Pack
click Next
Field Order -------------> System Name, OS Name, Service Pack
click Next
Order By ---------------> OS Name, Service Pack, System Name (all ascending)
click Next
click Save

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

Again, for point of reference, I have my Patch Labels set to "All Patches" (no limits), have enabled the "Hide Disabled Patches on Patch Listing" under Disabled Patch Options (prefer not to see these), and 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.
________________________________________________________
________________________________________________________

8) Creating smart labels for patches
________________________________________________________

Smart labels are extremely useful for specifying exactly which patches will be used during the detect and deploy phases of patching (typically they should overlap). To create smart labels for patches, click on Security - Patching - Patch Listing and then select the Create Smart Label tab.

In my environment, we only address critical patches so Impact is one criteria to consider. Others, based on the patching best practices guide, are OS version and Patch Type (OS & Application). I include one more criteria (Release Date) to shrink the number of patches further, as my patching practices thus far have been solid and documented. Depending on your environment, you may want to scratch this last factor if you want to be absolutely assured of patching completion, but it does cut the number down considerably.

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

XP SP3 (OS & App) ----------> 1,690 patches
XP SP3 (OS only) ------------> 228 patches
XP SP3 (OS only, Critial) -----> 218 patches
XP SP3 (OS only, Critical, since 2011-04-01) -----> 36 patches

Personally, I include all patches during client machine builds, so just scanning for the last 6 months of patches does not present any issues in my environment. Although ideally (for automation reasons), I would *REALLY* appreciate it if Kace would add the ability to use relative dates in the patching smart labels tool, as it is already possible to use them in SQL reports - i.e. DATEDIFF(NOW(), HD_TICKET.TIME_CLOSED) < 7). Presently, I *do* have to go revisit my patching smart labels periodically and adjust things when the number of patches gets too high and this would definitely save some time by letting things go pretty much on auto-pilot. I've requested this during Advisory Kouncil and here (http://kace.uservoice.com/forums/82699-k1000/suggestions/2224921-allow-last-days-for-patch-release-date-search), so there's hope.
____________________

Patch labels:

Here are the criteria I use for creating patching smart labels (os & app for each platform). I cover less months for the application patch scans as these tend to have *many* more patches than the OS patch lists.

OS
-Patch Type = OS
-Operating System = OS version
-Impact = Critical
-Status = Active

Applications
-Patch Type = Application
-Operating System = OS version
-Impact = Critical
-Status = Active
____________________

And here are the corresponding names of the patching smart labels I use, based on those criteria:

os-critical-xpsp3-04
app-critical-xpsp3-06

os-critical-7sp1-04
app-critical-7sp1-06

os-critical-2ksp4-04
app-critical-2ksp4-06

os-critical-2k3sp2-04
app-critical-2k3sp2-06

os-critical-2k3sp2x64-04
app-critical-2k3sp2x64-06

os-critical-2k8sp2-04
app-critical-2k8sp2-06

os-critical-2k8sp2x64-04
app-critical-2k8sp2x64-06

os-critical-2k8r2sp1x64-04
app-critical-2k8r2sp1x64-06
________________________________________________________
________________________________________________________

9) 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:

Schedule Description
------------> Stationary - WinXPsp3 (Detect & Deploy - OS - Critical - 04-2011)

Patch Action
------------> Detect and Deploy

Machine Selection - Limit Run To Selected Machine Labels
------------> patch (stationary - WinXPsp3)

Limit Run To Machines With Selected Operating Systems
------------> Win XP SP3

Detect Patch Label Selection - Limit Detect To Selected Patch Labels
------------> os-critical-xpsp3-04

Deploy Patch Label Selection - Limit Patches To Selected Patch Labels
------------> os-critical-xpsp3-04

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 * * 4

Suspend pending tasks after (720) minutes from scheduled start
------------> checked
____________________

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, don't run on next connection if offline). I still have afternoon and night shift users to take into account, which is why I still 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, until 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).

For roaming users, I used the "run on next connection if offline" and delay it by 15 minutes, so that if someone just needs to check email quickly and shut back down their (potentially very slow Internet connection) won't be dragged down by patch tasks. And per the patching best practices guide, this group gets a individual patch schedules for detect and deploy tasks. I found this helped considerably in actually 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).
____________________

Here are examples of how I have my patch times scheduled for different machine types, split between OS and apps. 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 (apps and OS) once a week, mainly because I my machines are so randomly on the network and because I got far too many complaints using the "run on next connection if offline" option. This has been my compromise.

I also try to separate my apps and OS scans and deploys by at least a couple days to give all of the machines a chance to complete their tasks before the next one comes up. Otherwise, unfinished tasks start filling up the agent tasks queue and nothing gets done. And in regards to OS scans, I set them to run a little later in the week with the intention of catching the Microsoft Patch Tuesday releases (which the patch feed should have by then).

As mentioned earlier, eventually the server2 machines will be patched separately from the server machines, but for the time being they're on the same schedule (since they're only detecting).

Patch Schedule Times

stationary - apps ------------> 0 21 * * 1 ---> (9:00PM every Monday)
------------> * detect & deploy
stationary - os --------------> 0 21 * * 4 ---> (9:00PM every Thursday)
------------> * detect & deploy

roaming - apps --------------> 30 11 * * 1 ---> (11:30AM every Monday)
------------> * detect
roaming - apps --------------> 30 11 * * 2 ---> (11:30AM every Tuesday)
------------> * deploy
roaming - os ----------------> 30 11 * * 4 ---> (11:30AM every Thursday)
------------> * detect
roaming - os ----------------> 30 11 * * 5 ---> (11:30AM every Friday)
------------> * deploy

control - apps --------------> 0 21 * * 6 ---> (9:00PM every Saturday)
------------> * detect
control - os ----------------> 0 21 * * 5 ---> (9:00PM every Friday)
------------> * detect

server - apps ---------------> 0 21 * * 6 ---> (9:00PM every Saturday)
------------> * detect
server - os -----------------> 0 21 * * 5 ---> (9:00PM every Friday)
------------> * detect

server2 - apps --------------> 0 21 * * 6 ---> (9:00PM every Saturday)
------------> * detect
server2 - os ----------------> 0 21 * * 5 ---> (9:00PM every Friday)
------------> * detect
____________________

Once all is said and done, I end up with the following patch schedules. More than I had initially anticipated setting up, but there it is. The nice part is that the SQL reports make short work of getting through the results, as you'll see.

Patch Schedules

Stationary - Win7sp1x64 (Detect & Deploy - Apps - Critical - 06-2011)
Stationary - Win7sp1x64 (Detect & Deploy - OS - Critical - 04-2011)

Stationary - WinXPsp3 (Detect & Deploy - Apps - Critical - 06-2011)
Stationary - WinXPsp3 (Detect & Deploy - OS - Critical - 04-2011)

Roaming - Win7sp1x64 (Detect - Apps - Critical - 06-2011)
Roaming - Win7sp1x64 (Deploy - Apps - Critical - 06-2011)
Roaming - Win7sp1x64 (Detect - OS - Critical - 04-2011)
Roaming - Win7sp1x64 (Deploy - OS - Critical - 04-2011)

Roaming - WinXPsp3 (Detect - Apps - Critical - 06-2011)
Roaming - WinXPsp3 (Deploy - Apps - Critical - 06-2011)
Roaming - WinXPsp3 (Deploy.NR - Apps - Critical - 06-2011)
Roaming - WinXPsp3 (Detect - OS - Critical - 04-2011)
Roaming - WinXPsp3 (Deploy - OS - Critical - 04-2011)
Roaming - WinXPsp3 (Deploy.NR - OS - Critical - 04-2011)

Control - Win2Ksp4 (Detect - Apps - Critical - 06-2011)
Control - Win2Ksp4 (Detect - OS - Critical - 04-2011)

Control - WinXPsp3 (Detect - Apps - Critical - 06-2011)
Control - WinXPsp3 (Detect - OS - Critical - 04-2011)

Server - Win2Ksp4 (Detect - Apps - Critical - 06-2011)
Server - Win2Ksp4 (Detect - OS - Critical - 04-2011)

Server - Win2K3sp2 (Detect - Apps - Critical - 06-2011)
Server - Win2K3sp2 (Detect - OS - Critical - 04-2011)

Server - Win2K3sp2x64 (Detect - Apps - Critical - 06-2011)
Server - Win2K3sp2x64 (Detect - OS - Critical - 04-2011)

Server - Win2K8sp2 (Detect - Apps - Critical - 06-2011)
Server - Win2K8sp2 (Detect - OS - Critical - 04-2011)

Server - Win2K8sp2x64 (Detect - Apps - Critical - 06-2011)
Server - Win2K8sp2x64 (Detect - OS - Critical - 04-2011)

Server - Win2K8r2sp1x64 (Detect - Apps - Critical - 06-2011)
Server - Win2K8r2sp1x64 (Detect - OS - Critical - 04-2011)

Server2 - Win2K3sp2 (Detect - Apps - Critical - 06-2011)
Server2 - Win2K3sp2 (Detect - OS - Critical - 04-2011)

Server2 - Win2K3sp2x64 (Detect - Apps - Critical - 06-2011)
Server2 - Win2K3sp2x64 (Detect - OS - Critical - 04-2011)
____________________

Note - The Roaming - WinXPsp3 (Detect...) schedules include both of the following machine labels, since only the deploy job is different (i.e. the "no reboot" part, which is what the ".nr" represents):

patch (roaming - WinXPsp3)
patch (roaming.nr - WinXPsp3)
________________________________________________________
________________________________________________________

9) 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. That being said, some of the errors continue to defy explanation, but at some point I have to lay some of the blame on some of my aging XP machines...
____________________

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" (I've requested one - 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.
________________________________________________________
________________________________________________________

10) 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 "kind of" 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). I'll be honest and admit that I'm still a little fuzzy on JOIN statements, but I have noticed that certain variables in the SELECT statement need to have corresponding JOIN statements, otherwise the report will generate errors. I typically just looked at other reports to figure out what to use. 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 when to include 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 (sorry, GillySpy!), but so far this level of comprehension has served me well enough to get the job done.

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'
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'
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.TITLE

*Break on Columns*
Computer Name
____________________

*** Note that my client reports may need tweaking on your end, as the first WHERE statement uses my labels (and because I'm excluding both the "control" and "server" labels - which means this pulls my "stationary" and "roaming" label machines). If you wanted to edit, you could probably remove the NOT from the WHERE statements and change the labels to ones that fit your environment. But this should give you an idea of how you can handle multiple labels in one report (while excluding others).
____________________

*Title*
Client missing active critical patches (patch)

*Report Category*
Patching (Custom)

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

*SQL Select Statement*
SELECT PP.TITLE AS 'Patch Name',
M.NAME AS ComputerName,
OS_NAME AS 'Windows Version'
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, MACHINE_LABEL_JT where M.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'control')) AND (1 NOT in (select 1 from LABEL, MACHINE_LABEL_JT where M.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.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 patch (roaming) and patch (stationary) labels, sorted by PC

*SQL Select Statement*
SELECT PP.TITLE AS 'Patch Name',
M.NAME AS 'Computer Name',
OS_NAME AS 'Windows Version'
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, MACHINE_LABEL_JT where M.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'control')) AND (1 NOT in (select 1 from LABEL, MACHINE_LABEL_JT where M.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'server'))
AND MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY M.NAME, PP.TITLE

*Break on Columns*
Computer Name
____________________

*** Same note on the server reports, as these use the "server" label machines in the WHERE statement.
____________________

*Title*
Server missing active critical patches (patch)

*Report Category*
Patching (Custom)

*Description*
Lists missing active critical patches for all computers in patch (server 1) and patch (server 2) labels, sorted by patch

*SQL Select Statement*
SELECT PP.TITLE AS 'Patch Name',
M.NAME AS ComputerName,
OS_NAME AS 'Windows Version'
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 in (select 1 from LABEL, MACHINE_LABEL_JT where M.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.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 patch (server 1) and patch (server 2) labels, sorted by PC

*SQL Select Statement*
SELECT PP.TITLE AS 'Patch Name',
M.NAME AS 'Computer Name',
OS_NAME AS 'Windows Version'
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 in (select 1 from LABEL, MACHINE_LABEL_JT where M.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'server'))
AND MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY M.NAME, PP.TITLE

*Break on Columns*
Computer Name
____________________

I created all of these reports using the old reporting tool (no longer available in K1200+, from what I understand) and continue to do so as I like the PDF output. However, I did test these using 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 topic items that weren't related. 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.

John