Creating an Inventory Report for aging
Trying to create a report that will give me Machine name, Model, Service tag, Ship date and last user logged in. Also might want to include a label as we use LDAP labels for our machine groups. Want to to run to an excel file, I've had little luck other than getting a couple reports to run, merge them and clean up the spreadsheet. Also found that for some reason, certain devices that show up in inventory don't appear in some of the reports I've run that are close to this. Want to run this so we can determine a refresh strategy but not sure what I have is good seeing it's leaving out specific items. I should also note, I'd like the output to have the specific fields to list as headers at top of spreadsheet, most current reports I've run tend to bunch all of the info into a block of cells in a column. Any ideas?
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
Here is the report that we use, it includes warranty expiration date.
SELECT DISTINCT(MACHINE.NAME),
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
DA.SHIP_DATE AS "Ship Date",
MAX(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level",
MACHINE.USER_LOGGED
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
To include a label it helps if you have them in a label group and it requires a couple of joins. Here are the joins that I use to get labels in our Departments label group which has an ID of 258:
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = M.ID)
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')
LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 258)
The first join gets you all of the label IDs assigned to the machine.
The second join gets you the actual labels.
The third join gets the label groups for those labels and limits to just the Departments label group (258 on my appliance).
You also need to add a where statement to limit the label group again for some reason
WHERE LABEL_LABEL_JT.LABEL_ID = 258
You can then select the label in the select clause with LABEL.NAME as Department.
