"Inventory fun" follow-up... AKA Kace's built-in service tag and warranty report

Thu 02 February 2012

Back in September, I wrote a script to grab Dell machines' warranty information based on their service tags, which I had retrieved from Kaseya or LANSweeper. A pretty nifty trick, or so I thought...

Since then, I've been introduced to Kace -- a suite of Dell tools for inventory management, scripting, software and patch deployment, and ghosting. Think of it as a solution that offers the functionality of your FOG server, LANSweeper and Kaseya.

The Kace solution is actually divided in two parts: one component handles inventory, application and update deployment, and scripting, while the other component handles ghosting and driver management. A "component", in this context, can be a piece of hardware (a physical server that you connect to your LAN - the O/S is a custom Unix distro) or simply a virtual machine (a VMWare application which can run on your existing ESX or ESXi box). The config is rather light -- the only thing these devices need is storage space. For those of you that are looking for a free / SOHO-level solution for all your sysadmin  needs: stick to OSCInventory, Zabbix, and FOG... These things have a price tag.  However, though not free, these are well worth it in my opinion.

I digress. I've been porting a lot of my existing stuff to Kace recently; this week, I'm working on the inventory report from September. It turns out that my work is pretty much done: perhaps unsurprisingly, Kace already has a report for extracting machine names, service tags and expiry dates. They have two boiler-plate reports, dubbed "Dell Warranty Expired" and "Dell Warranty Expires in the next 60 days", which dishes out all the information you may need in HTML, CSV or TXT format.

In point of fact, I needed something a bit more customized; I don't actually need the full warranty information but rather the date the warranty expires. This is because with our clients, the machines are amortized from the moment we get them to the moment they're no longer covered under warranty.

The nice thing about Kace is that you can take a boiler-plate report, duplicate it and change the SQL request directly, like with LANSweeper. This makes reporting a cinch. Here's my final report for all machines on my campus:

SELECT DISTINCT M.NAME AS MACHINE_NAME,M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE, M.USER_LOGGED AS LAST_LOGGED_IN_USER,
DW.END_DATE AS EXPIRATION_DATE
FROM KBSYS.DELL_WARRANTY DW
LEFT JOIN KBSYS.DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
LEFT JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG)
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
AND DW.END_DATE = (SELECT MAX(END_DATE) FROM KBSYS.DELL_WARRANTY DW2 WHERE DW2.SERVICE_TAG=DW.SERVICE_TAG AND DW2.SERVICE_LEVEL_CODE=DW.SERVICE_LEVEL_CODE);

It gives you a nice simple list with the machine name, model, service tag, shipment date, warranty expiry date, and the user that last logged on to the system.  Cool eh? Or maybe I'm just easily impressed. Regardless, it saves me time... Yay!

blogroll

social