/build/static/layout/Breadcrumb_cap_w.png

Smart Label based on OS Installation Date

There may be times when you need to group devices by installation date.   A practical example might be that you want to deploy an image to a system that has not be re imaged in 2 years.  Another reason to use this might be if you are deploying images and you want to run an aggressive patch schedule on newly imaged systems.  Since we cannot rely solely on K1000 agent creation date as this would apply to new agent installs and not only new machines, we need to separate the machines that were just re imaged from those that received the agent recently.

The problem lies in the smart label drop downs.  Not every inventory field is shown when building a smart label and the inventory item OS Install Date is one of those items.  In order to build a smart label for this we can use a simple SQL query pasted in over a new smart label.

First, build a new smart label from devices.  It does not matter what you use since we will wipe out the query.  Go to Label Management --> Smart Labels and click on the name of the smart label that was created.

JG5TGv.png

Click on Edit SQL and paste in the following SQL statement:

SELECT MACHINE.NAME AS SYSTEM_NAME, OS_INSTALLED_DATE as TOPIC_ID FROM MACHINE  WHERE (((TIMESTAMP(MACHINE.OS_INSTALLED_DATE) <= NOW() AND TIMESTAMP(MACHINE.OS_INSTALLED_DATE) > DATE_SUB(NOW(),INTERVAL 48 HOUR))))

Save the label.  This will automatically label devices that were imaged within the last 48 hours.

Now we can use that label in a patch schedule that aggressively patches machines in that label while keeping older machines out of that patch schedule.

We can alter the time query a bit to find whatever devices we need.  For example devices that were imaged over 2 years ago would use this query:

SELECT MACHINE.NAME AS SYSTEM_NAME, OS_INSTALLED_DATE as TOPIC_ID FROM MACHINE  WHERE ((TIMESTAMP(MACHINE.OS_INSTALLED_DATE) < DATE_SUB(NOW(),INTERVAL 2 YEAR)))

Happy labeling!
Posted 8 years ago 3212 views

Comments

  • Using OS_INSTALLED_DATE isn't always reliable depending on how you setup your machines. If you are using an image to deploy your systems then the install date will be the date that the OS was installed on your master, not when the image was applied. If you are using a scripted install, then using OS_INSTALLED_DATE will be accurate. - chucksteel 8 years ago
    • Very true. I would love to assume people are always using a K2000 to install the OS as well. If that field is not good because a system image was used then we might have to create custom fields during image deployment, maybe setting a date when it is imaged in the registry somewhere and use a CI to pull that into inventory. We could then use that date potentially to base our label on.

      UPDATE...Installed a system image that was sysprepped and the OS installation date is updated to today. So it seems the only instance that would cause this method to fail is if you deployed the image without sysprep which is against Microsoft best practices. - jamie_kace 8 years ago
  • Thanks. This will come in handy. Mine look accurate and we do sysprep. - rockhead44 8 years ago
  • This is exactly what I have been looking for, but it isn't working for me.

    I noticed looking at the db that the OS_INSTALLED_DATE isn't listed as a timestamp. Could this be an issue? Or what else may be causing it? I even changed the hours to 2000 hours, which should include a lot of machines but it doesn't. - crrussell3 7 years ago
    • I looked at my DB as well and you are correct it is not listed as a timestamp field. however, i just created a new VM 3 days ago, used the following query in a smart label:

      SELECT MACHINE.NAME AS SYSTEM_NAME, OS_INSTALLED_DATE as TOPIC_ID FROM MACHINE WHERE ((TIMESTAMP(MACHINE.OS_INSTALLED_DATE) > DATE_SUB(NOW(),INTERVAL 1 WEEK)))

      Forced the new VM to check in, and a couple of other older machines for good measure. The new VM was the only one to pick up this label. So it seems regardless of the field type in SQL it does seem to work. I suspect something either in the syntax or maybe the label you setup. Make sure when you create the manual label that is is used for Devices. And then create a new smart label, assign it to the manual label, and paste in your query.

      Might be worth a call to support if the label is not working right as all my tests (on 6.4.120756 of K1000) work perfectly. - jamie_kace 7 years ago
      • I have verified and recreated the label with same result. I will give support a call to see what is going on. Thanks! - crrussell3 7 years ago
      • When in doubt, restart. Restarting the K1000 seems to have resolved the issue. I was getting another wierd issue when trying to view software records. Going to have Support look into it none the less to make sure everything is good. - crrussell3 7 years ago
  • Thanks for the update @crrussell3 ...glad to know a little reboot cleared things up. Good idea to have support take a peek though. - jamie_kace 7 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ