K1000 Labels - Effective Organization & Process Flow Using Manual, Smart & LDAP Labels and Label Groups
__________________________________________________________________________________
__________________________________________________________________________________
(0) Preface
Section I - General Overview
(01) Manual Labels
(02) LDAP Labels
(03) Smart Labels
(04) Label Groups
Section II - Label Administration
(05) Assigning Labels
(06) Deleting Labels
(07) Renaming Labels
Section III - Example Labels & Label Groups
(08) Machine Labels
(09) Patch Labels
(10) Software Labels
(11) User Labels
Section IV - Label Groups
(12) Self-Organization
(13) Use Cases
Section V - Manual Labels
(14) Construction
(15) Machine Label Examples
(16) User Label Examples
Section VI - LDAP Labels
(17) LDAP Setup
(18) LDAP Label Construction
(19) Machine Label Examples
(20) User Label Examples
Section VII - Smart Labels
(21) Construction
(22) Machine Label Examples
(23) Patch Label Examples
(24) Software Label Examples
(X) Conclusion
__________________________________________________________________________________
__________________________________________________________________________________
(0) Preface
__________________________________________________________________________________
Labels are critical to the successful and efficient administration of the K1000, as they will either be referenced or act as the driving agents in practically every operation - the K1000's vascular system, if you will.
They are the most "organic" part of the K1000, in the sense that (once you are comfortable with them) they will almost seem to grow (and occasionally, need pruned) as you develop the other parts of your K1000. Of course, you will be creating them yourself and will have total control over them, so I guess good analogies would be bonsai trees, vegetable gardens or reef tanks - there are the obvious setup and maintenance tasks, but later (as things mature) you will get a "feel" for how to help them grow, as you see the overall system take shape. In more technical terms, just think of them as very flexible organizational tools (name tags, basically) that will allow you to organize and target specific groups of machines, software, patches, users and more.
The potentially daunting part is figuring out how to employ them to your benefit, because (as every new K1000 admin is painfully aware) they are initially a blank slate with lots of options (and thus, possibilities) with little direction on effective usage. The good news is, the more you use the different parts of your K1000 and get used to the flow of things, the more label use cases will jump out at you. This is why explaining labels can be a slight challenge, as every organization is unique - what works great for my medium-sized network with all of its particular quirks probably won't be a cookie-cutter solution for your own.
That being said, all organizations share *some* basic commonalities, so a number of approaches others take with labels *can* be applied to your own org. This is the point of this article - to explain label types and then show you how I'm using them in my own organization. For clarity, there will be a decent degree of repetition and mind-numbing detail (as usual ^_^), but hopefully by the end you will have a solid understanding and some brilliant ideas for your own labels. And if nothing else, some Smart label queries you can throw on your own box.
One disclaimer - I do not claim to be the originator of all of the labels listed here. In particular, a number of my Smart labels were inspired by, built around or outright copied from other contributors' offerings here on ITNinja. My goal here is to analyze and illustrate *all* of my working labels so others have some real-world examples - not to claim authorship (although I did write a good number of them). I apologize for not giving credit for every borrowed label (honestly, I didn't keep track of my sources for labels) and if you see one that you know you developed, please know that I truly appreciate your original contribution and that I am only trying to help as well. Consider this to be imitation as the sincerest form of flattery (or something).
__________________________________________________________________________________
__________________________________________________________________________________
Section I - General Overview
__________________________________________________________________________________
(01) Manual Labels
__________________________________________________________________________________
These are labels you create and assign to resources yourself (manually), hence the name. These can be *very* useful in the right circumstances - but if there's some object criteria that you can target, by all means use Smart labels. Likewise, use LDAP labels if there is some benefit to having the information in ADUC fields (or leverage it if it's already there). I personally use manual labels the most for very narrowly focused K1000-specific user groups that are only utilized in the Service Desk queues, as I see no advantage in having this information in (or pulled from) ADUC and the focus is so narrow that Smart label queries would just be specifying user names (which would be a bit silly albeit possible).
Manual labels generally take effect as soon as they are assigned to objects, unlike some types of Smart labels (and LDAP labels in general). This aspect makes them very useful for focused tasks, as (for example) no time will be wasted waiting on machines to run Inventory before the labels can be utilized in other K1000 operations.
Another function of manual labels is acting as the "parent" or foundation for Smart and LDAP labels. This is done automatically for Smart labels if the Create Smart Label Wizard is used, but when manually creating Smart labels (and LDAP labels in general) the manual label will need created first. The manual label is then specified in the Assigned Label field (Smart labels) or Associated Label Name field (LDAP labels) when creating the respective Smart or LDAP label.
__________________________________________________________________________________
(02) LDAP Labels
__________________________________________________________________________________
These labels work by finding matches in ADUC (or alternative) computer and user objects. If the criteria specified in the LDAP label is found in a targeted ADUC object, then the label is applied to the corresponding K1000 (machine or user) object. They can be a great time saver if you already have computer and user objects fairly populated with information and very convenient if sysadmins need to reference this information (in which case adding it to ADUC just makes sense). My own use cases fall into both categories - for some LDAP labels I merely leveraged fields that were already populated, and for others I spent time constructing and applying descriptions to ADUC objects so I could take advantage of them. Although initially populating ADUC fields can be arduous, maintenance is typically light with adding new users and machines usually just requiring a quick copy (or copy/paste) or objects (or fields).
LDAP labels are constructed with a "parent" manual label and the LDAP label itself, which references the manual label and contains LDAP connection information and search filter criteria (for accessing the LDAP directory and matching specified fields, respectively). The LDAP label's fields can be populated by using a "wizard" (the LDAP Browser) or manually, and there is also an option to duplicate existing labels for use as templates. Even if you plan on building LDAP labels manually, it is still a good idea to "test" them using the LDAP Browser to ensure that match results are as expected. When initially setting things up, I had to tweak my ADUC fields and LDAP Search Filters on several occasions due to overlapping and unexpected matches - if I hadn't tested using the LDAP Browser, it could have been a while before I noticed them.
Due to all of the fields that need populated, duplicating can be a particularly great time saver for LDAP labels when you have a group to build. The most efficient duplication strategy I have found is to first work out the Search Filter query for all of the new labels (in Notepad, etc), followed by creating the corresponding "parent" manual labels. Then it's just a matter of duplicating an existing LDAP label and tweaking the Search Filter field in the duplicates (note - this approach assumes all the other LDAP criteria will remain the same). Just make it a point to remember to check the Enabled box when taking this approach, as it's easy enough to overlook and missing it can cause unneeded frustration when you realize that the label isn't being applied as expected (been there, done that...).
Unlike manual labels, LDAP labels can only be applied to Machines and Users in the K1000. They are applied when a machine checks in (or user logs into the K1000 web portal) and the LDAP label's search filter criteria matches the referenced ADUC object fields. Adjustments to ADUC object fields and search filter criteria are not reflected on the fly and will only be applied when machines check in (or users log in) following the change.
__________________________________________________________________________________
(03) Smart Labels
__________________________________________________________________________________
This type of label is kind of like a tag you would apply to search engine results (the same way Personal Filters work here on ITNinja). You don't necessarily specify the actual objects that will have these labels applied (as is the case with manual labels), rather you specify the *criteria* for the objects and the labels get applied to the objects based on those criteria.
Like LDAP labels, they consist of two parts (a manual "parent" label and a Smart label), but instead of LDAP these are driven by a SQL query that defines the matching criteria. The SQL query can be automatically generated by using the Create Smart Label wizard and specifying the criteria via the drop-down boxes, however certain criteria and conditions are impossible to define using the wizard, so there is also the option to use your own (carefully crafted & tested) SQL query. Regardless of how they are constructed, one of the most useful tricks I've found is using Smart labels to combine other labels, and they be any of the other label types. Using the wizard (or the Advanced Search tool), just specify the Label Names criteria and you can quickly filter your results further, as I've done for a number of my machine labels - one example might be a Smart label (or Advanced Search) combining an LDAP label for machine location and a different Smart label for machine age, which results in a list of machines that meet both sets of criteria (i.e. Pittsburgh computers over 5 years old).
Also like LDAP labels, once created you have the option to duplicate Smart labels. The most effective way I've found to employ this capability is to work out your queries and create the respective "parent" manual labels ahead of time. Then it's just a matter of going into the template Smart label's screen and hitting Duplicate, and a new Smart label with the same query will be started. From there, specify the "parent" manual label, tweak the query as necessary and then hit Save. Keep repeating the process and you can build a group pretty quickly. This strategy is particularly useful when you will only be changing one value in the query, like I did when constructing Smart labels that target machine age (1 year old, 2 years old, etc).
Unlike the other label types, Smart labels are restricted to Machine, Software, Patch, Dell Package and IP Scan objects - so no Smart labels for Processes, Startup Items or Services. Another unique aspect is the ability to order Smart labels - this effectively allows you to nest them so results from one will be passed up to a higher order (number) Smart label for further criteria to be applied, a very useful aspect considering the four field limit when building Smart labels using the wizard (although honestly, REGEX matches let you work around this limit in *most* cases). For all intensive purposes, this is the same as having the lower order query function as an inline view (noncorrelated subquery), passing results up to the higher order main query - something that would require a decent amount of time and MySQL skills to write from scratch. Another nice thing about this approach is the flexibility to employ lower order Smart labels in multiple higher order Smart labels, making it very reuse friendly. I'm using this capability in my application patch Smart labels to progressively filter out patches in the highest level label, and it works very nicely.
The way Smart labels are applied depends on the type of K1000 object - for example, software and patch Smart labels are evaluated and applied immediately (the same idea as running an Advanced Search, just that the search criteria are saved to a label). On the other hand, machine objects have applicable Smart labels assigned when a machine checks in and the criteria defined in the Smart label matches that machine's Inventory results. Changes to the Smart label's criteria (SQL query) are reflected similarly.
__________________________________________________________________________________
(04) Label Groups
__________________________________________________________________________________
This type of "label" is only for organizational purposes, and there are some noteworthy functional restrictions. For example, they can't be targeted for Patching or Managed Installations (or in a Replication Share's Label field, which I did when I first set things up... >_<). Label groups can be as inclusive, overlapping and nested as deeply as desired, but (just like folders) it all depends on how many levels you want to scroll through - two is my max (for Label Groups, at least).
I have found that the greatest benefit (saving time!) is realized when you start amassing *lots* of various labels, as you won't have to scroll as much through the View by: > Label menu. Logical arrangement also makes finding labels much easier for other K1000 admins/users, as they can quickly drill down into the various categories without having to make assumptions based on label names. Finally, they are also nice to use while giving others a quick K1000 tour, as the results populate faster and are more interactive than reports (since you can click into the resulting objects).
__________________________________________________________________________________
__________________________________________________________________________________
Section II - Label Administration
__________________________________________________________________________________
(05) Assigning Labels
__________________________________________________________________________________
The most common label task by far is assigning labels. Once you've created a label (and in the case of Smart and LDAP labels, verified a new one is working as expected), the next step is to assign it before it can actually be used/applied. Almost every section of the KBOX has an option for label usage, for example:
1) Go to Scripting > Scripts
2) Click on a script name
3) In the scripts's Detail Item screen, find the Deployment section
4) Click on the Edit link next to Limit Deployment To Selected Labels
5) Start typing the target label's name in the Select from Available Machine Labels drop-down
6) If necessary, scroll through multiple matches, then hit Enter
7) Verify the correct label is listed in the Limit Deployment To: section
8) Click OK to save and return to the Edit Detail screen
9) Make any other necessary adjustments and hit Save at the bottom of the screen
Another example is assigning a manual label to multiple objects (i.e. computers, software, patches, users, etc):
0) If possible, run an Advanced search to only list objects that should receive the label
1) Select the label's target objects using the leftmost checkboxes (or uppermost checkbox next to Name to select all)
2) From the Choose Action menu, expand Apply Label...
3) Select the target label name from the list of available labels
Be aware that the list of labels under Apply Label... will correspond to any label usage restrictions specified in the manual label's Edit Detail screen, so you may not see all of your labels listed. One thing to note - Smart labels created using the wizard will automatically be restricted to that corresponding object type. For example, running the Create Smart Label wizard from the Inventory > Computers screen will result in a Smart label restricted to Computer Inventory and will not appear when on the Inventory > Software screen. *verify*
__________________________________________________________________________________
(06) Deleting Labels
__________________________________________________________________________________
Before you can delete a label, you'll need to remove it from every screen/object it is currently assigned (or you'll get an error advising that it's still being used). The easiest way to figure out where it's being used (regardless of label type) is to open the manual label's Edit Detail screen (just click on the label name) and then expand the Labeled Items section at the bottom. If there is an associated LDAP or Smart label present, these will need deleted prior to deleting the manual label.
Most screens will have an Edit link next to where the label is specified and you can just remove it by using the garbage can icon, as in this example:
1) Go to Inventory > Computers
2) Click on a machine name
3) In the machine's Detail Item screen, scroll to the Activities section
4) Click on Labels to expand the list
5) Click on Edit
6) Click on the garbage can icon next to the label name
7) Click OK
8) Click Save
This approach is ideal for when you need to remove multiple manual labels from a single machine (or are already in the machine's Detail Item screen), but would be tedious for multiple objects.
If a label is currently being applied to multiple objects, it will need removed from all of them. The easiest way to do this is to go into the applicable screen and complete the necessary steps, for example:
1) Go to Inventory > Computers
2) Select View by:
3) Specify the target label
4) Select all objects using the uppermost checkbox next to Name
5) Select Choose Action
6) Specify Remove Label
7) Specify the target label
Note that if an LDAP or Smart label is removed from an object and the criteria is still true, the label will be reapplied. LDAP and Smart labels do not need removed from objects before being deleted, only from "Limit Deployment To..." type screens.
__________________________________________________________________________________
(07) Renaming Labels
__________________________________________________________________________________
One common aspect of all label types is that changes to label *names* are applied on the fly (as I can attest to, having taken my labels through several iterations of naming schemes as my setup grew and matured). This is a huge time saver, as labels tend to get embedded in *everything* - just update the manual label and the change will take effect in *every* K1000 screen and field that specifies that label (including corresponding Smart and LDAP label names).
__________________________________________________________________________________
__________________________________________________________________________________
Section III - Example Labels & Label Groups
__________________________________________________________________________________
Before I get into details, I want to list my current labels and label groups. I'm using all three types, but there are distinctions aside from how they are employed. For example, life cycles depend on the application - some are more or less permanent (like hardware characteristics), while other are transient (like current software versions). Still others are just the start of an idea that I'll develop further as inspiration strikes.
Here's a game now that you've gotten through the obligatory overview - can you determine what type of label construction is being used based *only* on the names of the following labels? Please disregard the fact that *all* are manual labels at the most basic level...
__________________________________________________________________________________
(08) Machine Labels
__________________________________________________________________________________
Machine - Age
_____________________________
machine age > 01 year
machine age > 02 years
machine age > 03 years
machine age > 04 years
machine age > 05 years
machine age > 06 years
machine age > 07 years
machine age > 08 years
machine age > 09 years
machine age > 10 years
_____________________________
_____________________________
Machine - Department
_____________________________
hr pc
sales pc
_____________________________
_____________________________
Machine - Hardware
_____________________________
client hdd space < 01gb
client hdd space < 02gb
client hdd space < 05gb
client hdd space < 10gb
client hdd space < 15gb
client hdd space < 20gb
client ram < 1gb
client ram = 512mb
_____________________________
_____________________________
Machine - Location
_____________________________
Site - Client Desktop PCs
Site - Client Laptops
Site - Clients (All)
Site - Computers (All)
Site - Control PCs
Site - Servers
_____________________________
_____________________________
Machine - Location
Site - Client Desktop PCs
_____________________________
East Canton desktop
Greensboro desktop
Hammond desktop
Hillsborough desktop
King Of Prussia desktop
Marelan desktop
Moulton desktop
Oak Hill desktop
Pittsburgh desktop
Remote desktop
Santa Fe Springs desktop
Shenango desktop
Tarentum desktop
Testnet desktop
UK desktop
Warren desktop
Warren Warehouse desktop
Wellston desktop
_____________________________
_____________________________
Machine - Location
Site - Client Laptops
_____________________________
East Canton laptops
Greensboro laptops
Hammond laptops
Hillsborough laptops
King Of Prussia laptops
Marelan laptops
Moulton laptops
Oak Hill laptops
Pittsburgh laptops
Remote laptops
Santa Fe Springs laptops
Shenango laptops
Tarentum laptops
Testnet laptops
UK laptops
Warren laptops
Warren Warehouse laptops
Wellston laptops
_____________________________
_____________________________
Machine - Location
Site - Clients (All)
_____________________________
East Canton clients
Greensboro clients
Hammond clients
Hillsborough clients
King Of Prussia clients
Marelan clients
Moulton clients
Oak Hill clients
Pittsburgh clients
Remote clients
Santa Fe Springs clients
Shenango clients
Tarentum clients
Testnet clients
UK clients
Warren clients
Warren Warehouse clients
Wellston clients
_____________________________
_____________________________
Machine - Location
Site - Computers (All)
_____________________________
East Canton computers
Greensboro computers
Hammond computers
Hillsborough computers
King Of Prussia computers
Marelan computers
Moulton computers
Oak Hill computers
Pittsburgh computers
Remote computers
Santa Fe Springs computers
Shenango computers
Tarentum computers
Testnet computers
UK computers
Warren computers
Warren Warehouse computers
Wellston computers
_____________________________
_____________________________
Machine - Location
Site - Control PCs
_____________________________
East Canton control
Greensboro control
Hammond control
Hillsborough control
King Of Prussia control
Marelan control
Moulton control
Oak Hill control
Pittsburgh control
Remote control
Santa Fe Springs control
Shenango control
Tarentum control
Testnet control
UK control
Warren control
Warren Warehouse control
Wellston control
_____________________________
_____________________________
Machine - Location
Site - Servers
_____________________________
East Canton servers
Greensboro servers
Hammond servers
Hillsborough servers
King Of Prussia servers
Marelan servers
Moulton servers
Oak Hill servers
Pittsburgh servers
Remote servers
Santa Fe Springs servers
Shenango servers
Tarentum servers
Testnet servers
UK servers
Warren servers
Warren Warehouse servers
Wellston servers
_____________________________
_____________________________
Machine - OS & Type
_____________________________
desktop
laptop
server
server2k3
server2k3x64
server2k8
server2k8r2x64
server2k8x64
virtual
win7
win7-desktop
win7-laptop
winxp
winxp-desktop
winxp-eng
winxp-fr
winxp-laptop
_____________________________
_____________________________
Machine - Patching
_____________________________
patch (control - 2Ksp4)
patch (control - 7sp1x64)
patch (control - XPsp3)
patch (roaming - 7sp1x64)
patch (roaming - XPsp3)
patch (roaming.nr - 7sp1x64)
patch (roaming.nr - XPsp3)
patch (server - 2K3.sp2x64)
patch (server - 2K3sp2)
patch (server - 2K8.r2sp1x64)
patch (server - 2K8.sp2x64)
patch (server - 2K8sp2)
patch (server - 2Ksp4)
patch (stationary - 7sp1x64)
patch (stationary - XPsp3)
_____________________________
_____________________________
Machine - Policy Enforcement
_____________________________
client backups
redirectmydocs
_____________________________
_____________________________
Machine - Role
_____________________________
canada pc
client
client-english
client-french
control
dc
french
replication-share
restricted pc
shared
workgroup
_____________________________
_____________________________
Machine - Software
_____________________________
current-ar(10.1.4)
current-flash(11.4.402.265)
current-java(7u7)
inst-vpro
office 2003 pc
old-ar(pre-10.1.4)
old-flash(pre-11.4.402.265)
old-ie(pre-8)
old-java(pre-7u7)
old-wmp(pre-11)
uninst-ar8dic
uninst-ar9dic
_____________________________
_____________________________
Machine - Utility
_____________________________
Force Check-In
JV-Replication
__________________________________________________________________________________
(09) Patch Labels
__________________________________________________________________________________
Patching - Patch Group
_____________________________
patch apps-server
patch-apps
patch-apps-adobe
patch-apps-cfilter
patch-apps-client
patch-apps-id-title
patch-apps-main
patch-apps-regex
patch-apps-server
patch-apps-sfilter
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
__________________________________________________________________________________
(10) Software Labels
__________________________________________________________________________________
Software - Title
_____________________________
adobe reader
office 2003
__________________________________________________________________________________
(11) User Labels
__________________________________________________________________________________
User - Department
_____________________________
executive
finance
hr
it
manufacturing
office
purchasing
qa
r&d
sales
shipping
traffic
_____________________________
_____________________________
User - Location
_____________________________
canada
East Canton users
Greensboro users
Hammond users
Hillsborough users
King Of Prussia users
Marelan users
Moulton users
Oak Hill users
Pittsburgh users
Remote users
Santa Fe Springs users
Shenango users
Tarentum users
UK users
Warren users
Warren Warehouse users
Wellston users
_____________________________
_____________________________
User - Role
_____________________________
approvers
hr manager
industrial
it director
kbox admin
managers
restricted
steel
__________________________________________________________________________________
__________________________________________________________________________________
Section IV - Label Groups
__________________________________________________________________________________
(12) Self-Organization
__________________________________________________________________________________
As I explained earlier, label groups are primarily used for organizational convenience. Rather than trying to scroll through nearly 250 labels trying to find a single label, these condense my initial View by: results to these 14 label groups:
Machine - Age
Machine - Department
Machine - Hardware
Machine - Location
Machine - OS & Type
Machine - Patching
Machine - Policy Enforcement
Machine - Role
Machine - Software
Machine - Utility
Patching - Patch Group
Software - Title
User - Department
User - Location
User - Role
It's *much* faster to find things now and I'm also much less likely to have a stray label that gets forgotten (which is quite easy to have happen when you get a decent collection going).
Another point - by ensuring that *every* label is assigned to a label group, focus and purpose result. For example, my first label group was simply called "Location" and included my "computers" machine labels (which list all of the computers by site). I initially created this group simply because I was using these labels a *lot* and grew tired of scrolling through the entire list (which was much smaller at that point, but still required going to the bottom of the list and holding the Down arrow key to get to the results that went off of the screen). I also found myself frequently making advanced searches using label combinations like "Pittsburgh computers + client" or "East Canton computers + laptop" (particularly in reports and for deploying scripts), so this original group organically developed into more focused labels, which I organized using these label groups:
Machine - Location (parent label group)
Site - Client Desktop PCs
Site - Client Laptops
Site - Clients (All)
Site - Computers (All)
Site - Control PCs
Site - Servers
This is what I was referring to earlier when I said that after a while labels begin to come into being and organize (almost) by themselves. When you find yourself running searches using existing labels and other criteria (including other labels), it's probably time to consider making (and grouping) more based on those criteria.
__________________________________________________________________________________
(13) Use Cases
__________________________________________________________________________________
Next, I'd like to explain exactly how I'm classifying my label groups, which is generally done by usage. Some will be obvious by the name, others perhaps not - hence the brief explanation.
_____________________________
Machine - Age
The labels in this group target machines based on age. And yes, believe it or not, there are machines in my company over 10 years old.
_____________________________
Machine - Department
An obvious one... show me all of the machines in the Sales department. Mainly a placeholder for the time being.
_____________________________
Machine - Hardware
This group targets machines low on free hard drive space and machines with low amounts of physical memory. This is one I'll probably build out further with more subcategories and corresponding labels, so it's more of a placeholder for the moment.
_____________________________
Machine - Location
As discussed a moment ago, this is the label group that contains all of my various categories of location focused label groups. I target these a ton in the other K1000 modules, so these are more "functional" labels rather than "informational" labels like many of the others.
_____________________________
Machine - Location > Site - Client Desktop PCs
Label group for... you guessed it, client desktop machines (i.e. no laptops, no servers, no control machines).
_____________________________
Machine - Location > Site - Client Laptops
Same as the last one, but this time focused on laptops
_____________________________
Machine - Location > Site - Clients (All)
This is a combination of the last two and excludes servers and control PCs.
_____________________________
Machine - Location > Site - Computers (All)
My original "location" label group, which focuses on all of the computers at my sites.
_____________________________
Machine - Location > Site - Control PCs
Same idea as the client desktop and laptop label groups, but focused on control PCs (i.e. non-server highly sensitive machines).
_____________________________
Machine - Location > Site - Servers
And just to round the list out...
_____________________________
Machine - OS & Type
When my boss asks for a count, I use these labels. For example, in budgeting for our Win7 replacement project, he recently asked for the number of WinXP desktops and laptops. I simply pulled up these labels and gave him the numbers - in seconds.
_____________________________
Machine - Patching
This group is used in the Limit Run To Selected Machine Labels field in patch schedules.
_____________________________
Machine - Policy Enforcement
The labels in this group target machines that should have corresponding scripts deployed.
_____________________________
Machine - Role
This one focuses on miscellaneous machine roles and characteristics.
_____________________________
Machine - Software
This group is used to help me track managed installs and get accurate machine counts (without the need to run corresponding reports).
_____________________________
Machine - Utility
This is a catch-all for labels related to K1000 administrative tasks.
_____________________________
Patching - Patch Group
This one holds my patching labels used in patch subscription and patch schedule screens.
_____________________________
Software - Title
A placeholder for the moment, the idea here being to target specific families of software and get software install counts quickly.
_____________________________
User - Department
Another obvious one... show me all of the users in the Sales department.
_____________________________
User - Location
Same as the last but based on user locations.
_____________________________
User - Role
This group consists of K1000 functional user labels (used in service desk roles and queue settings) and company-specific classifications (outside of department and location).
__________________________________________________________________________________
__________________________________________________________________________________
Section V - Manual Labels
__________________________________________________________________________________
(14) Construction
__________________________________________________________________________________
Manual labels are the most straightforward labels to create and can be done from either the Label Management screen or while in any of the screens that support the viewing of objects by label.
_____________________________
To create
a manual label from the Label Management screen (which is ideal for bulk manual label creation):
1) Go to Home > Label > Label Management
2) From the Choose Action menu, select Add New Label
3) Enter the label's name in the Name field
All other fields are optional and not necessary, unless you feel the need to restrict usage or already have a label group you'd like to specify for assignment. Note that when creating a manual label in this manner, nothing will be assigned to it initially - this will need to be done after the fact.
_____________________________
To create a manual label using the "Add Label..." menu option (ideal when you want to select multiple objects manually or following an advanced search, create a new label and have it assigned in one shot):
1) Select the label's target objects using the leftmost checkboxes
2) From the Choose Action menu, select Add Label...
3) Enter the label's name in the Add Label screen
4) Click OK
__________________________________________________________________________________
(15) Machine Label Examples
__________________________________________________________________________________
Force Check-In
I assign this label to machines when I have multiple that I want to check in (run Inventory) and they don't line up with any of my other labels. This is more of a time saver than anything, as I find it faster to scroll through the Inventory - Computer screen, click checkboxes next to machine names, tag them with this label and specify them in the Scripts > Run Now > K1000 Agent Inventory & Check-In script by label name than typing out the names in the Filter field.
_____________________________
JV-Replication
A label for my own computer, which I use for populating with replication share data for easy copying to a USB key (for populating new remote replication shares). As all replication shares require referencing a label in the setup screen, I used a manual label.
_____________________________
kbox admin
I tagged the admin account under Service Desk - Users with this label to clarify the account for other KBOX admins.
_____________________________
replication-share
In hindsight I could/should have done this with a smart label by digging into the database a bit, but took the easy way out and just assigned this label manually to the 14 machines used as replication shares (for patch/application distribution). Call me lazy...
_____________________________
Testnet clients
Testnet computers
Testnet control
Testnet desktop
Testnet laptops
Testnet servers
I added manual labels for our secondary test domain (which currently only has a single server for recovering data from a very old version of Exchange). This was primarily done so I could get it to appear in my various server reports which make regex matches on "server" and list the location by stripping the " servers" part off of the label name. I suppose this could also be done using a second ORG, but not sure one machine (that's typically off) would justify that. The other labels are placeholders for consistency.
_____________________________
uninst-ar8dic
uninst-ar9dic
Basically proof of concept (and placeholders for the concept) at this point, these were used to target specific machines with old versions Adobe Reader dictionaries that I wanted to uninstall. Basically my first test run with using Managed Installs to handle uninstalls. Manual labels were used so the machines would still be listed in the MI screen, as a Smart label listing these would drop the machine off the list completely on uninstall (and I wanted to track them a bit, since I was testing).
__________________________________________________________________________________
(16) User Label Examples
__________________________________________________________________________________
approvers
hr manager
it director
managers
These labels tag single or multiple user objects that have approval or ownership permissions in the various Service Desk queues. Manual labels are used in these cases for ease of administration.
__________________________________________________________________________________
__________________________________________________________________________________
Section VI - LDAP Labels
__________________________________________________________________________________
(17) LDAP Setup
__________________________________________________________________________________
For full details on LDAP setup, please see this article I posted earlier:
K1000 Patching - Setup, Tips & Things I Have Learned (LDAP, Smart Labels, SQL Reports)
http://www.itninja.com/blog/view/k1000-patching-setup-tips-things-i-have-learned-ldap-smart-labels-sql-reports
For continuity's sake, I'll restate the main points to illustrate how to create an LDAP label named "Pittsburgh computers" by using an ADUC computer description field that begins with "Pittsburgh (etc...":
_____________________________
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: example > (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. Keeping the Search Base DN consistent also saves time when duplicating LDAP labels as only the Search Filter will need updated.
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. 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.
__________________________________________________________________________________
(18) LDAP Label Construction
__________________________________________________________________________________
First, create your "parent" manual label. Second, create your LDAP label. The LDAP label will reference the "parent" manual label and contain your LDAP query (similar to what you used in the Search Filter in the LDAP Setup section above, but with required additions). Note that the arrangement of criteria statements in the Search Filter is completely arbitrary - for example, (&(a=1)(b=2)) is functionally the same as (&(b=2)(a=1)).
1) Go to Home > Label > LDAP Labels
2) From the Choose Action menu, select Add New Item
3) Fill out the LDAP Label fields as follows:
Enabled check this box if you want the label to be applied
Filter Type specify Machine or User
Associated Label Name whatever you named your "parent" manual 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 the next section for examples) to include any accounts that contain the specified string in the account's Description field (in ADUC).
Note 3 - You'll notice in the next examples in the next section 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 described earlier in the LDAP Setup section), there will be no results. Just be aware of this and you'll be fine.
__________________________________________________________________________________
(19) Machine Label Examples
__________________________________________________________________________________
You'll notice that the ADUC computer description fields are fairly populated (with multiple fields being targeted by separate LDAP labels), but the LDAP label Search filters only target one specific string for each corresponding LDAP label. I made the decision to use parentheses to make things a bit more legible once I realized how useful these labels could be (well before I understood the functional aspects - honestly, I was just happy about being able to pull up groups of machines based on the labels in the Inventory screen).
On a separate note, I got into LDAP labels, well before doing too much with Smart labels, and there are definitely places where Smart labels would be easier to implement while retaining the same base functionality. That being said, I do like having the info in ADUC for quick and clear access (the K1000 Inventory screen's Show Labels view can be a bit much to scan through, once you have a good number of labels going and the labels are being displayed for all of the machines).
_____________________________
_____________________________
Machine - Department Labels
hr pc
sales pc
_____________________________
Example LDAP label Search Filters:
1) (&(&(description=*hr - payroll*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
2) (&(&(description=*sales*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
LDAP Browser Version:
1) (&(description=*hr - payroll*)(objectClass=computer))
2) (&(description=*sales*)(objectClass=computer))
Corresponding ADUC computer properties field > General > Description:
1) Pittsburgh (desktop) (stationary - XPsp3en) (hr - payroll)
2) King Of Prussia (desktop) (stationary - XPsp3en) (sales)
_____________________________
At one point several years ago (well before I had the K1000), someone had requested tagging all Sales and HR computer objects (as these were deemed critical). I basically took advantage of this info already being present in ADUC to label all of these machines accordingly in the K1000. At some point I'll expand this to include more departments, so it's more of a proof-of-concept placeholder for now.
Note that since the search terms are in the middle of the Description string, I'm using a wildcard (*) before and after the search terms in the LDAP query.
_____________________________
_____________________________
Machine - Location
Site - Computers (All) Labels
East Canton computers
Greensboro computers
Hammond computers
Hillsborough computers
King Of Prussia computers
Marelan computers
Moulton computers
Oak Hill computers
Pittsburgh computers
Remote computers
Santa Fe Springs computers
Shenango computers
Tarentum computers
UK computers
Warren computers
Warren Warehouse computers
Wellston computers
_____________________________
Example LDAP label Search Filters:
1) (&(&(description=East*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
2) (&(&(&(description=Warren*)(objectClass=computer))(!(description=Warren Warehouse*)))(name=KBOX_COMPUTER_NAME))
3) (&(&(description=Warren Warehouse*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
LDAP Browser Version:
1) (&(description=East*)(objectClass=computer))
2) (&(&(description=Warren*)(objectClass=computer))(!(description=Warren Warehouse*)))
3) (&(description=Warren Warehouse*)(objectClass=computer))
Corresponding ADUC computer properties field > General > Description:
1) East Canton (laptop) (roaming - XPsp3en) (shared)
2) Warren (laptop) (roaming - 7sp1x64en) (sales)
3) Warren Warehouse (desktop) (stationary - XPsp3en)
_____________________________
As with the previous Department machine labels, I just leveraged info that was already in ADUC - conveniently, the OUs are location based so this was just a natural fit. These turned out to be some of my most useful labels and I've come up with many uses for them, including using them in reports to include a Location column (after stripping off " computers" using string functions) and restrict results, and for deriving many subcategories using Smart label + label combinations (as you'll see later).
One thing I'd like to point out here is one way to handle similar names. My initial LDAP Browser query for Warren* brought back all of the Warren Warehouse computers in addition to the Warren machines - note how I solved this with a "not" statement for the Warren label (an afterthought - you can tell by the statement order).
_____________________________
_____________________________
Machine - OS & Type Labels
desktop
laptop
server
virtual
_____________________________
Example LDAP label Search Filters:
1) (&(&(description=*desktop*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
2) (&(&(description=*laptop*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
3) (&(&(description=*virtual*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
LDAP Browser Version:
1) (&(description=*desktop*)(objectClass=computer))
2) (&(description=*laptop*)(objectClass=computer))
3) (&(description=*virtual*)(objectClass=computer))
Corresponding ADUC computer properties field > General > Description:
1) Pittsburgh (desktop) (stationary - XPsp3en)
2) Shenango (laptop) (roaming - XPsp3en)
3) Pittsburgh (virtual) (stationary - XPsp3en)
_____________________________
I added these to ADUC after getting the previous LDAP labels working, well before I was aware of chassis type as a Smart label criteria. Again, I don't regret this as having this in ADUC is in itself useful, but handling this via Smart labels would definitely be less grinding to setup - after all, it's *not* fun tagging all of the computer objects in ADUC.
_____________________________
_____________________________
Machine - Patching Labels
patch (control - 2Ksp4)
patch (control - 7sp1x64)
patch (control - XPsp3)
patch (roaming - 7sp1x64)
patch (roaming - XPsp3)
patch (roaming.nr - 7sp1x64)
patch (roaming.nr - XPsp3)
patch (server - 2K3.sp2x64)
patch (server - 2K3sp2)
patch (server - 2K8.r2sp1x64)
patch (server - 2K8.sp2x64)
patch (server - 2K8sp2)
patch (server - 2Ksp4)
patch (stationary - 7sp1x64)
patch (stationary - XPsp3)
_____________________________
Example LDAP label Search Filters:
1) (&(&(description=*control - XPsp3*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
2) (&(&(description=*roaming - 7sp1x64*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
3) (&(&(description=*server - 2K8sp2*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
LDAP Browser Version:
1) (&(description=*control - XPsp3*)(objectClass=computer))
2) (&(description=*roaming - 7sp1x64*)(objectClass=computer))
3) (&(description=*server - 2K8sp2*)(objectClass=computer))
Corresponding ADUC computer properties field > General > Description:
1) East Canton (control - XPsp3en) (restricted)
2) Santa Fe Springs (laptop) (roaming - 7sp1x64en) (sales)
3) Pittsburgh (backup) (server - 2K8sp2)
_____________________________
These are the drivers for my patch subscription screens (i.e. Limit Run to Selected Machine Labels). The only trick here was dealing with similar names (i.e. 2k8sp2 vs 2k8sp2x64), but using a "." in strategic places worked quite well. For full documentation on my patching setup, please see:
K1000 Patching - Setup, Tips & Things I Have Learned (LDAP, Smart Labels, SQL Reports)
http://www.itninja.com/blog/view/k1000-patching-setup-tips-things-i-have-learned-ldap-smart-labels-sql-reports
_____________________________
_____________________________
Machine - Role Labels
canada pc
control
dc
french
restricted pc
shared
workgroup
_____________________________
Example LDAP label Search Filters:
1) (&(&(description=*Remote - Canada*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
2) (&(&(description=*control*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
3) (&(&(description=*restricted*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
LDAP Browser Version:
1) (&(description=*Remote - Canada*)(objectClass=computer))
2) (&(description=*control*)(objectClass=computer))
3) (&(description=*restricted*)(objectClass=computer))
Corresponding ADUC computer properties field > General > Description:
1) Remote - Canada (laptop) (roaming - XPsp3en) (sales)
2) Greensboro (control - 7sp1x64en) (restricted)
3) Hammond (desktop) (stationary - XPsp3en) (shared) (restricted)
_____________________________
Again, I'm just taking advantage of already having many roles commented in my ADUC computer description fields to label all of these machines accordingly in the K1000. A lot of these are the "quirky" computers which can cause minor grief if one isn't aware of the machine's config (yes, like domain controllers... ^_^).
__________________________________________________________________________________
(20) User Label Examples
__________________________________________________________________________________
I'm handling LDAP user labels are a bit differently, as I have more fields to leverage. Some use the "description" field the same way the LDAP machine labels do, but others target the "department" field. And when a typical construct is returning undesired accounts (such as "utility" user objects that don't relate to a real person), I add further criteria that only "real person" objects would have to filter them out (such as givenName=*).
_____________________________
_____________________________
User - Department Labels
executive
finance
hr
it
manufacturing
office
purchasing
qa
r&d
sales
shipping
traffic
_____________________________
Example LDAP label Search Filters:
1) (&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(department=IT))
2) (&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(department=R&D))
3) (&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(department=Manufacturing / Shipping))
LDAP Browser Version:
1) (&(objectClass=user)(department=IT))
2) (&(objectClass=user)(department=R&D))
3) (&(objectClass=user)(department=Manufacturing / Shipping))
Corresponding ADUC user properties field > Organization > Department:
1) IT
2) R&D
3) Manufacturing / Shipping
_____________________________
These are very straightforward, as the department field contents are being matched exactly by the LDAP search filter string. Note that characters such as "&", " " and "/" cause no problems, and that the LDAP label itself does not have to match up with the target string name (i.e. LDAP label "shipping" is derived from "Manufacturing / Shipping").
_____________________________
_____________________________
User - Location Labels
East Canton users
Greensboro users
Hammond users
Hillsborough users
King Of Prussia users
Marelan users
Moulton users
Oak Hill users
Pittsburgh users
Remote users
Santa Fe Springs users
Shenango users
Tarentum users
UK users
Warren users
Warren Warehouse users
Wellston users
_____________________________
Example LDAP label Search Filters:
1) (&(&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(description=EC*))(givenName=*))
2) (&(&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(description=WAR*))(givenName=*))
3) (&(&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(description=WWH*))(givenName=*))
LDAP Browser Version:
1) (&(&(objectClass=user)(description=EC*))(givenName=*))
2) (&(&(objectClass=user)(description=WAR*))(givenName=*))
3) (&(&(objectClass=user)(description=WWH*))(givenName=*))
Corresponding ADUC user properties field > General > Description:
1) EC-MFG
2) WAR-Office
3) WWH-QA Tech
_____________________________
These are slightly less direct than the department labels, as it's not an exact 1-to-1 match. In this case, the Description fields for user accounts begin with the site name abbreviation (with extra info trailing), so adding a wildcard character (*) to the end of the abbreviation is all that was required.
_____________________________
_____________________________
User - Role Labels
canada
industrial
restricted
steel
_____________________________
Example LDAP label Search Filters:
1) (&(&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(description=*Canada*))(givenName=*))
2) (&(&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(description=*industrial*))(givenName=*))
3) (&(&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(description=*restricted*))(homeDirectory=*))
LDAP Browser Version:
1) (&(&(objectClass=user)(description=*Canada*))(givenName=*))
2) (&(&(objectClass=user)(description=*industrial*))(givenName=*))
3) (&(&(objectClass=user)(description=*restricted*))(homeDirectory=*))
Corresponding ADUC user properties field > General > Description:
1) RMT-SLS-CSR Canada
2) RMT-SLS-FSR-Industrial
3) GSO-QA - only permitted to login to GSO-QA1, GSO-QA2, GSO-QA3 & GSO-QA4 (restricted)
_____________________________
These target strings inside the description, so a beginning and ending wildcard character (*) is required. Without adding the extra criteria, the LDAP Browser returns unwanted machines and utility user accounts with the search strings in their description fields. In the case of machines being in the results, the LDAP label itself specifies (samaccountname=KBOX_USER_NAME) which should restrict it to users, but I like to err on the side of caution and make sure my LDAP Browser results are exactly what I want before building the actual LDAP label.
__________________________________________________________________________________
__________________________________________________________________________________
Section VII - Smart Labels
__________________________________________________________________________________
(21) Construction
__________________________________________________________________________________
There are basically three approaches I take to building Smart labels:
1) Use the Create Smart Label wizard
This option will be on any screen that supports using Smart Labels (i.e. Inventory > Computers). Just click on the Create Smart Label tab on the right (under the Search field), supply your criteria, review the resulting matches using the Test Smart Label button, type a name in the Choose label: field and then hit Create Smart Label. Simple, yes?
The main trick is determining what to actually put in the criteria fields and confirm the matches - and this is where a little SQL query knowledge becomes extremely useful. I frequently use the MySQL Query Browser to look for field values and confirm label results when my query is a little less obvious than "show me laptops" or "show me machines with Adobe Reader". If you need a little help getting started with SQL, see if this might help:
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
SQL queries aside, for straightforward queries the wizard is a huge time saver. For example, here's how to create a Smart label to target all laptops (as offered as an example a moment ago):
-Inventory > Computers > Create Smart Label
-Select "Chassis Type", "=" and "laptop"
-Click on Test Smart Label and review
-Type "laptop" in the Choose label: field
-Click on the Create Smart Label button
-Run inventory on a few of your laptops and confirm they have the new label applied
Another great way to use the wizard is for taking care of the preliminary Smart label creation steps when you already have a SQL query to use. I use this frequently so I don't have to bother with creating the manual label, etc:
-Use dummy criteria (i.e. Agent Version = blueberries)
-Test to verify there are 0 matches
-Specify the actual Smart label name
-Create the Smart label
-Open the Smart label via Home > Label > Smart Labels
-Paste in your SQL query
-Hit Save
-Done
It actually took more time to write that than to actually do it, and is an *extremely* useful process for creating batches of Smart labels that require minor tweaks and can't be done solely with the wizard criteria. Just create as many "dummy" labels as you need, then drop in your queries and you're set.
A final great use of the wizard is combining labels. I've done this with my "Sitename computers" labels to create more discrete labels and it's almost addictive once you get started - the combinations are almost endless! Plenty of examples further below.
_____________________________
2) Modify a wizard-created Smart label SQL query
I take this approach when I create a label that needs criteria available in the wizard *and* criteria not available in the wizard. A good example of this are my software labels that target application versions using REGEX statements. I'll let the wizard take care of the necessary JOIN statements and preliminary WHERE statement(s), then add/tweak as needed.
_____________________________
3) Borrow someone else's Smart label SQL query and tweak for my environment
There is a plethora of great example Smart labels on ITNinja - take advantage of them (and use the wizard trick I mentioned in point 1 for quick label creation). Just be sure to test them in the MySQL Query Browser to make sure results are what you expect before actually building the labels.
_____________________________
Tip - Use the Smart label wizard to build baseline SQL report queries
Here's another tip - you can use your Smart label queries to build SQL Reports and vice versa (with minor tweaking - I'd recommend specifying columns for the Reports). Something to keep in mind, if you'd like to have live results as well as report-formatted results. Just pop a Smart label SQL query into the MySQL Query Browser and run to see for yourself. This works especially well for generating baseline queries that require JOIN statements to multiple tables - something that isn't possible with the report wizard. For example, here's a baseline query created using the Smart label wizard that would be impossible to construct with the report wizard, as the criteria require pulling data from three tables (MACHINE, SOFTWARE, LABEL):
Create Smart Label wizard criteria:
IP Address contains 192.168.1. *and* (uses MACHINE table)
Software Titles contains Adobe Reader *and* (uses SOFTWARE table)
User Full Name contains john *and* (uses MACHINE table)
Label Names contains laptop (uses LABEL table)
Smart label query:
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((((( (1 in (select 1 from ORG1.MACHINE M2 where M2.ID = MACHINE.ID
and M2.IP like '%192.168.1.%'
union select 1 from ORG1.MACHINE_NICS
where MACHINE.ID = MACHINE_NICS.ID
and MACHINE_NICS.IP like '%192.168.1.%')) )
AND (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Adobe Reader%')) )
AND USER_FULLNAME like '%john%')
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%laptop%')) ))
Putting this into the MySQL Query Browser will show you all of the columns, at which point you can select exactly what you want. For example, here's a minor revision to the main SELECT query that only displays the user, machine name, IP address, model and service tag columns for machines matching the above criteria:
select USER, NAME as MACHINE, IP as IP_ADDRESS,
CS_MODEL as MODEL, BIOS_SERIAL_NUMBER as SERVICE_TAG
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((((( (1 in (select 1 from ORG1.MACHINE M2 where M2.ID = MACHINE.ID
and M2.IP like '%192.168.1.%'
union select 1 from ORG1.MACHINE_NICS
where MACHINE.ID = MACHINE_NICS.ID
and MACHINE_NICS.IP like '%192.168.1.%')) )
AND (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Adobe Reader%')) )
AND USER_FULLNAME like '%john%')
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%laptop%')) ))
It should be noted that the JOIN statements to the SOFTWARE and LABEL tables are in the subqueries, so actually calling columns from the SOFTWARE and LABEL tables would require adding the usual JOIN statements to the main query. Depending on your requirements, though, this could be useful (or at least give you something to start with - i.e. see which columns are necessary for the JOIN statements).
__________________________________________________________________________________
(22) Machine Label Examples
__________________________________________________________________________________
As with the other labels, I'm listing these alphabetically to make jumping to a section a bit easier, so some of the dependent labels will actually be analyzed later - when this happens, I'll let you know where to look. Hopefully after you read/skim through these, you'll get a pretty good feel of how to combine existing labels with other criteria (including other labels) to further your ordering of everything, as that's how the bulk of the machine labels here are constructed. For labels created using the Create Smart Label wizard, I'll list the criteria and the corresponding SQL statement.
One thing I'd like to mention is that there are (in many cases) multiple ways to create a Smart label with the same base functionality (i.e. matches). A number of my machine labels below were built at different "organizational periods", where (when inspiration hit) I would go on a label constructing run. Breaking them all down and revisiting the criteria I used, I see many places where I could (would) change certain criteria for a more uniform approach through all labels (if I were rebuilding them all from scratch). But, similar to the wizard-generated SQL queries being a bit verbose, they are functionally identical and interesting from both variation and historical points of view, so I am just leaving well enough alone and analyzing them in their present forms.
In regards to the SQL queries, I've adjusted formatting to (hopefully) make them a bit easier to follow and digest. The Create Smart Label wizard does a great job of creating working queries, but they aren't always in a form that is easily read. Also, the bulk of many of the wizard-generated queries consist of multiple JOIN statements and subquery statements that allow results to be pulled from multiple tables - honestly, these could be written differently (more condensed in many cases), but why spend the time when the wizard handles building the queries and they work just fine. Finally, I've left the extra parentheses in the WHERE statements (that the wizard generates) in place for consistency, since this is what you'll see when you use the wizard to construct queries with multiple criteria. In most cases these could be removed, but again - what's the point (aside from aesthetics) when the functionality is the same.
One last note regarding my mixed usage of "contains" ("like" in the SQL query) and "=" in the wizard criteria when targeting label names - both are functionally the same in my environment, but I (technically speaking) should have used "=" for absolute preciseness. This is one area where my typically compulsive consistency lapsed... If you are setting these up in your own environment, I strongly recommend using "=" unless you need to match multiple variations with a "like" string - just be sure to test. Also, in case this is new for anyone - wherever you see "rlike" in a query, this is the SQL version of the wizard's "matches REGEX" criteria.
_____________________________
_____________________________
Machine - Age Labels
_____________________________
machine age > 01 year
machine age > 02 years
machine age > 03 years
machine age > 04 years
machine age > 05 years
machine age > 06 years
machine age > 07 years
machine age > 08 years
machine age > 09 years
machine age > 10 years
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
JOIN KBSYS.DELL_ASSET
ON KBSYS.DELL_ASSET.SERVICE_TAG =MACHINE.BIOS_SERIAL_NUMBER
where ((KBSYS.DELL_ASSET.SHIP_DATE < CURDATE() - INTERVAL 5 YEAR))
_________
As the label names suggest, these labels target machines over a certain age. To adjust the age, just change the number in the "INTERVAL # YEAR" statement. This example query picks up machines older than 5 years.
_____________________________
_____________________________
Machine - Hardware Labels
_____________________________
client hdd space < 01gb
client hdd space < 02gb
client hdd space < 05gb
client hdd space < 10gb
client hdd space < 15gb
client hdd space < 20gb
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 in (select 1 from ORG1.MACHINE_DISKS
where MACHINE.ID = MACHINE_DISKS.ID
and MACHINE_DISKS.DISK_FREE < '2')) )
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%client%')) )
_________
This is the first example of how I'm using the Smart label wizard to leverage and refine existing labels. These "free hard drive space" labels were wizard built, using Disk Free (G) and Label Names for the criteria. Please see the Machine - Role Labels further below for the "client" label setup.
The wizard criteria and actual statements that target these criteria are:
Disk Free (G) where... MACHINE_DISKS.DISK_FREE < '2'
Label Names and... LABEL.NAME like '%client%'
_____________________________
client ram < 1gb
client ram = 512mb
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( RAM_TOTAL < '1024')
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%client%')) )
_________
Same basic idea as the "free hard drive space" labels, but using Total RAM (MB) and Label Names for the criteria. The wizard criteria and actual statements that target these criteria are:
Total RAM (MB) where... RAM_TOTAL < '1024'
Label Names and... LABEL.NAME like '%client%'
_____________________________
_____________________________
Machine - Location
Site - Client Desktop PCs Labels
East Canton desktop
Greensboro desktop
Hammond desktop
Hillsborough desktop
King Of Prussia desktop
Marelan desktop
Moulton desktop
Oak Hill desktop
Pittsburgh desktop
Remote desktop
Santa Fe Springs desktop
Shenango desktop
Tarentum desktop
Testnet desktop
UK desktop
Warren desktop
Warren Warehouse desktop
Wellston desktop
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%East Canton computers%')) )
AND (1 not in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME rlike 'server|control')) )
AND CHASSIS_TYPE = 'desktop'))
_________
These labels use a combination of three Smart label criteria to list all desktop machines for a location (i.e. no laptops, servers or control PCs). The criteria are as follows (with the first changing for each location):
Label Names where... LABEL.NAME like '%East Canton computers%'
Label Names and... LABEL.NAME rlike 'server|control'
Chassis Type and... CHASSIS_TYPE = 'desktop'
Please note that my location (i.e. East Canton) and role (i.e. server) labels in this example are LDAP labels, but they could be setup as Smart (or manual) labels in your own org - it's just a matter of finding the criteria to target, confirming the results, and then tweaking as necessary to get exactly what you want/need.
_____________________________
_____________________________
Machine - Location
Site - Client Laptops Labels
East Canton laptops
Greensboro laptops
Hammond laptops
Hillsborough laptops
King Of Prussia laptops
Marelan laptops
Moulton laptops
Oak Hill laptops
Pittsburgh laptops
Remote laptops
Santa Fe Springs laptops
Shenango laptops
Tarentum laptops
Testnet laptops
UK laptops
Warren laptops
Warren Warehouse laptops
Wellston laptops
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%East Canton computers%')) )
AND (1 not in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME rlike 'server|control')) )
AND CHASSIS_TYPE = 'laptop'))
_________
Same idea as the previous location-desktop labels, but these target laptops. The same basic combination of three Smart label criteria are used to list all laptops for a location (i.e. no desktops, servers or control PCs). The criteria are as follows (with the first changing for each location):
Label Names where... LABEL.NAME like '%East Canton computers%'
Label Names and... LABEL.NAME rlike 'server|control'
Chassis Type and... CHASSIS_TYPE = 'laptop'
_____________________________
_____________________________
Machine - Location
Site - Clients (All) Labels
East Canton clients
Greensboro clients
Hammond clients
Hillsborough clients
King Of Prussia clients
Marelan clients
Moulton clients
Oak Hill clients
Pittsburgh clients
Remote clients
Santa Fe Springs clients
Shenango clients
Tarentum clients
Testnet clients
UK clients
Warren clients
Warren Warehouse clients
Wellston clients
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME = 'East Canton computers')) )
AND (1 not in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME rlike 'server|control')) ))
_________
These labels were the baseline for the location-desktops and location-laptops labels - exactly the same, just without filtering on the chassis type:
Label Names where... LABEL.NAME like '%East Canton computers%'
Label Names and... LABEL.NAME rlike 'server|control'
On a side note, I use these labels a lot for more discrete patch reports (so I can get a feel for patching issues related to a specific site and it's replication share).
_____________________________
_____________________________
Machine - Location
Site - Control PCs
East Canton control
Greensboro control
Hammond control
Hillsborough control
King Of Prussia control
Marelan control
Moulton control
Oak Hill control
Pittsburgh control
Remote control
Santa Fe Springs control
Shenango control
Tarentum control
Testnet control
UK control
Warren control
Warren Warehouse control
Wellston control
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME = 'East Canton computers')) )
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME = 'control')) ))
_________
These labels leverage the "control" LDAP label so I can break them down further by location:
Label Names where... LABEL.NAME like '%East Canton computers%'
Label Names and... LABEL.NAME = 'control'
_____________________________
_____________________________
Machine - Location
Site - Servers
East Canton servers
Greensboro servers
Hammond servers
Hillsborough servers
King Of Prussia servers
Marelan servers
Moulton servers
Oak Hill servers
Pittsburgh servers
Remote servers
Santa Fe Springs servers
Shenango servers
Tarentum servers
Testnet servers
UK servers
Warren servers
Warren Warehouse servers
Wellston servers
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME = 'East Canton computers')) )
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME = 'server')) ))
_________
These labels leverage the "server" LDAP label so I can break them down further by location:
Label Names where... LABEL.NAME like '%East Canton computers%'
Label Names and... LABEL.NAME = 'server'
_____________________________
_____________________________
Machine - OS & Type Labels
For these labels, I referenced the machine's Operating System > Name listing in Computers: Detail Item (OS_NAME in the MACHINE table). In cases where there is both an x86 and an x64 version, I use additional criteria of "like x64" or "not like x64".
Aside from listing machines using the View by: > Labels menu, these could also be used for setting up patch schedules (although I presently use separate LDAP labels for that purpose, as I built those first and thought of creating these labels after the fact).
_____________________________
server2k3
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (((OS_NAME like '%Microsoft(R) Windows(R) Server 2003%')
AND OS_NAME not like '%x64%'))
_________
OS Name where... OS_NAME like '%Microsoft(R) Windows(R) Server 2003%'
OS Name and... OS_NAME not like '%x64%'
_____________________________
_____________________________
server2k3x64
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (((OS_NAME like '%Microsoft(R) Windows(R) Server 2003%')
AND OS_NAME like '%x64%'))
_________
OS Name where... OS_NAME like '%Microsoft(R) Windows(R) Server 2003%'
OS Name and... OS_NAME like '%x64%'
_____________________________
_____________________________
server2k8
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (((OS_NAME like '%Microsoft® Windows Server® 2008%')
AND OS_NAME not like '%x64%'))
_________
OS Name where... OS_NAME like '%Microsoft® Windows Server® 2008%'
OS Name and... OS_NAME not like '%x64%'
_____________________________
_____________________________
server2k8r2x64
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (OS_NAME like '%Microsoft Windows Server 2008 R2 %')
_________
OS Name where... OS_NAME like '%Microsoft Windows Server 2008 R2 %'
_____________________________
_____________________________
server2k8x64
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (((OS_NAME like '%Microsoft(R) Windows(R) Server 2003%')
AND OS_NAME not like '%x64%'))
_________
OS Name where... OS_NAME like '%Microsoft(R) Windows(R) Server 2003%'
OS Name and... OS_NAME not like '%x64%'
_____________________________
_____________________________
win7
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( OS_NAME like '%Microsoft Windows 7 Professional x64%'))
_________
OS Name where... OS_NAME like '%Microsoft Windows 7 Professional x64%'
_____________________________
_____________________________
win7-desktop
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%win7%')) )
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%desktop%')) ))
_________
Label Names where... LABEL.NAME like '%win7%'
Label Names and... LABEL.NAME like '%desktop%'
Show me all of my Windows 7 desktops...
_____________________________
_____________________________
win7-laptop
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%win7%')) )
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%laptop%')) ))
_________
Label Names where... LABEL.NAME like '%win7%'
Label Names and... LABEL.NAME like '%laptop%'
Now show me all of my Windows 7 laptops...
_____________________________
_____________________________
winxp
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((OS_NAME like '%xp%'))
_________
OS Name where... OS_NAME like '%xp%'
I took this (condensed) approach due to the OS name being spelled differently for the French version (yes it is - believe it or not).
_____________________________
_____________________________
winxp-desktop
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%xp%')) )
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%desktop%')) ))
_________
Label Names where... LABEL.NAME like '%xp%'
Label Names and ... LABEL.NAME like '%desktop%'
Next up - show me all of my Windows XP desktops...
_____________________________
_____________________________
winxp-eng
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( OS_NAME like '%XP%')
AND (1 not in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME = 'french')) ))
_________
OS Name where... OS_NAME like '%XP%'
Label Names and... 1 not in (where... LABEL.NAME = 'french')
This is one of my earlier Smart labels and the concept of combining labels hadn't really germinated at the point it was created, thus using the typical OS Name like "xp". Also, I set the wizard criteria to Label Names != "french", but the generated SQL code decided to use a "negating" subquery to handle this. Personally, I would have handled the join to the machine label table prior to the WHERE statement, and written the WHERE statement like this (based on the criteria I provided):
WHERE (OS_NAME like '%XP%'
AND LABEL.NAME != 'french')
Not that I'm complaining, since it works just fine either way and I *didn't* have to write the query (which, at the point when I created this label, I would have struggled to do so).
_____________________________
_____________________________
winxp-fr
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( OS_NAME like '%XP%')
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME = 'french')) ))
_________
OS Name where... OS_NAME like '%XP%'
Label Names and... LABEL.NAME = 'french'
_____________________________
_____________________________
winxp-laptop
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%xp%')) )
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%laptop%')) ))
_________
Label Names where... LABEL.NAME like '%xp%'
Label Names and... LABEL.NAME like '%laptop%'
And finally - show me all of my Windows XP laptops...
_____________________________
_____________________________
Machine - Policy Enforcement Labels
These labels help me track machines with slightly complicated policy delineations. The first targets machines that have a policy enforced (via a script) to redirect the users' My Documents folders to their home directories (located on servers), but only when the client machine has a local server and is not a laptop. The second label tracks the remaining client machines, which have the My Documents folders local and use a backup program (SecondCopy driven by a batch file to run the specified profiles and copy up the log file to the users' backup folders for daily review via Windows Grep) to ensure data is still backed up to tape for recovery purposes.
_____________________________
redirectmydocs
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME rlike 'East Canton computers|Greensboro computers|Hammond computers|Marelan computers|Pittsburgh computers|Tarentum computers|Warren computers')) )
AND (1 not in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME rlike 'control|laptop|server')) ))
_________
Label Names where... LABEL.NAME rlike 'East Canton computers|Greensboro computers|Hammond computers|Marelan computers|Pittsburgh computers|Tarentum computers|Warren computers'
Label Names and... LABEL.NAME rlike 'control|laptop|server')
The first label specifies exactly which sites have local file servers with homed user directories, the second excludes the machine types that shouldn't be targeted.
_____________________________
_____________________________
client backups
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%client%')) )
AND (1 not in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME like '%redirectmydocs%')) ))
_________
Label Names where... LABEL.NAME like '%client%'
Label Names and... 1 not in (where... LABEL.NAME like '%redirectmydocs%')
As with the "winxp-eng" label, I specified Label Names != "redirectmydocs" and the wizard generated a "negating" subquery to handle this.
_____________________________
_____________________________
Machine - Role Labels
Here are the frequently utilized and referenced client labels. Most of my role labels are handled by LDAP labels, but I decided to give these a shot using Smart labels (rather than update ADUC computer descriptions *again*) and they worked quite well (and gave me the confidence to continue with the other Smart labels). Note that these (again) use a "negating" subquery to exclude the non-client machine labels.
_____________________________
client
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 not in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME rlike 'control|server')) ))
_________
Label Names where... 1 not in (where... LABEL.NAME rlike 'control|server')
_____________________________
_____________________________
client-english
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 not in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME rlike 'control|server|french')) ))
_________
Label Names where... 1 not in (where... LABEL.NAME rlike 'control|server|french')
For this label I simply included the "french" machine label in the exclusion criteria. This is why I like REGEX statements, as they allow for quick extensibility.
_____________________________
_____________________________
client-french
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 not in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME rlike 'control|server')) )
AND (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT
where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.TYPE != 'hidden'
and LABEL.NAME = 'french')) ))
_________
Label Names where... 1 not in (where... LABEL.NAME rlike 'control|server')
Label Names and... LABEL.NAME = 'french'
_____________________________
_____________________________
Machine - Software Labels
I would imagine of all the labels I'm sharing here (most with the sole intention of sharing the concepts and constructions), these will actually be usable for others as listed - copy/paste away, my friends!
For labels with REGEX statements, I built the main query using the Software Titles wizard criteria (SOFTWARE.DISPLAY_NAME in the query) and a dummy value which I then manually updated with the REGEX statement (SOFTWARE.DISPLAY_VERSION) to target the specific version(s). These should all be current as I'm writing this - I just updated Adobe Flash and Java this past week. If you would like to see a breakdown of the REGEX statements (and how to construct your own), please see this:
Using REGEX in Smart Labels to Find Lower Versioned Software (w/Java Example)
http://www.itninja.com/blog/view/using-regex-in-smart-labels-to-find-lower-versioned-software-w-java-example
For labels without REGEX statements, I would recommend running a search in your Inventory - Software screen using the term(s) specified in the SOFTWARE.DISPLAY_NAME part of the query to verify they will work for you.
_____________________________
current-ar(10.1.4)
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like 'Adobe Reader%'
and SOFTWARE.DISPLAY_VERSION RLIKE '(^10[[.period.]]1[[.period.]]4$)')) ))
_________
Software Titles where... SOFTWARE.DISPLAY_NAME like 'Adobe Reader%'
Software Version Number and... SOFTWARE.DISPLAY_VERSION RLIKE (REGEX statement)
This label lists all machines with Adobe Reader 10.1.4 installed.
_____________________________
_____________________________
current-flash(11.4.402.265)
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Adobe Flash Player%'
and SOFTWARE.DISPLAY_VERSION RLIKE '(^11[[.period.]]4[[.period.]]402[[.period.]]265$)')) ))
_________
Software Titles where... SOFTWARE.DISPLAY_NAME like '%Adobe Flash Player%'
Software Version Number and... SOFTWARE.DISPLAY_VERSION RLIKE (REGEX statement)
This label lists all machines with Adobe Flash 11.4.402.265 installed.
_____________________________
_____________________________
current-java(7u7)
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Java%'
and SOFTWARE.DISPLAY_VERSION RLIKE '(^7[[.period.]]0[[.period.]]70$)')) ))
_________
Software Titles where... SOFTWARE.DISPLAY_NAME like '%Java%'
Software Version Number and... SOFTWARE.DISPLAY_VERSION RLIKE (REGEX statement)
This label lists all machines with Java 7u7 installed.
_____________________________
_____________________________
inst-vpro
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( CS_MODEL like '%e6420%')
AND (1 not in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Management Engine Components%')) ))
_________
System Model where... CS_MODEL like '%e6420%'
Software Titles and... 1 not in (SOFTWARE.DISPLAY_NAME like '%Management Engine Components%')
I played with Intel vPro, which unfortunately turned out to be inadequate for my environment as certain settings cannot be provisioned from the K1000 and have to be configured by hand in the BIOS/setup screen - major bummer. I'm still deploying the prerequisite Intel app with the hopes that this will be addressed in a future version, and this is the label I use to target my Latitude E6420 laptops that don't have it installed (in typical wizard fashion, with a "negating" subquery).
_____________________________
_____________________________
office 2003 pc
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (((( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Microsoft Office Basic Edition 2003%')) )
OR (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Microsoft Office Professional Edition 2003%')) )
OR (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Microsoft Office Small Business Edition 2003%')) ))
_________
Software Titles where... SOFTWARE.DISPLAY_NAME like '%Microsoft Office Basic Edition 2003%'
Software Titles or... SOFTWARE.DISPLAY_NAME like '%Microsoft Office Professional Edition 2003%'
Software Titles or... SOFTWARE.DISPLAY_NAME like '%Microsoft Office Small Business Edition 2003%'
This label targets all machines with some version of Office 2003 installed (and there are a few in my environment). Note the usage of "or" to capture all of the machines.
_____________________________
_____________________________
old-ar(pre-10.1.4)
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like 'Adobe Reader%'
and SOFTWARE.DISPLAY_VERSION RLIKE '(^[0-9][[.period.]])|(^10[[.period.]]0[[.period.]])|(^10[[.period.]]1[[.period.]][0-3]$)' )) ))
_________
Software Titles where... SOFTWARE.DISPLAY_NAME like 'Adobe Reader%'
Software Version Number and... SOFTWARE.DISPLAY_VERSION RLIKE (REGEX statement)
This label lists all machines with versions of Adobe Reader prior to 10.1.4 installed.
_____________________________
_____________________________
old-flash(pre-11.4.402.265)
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Adobe Flash Player%'
and SOFTWARE.DISPLAY_VERSION RLIKE '(^[0-9][[.period.]])|(^10[[.period.]])|(^11[[.period.]][0-3][[.period.]])|(^11[[.period.]]4[[.period.]]([0-3][0-9][0-9]|40[0-1])[[.period.]])|(^11[[.period.]]4[[.period.]]402[[.period.]]([0-1][0-9][0-9]|2[0-5][0-9]|26[1-4])$)')) ))
_________
Software Titles where... SOFTWARE.DISPLAY_NAME like '%Adobe Flash Player%'
Software Version Number and... SOFTWARE.DISPLAY_VERSION RLIKE (REGEX statement)
This label lists all machines with versions of Adobe Flash prior to 11.4.402.265 installed.
_____________________________
_____________________________
old-ie(pre-8)
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Windows Internet Explorer 7%')) )
AND (1 not in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Windows Internet Explorer 8%')) ))
_________
Software Titles where... SOFTWARE.DISPLAY_NAME like '%Windows Internet Explorer 7%'
Software Titles and... 1 not in (SOFTWARE.DISPLAY_NAME like '%Windows Internet Explorer 8%')
This label lists all machines that have Internet Explorer 7 installed, but not Internet Explorer 8.
_____________________________
_____________________________
old-java(pre-7u7)
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
WHERE ((( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and (SOFTWARE.DISPLAY_NAME like '%Java(TM)%'
and SOFTWARE.DISPLAY_VERSION RLIKE '(^[0-6][[.period.]])|(^7[[.period.]]0[[.period.]]([0-6][0-9])$)'))) )
OR (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME rlike 'Java 2 Runtime Environment|J2SE Runtime Environment 5.0')) ))
_________
Software Titles where... SOFTWARE.DISPLAY_NAME like '%Java(TM)%'
Software Version Number and... SOFTWARE.DISPLAY_VERSION RLIKE (REGEX statement)
Software Titles and... SOFTWARE.DISPLAY_NAME rlike 'Java 2 Runtime Environment|J2SE Runtime Environment 5.0'
This label lists all machines with versions of Java prior to 7u7 installed.
_____________________________
_____________________________
old-wmp(pre-11)
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where ((( OS_NAME like '%XP%')
AND (1 not in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Windows Media Player 11%')) ))
_________
OS Name where... OS_NAME like '%XP%'
Software Titles and... 1 not in (SOFTWARE.DISPLAY_NAME like '%Windows Media Player 11%')
This label lists all Windows XP machines that don't have Windows Media Player 11 installed.
__________________________________________________________________________________
(23) Patch Label Examples
__________________________________________________________________________________
Patching - Patch Group Labels
I use the patch labels for my patch subscriptions and patch schedules. They are constructed so that only non-superseded, critical patches are being downloaded and deployed (to save disk space all around). The OS patch labels target critical OS patches and the application patch labels target specific applications patches using some fairly deep REGEX statements. If you would like a *complete* breakdown of the patching labels, please see this:
K1000 Patching - Setup, Tips & Things I Have Learned (LDAP, Smart Labels, SQL Reports)
http://www.itninja.com/blog/view/k1000-patching-setup-tips-things-i-have-learned-ldap-smart-labels-sql-reports
I'll list the main application patch label query and list the rest for continuity's sake, but I feel that a full explanation is in order for how these were constructed.
_____________________________
patch-apps
select UID from KBSYS.PATCHLINK_PATCH
where (((( (1 in (select 1 from LABEL, PATCHLINK_PATCH_LABEL_JT
where PATCHLINK_PATCH.UID = PATCHLINK_PATCH_LABEL_JT.PATCHUID
and PATCHLINK_PATCH_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.NAME = 'patch-apps-main')) )
AND (1 in (select 1 from LABEL, PATCHLINK_PATCH_LABEL_JT
where PATCHLINK_PATCH.UID = PATCHLINK_PATCH_LABEL_JT.PATCHUID
and PATCHLINK_PATCH_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.NAME = 'patch-apps-regex')) )
AND (1 in (select 1 from LABEL, PATCHLINK_PATCH_LABEL_JT
where PATCHLINK_PATCH.UID = PATCHLINK_PATCH_LABEL_JT.PATCHUID
and PATCHLINK_PATCH_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.NAME = 'patch-apps-adobe')) )
AND (1 in (select 1 from LABEL, PATCHLINK_PATCH_LABEL_JT
where PATCHLINK_PATCH.UID = PATCHLINK_PATCH_LABEL_JT.PATCHUID
and PATCHLINK_PATCH_LABEL_JT.LABEL_ID = LABEL.ID
and LABEL.NAME = 'patch-apps-id-title')) )
_________
Label Names where... LABEL.NAME = 'patch-apps-main'
Label Names and... LABEL.NAME = 'patch-apps-regex'
Label Names and... LABEL.NAME = 'patch-apps-adobe'
Label Names and... LABEL.NAME = 'patch-apps-id-title'
This is the main label used for patching applications, which works by combining the results (exclusions) of the lower order "filter" labels.
_____________________________
_____________________________
patch-apps-adobe
patch-apps-cfilter
patch-apps-client
patch-apps-id-title
patch-apps-main
patch-apps-regex
patch-apps-server
patch-apps-sfilter
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
These are the other patch labels. Please see my patching article for the gory details.
__________________________________________________________________________________
(24) Software Label Examples
__________________________________________________________________________________
Software - Title Labels
As I mentioned earlier, these are mainly placeholders and proof-of-concept for more to come. I am using the "office 2003" label to drive some reports for license counts, but that's about it right now.
_____________________________
adobe reader
SELECT ID
FROM SOFTWARE
WHERE (SOFTWARE.DISPLAY_NAME like 'Adobe Reader%')
_________
Display Name (Titles) where... SOFTWARE.DISPLAY_NAME like 'Adobe Reader%'
This label lists all versions of Adobe Reader. I created this label for the simple fact that I got sick of typing "adobe reader" in the Search box while trying to standardize machines on the latest version. Prior to the K1000, this had been infeasible.
_____________________________
_____________________________
office 2003
SELECT ID
FROM SOFTWARE
WHERE ((( SOFTWARE.DISPLAY_NAME like '%office%')
AND SOFTWARE.DISPLAY_NAME like '%2003%')
AND SOFTWARE.DISPLAY_NAME not rlike 'update|pack|components|viewer')
_________
Display Name (Titles) where... SOFTWARE.DISPLAY_NAME like '%office%'
Display Name (Titles) and... SOFTWARE.DISPLAY_NAME like '%2003%'
Display Name (Titles) and... SOFTWARE.DISPLAY_NAME not rlike 'update|pack|components|viewer'
This is my attempt to only list Microsoft Office 2003 versions, without all of the extras (packs, components, etc) that show up. Works well.
__________________________________________________________________________________
(X) Conclusion
Well that was quite the marathon... If you read (or meaningfully skimmed) all of that, I would hope you have a better handle on labels now. As usual, if I got anything terribly wrong, if you need further clarification on any points or if you picked up a useful tip or SQL query, please let me know in the comments.
Hope that helps somebody out there!
John
73 pages.
John, this is the third primer I've stolen from you and I owe you so many boxes of chocolates and edible arrangement orders.
Thank you, sir. - Wildwolfay 11 years ago
John - jverbosk 11 years ago