/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Creating an Inventory Report for aging

09/21/2016 820 views
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

Comments


All Answers

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



Answered 09/22/2016 by: chucksteel
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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