/build/static/layout/Breadcrumb_cap_w.png
06/29/2017 1009 views
I am not a SQL person nor do I have one on staff. I normally use the Wizard when creating reports, but I can not get what I want with it. I was wondering if a SQL expert out there could give me a hand creating this report in the K1000. 

What I would like is a report that has System model (header break) then sorted by Shipped date (from Warranty information) to also include System name, Date created, User name, User Full name and Service Tag number. 

I can almost get what I want through the Wizard but it puts multiple entries for the same device that have to be expanded, I would like to not have those multiple entries. 

Thanks for any help in advance. 
Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • Some of the fields you are mentioning are custom fields that exist only on your K1000. Without access to your K1000 DB people here could only give you a general outline.

    Generally the K1000 stores data from different sections in different DB tables. The wizard is limited in creating SQL queries that pull data from each table and display in a single table output. Thus you get the nested tables when you have a report that has a subtopic.

    Is all of the info you want stored under the device asset or device inventory page?

Answer Chosen by the Author

2
This report will only work for Dell computers:
SELECT DISTINCT(MACHINE.NAME), 
MACHINE.OS_NAME, 
MACHINE.CS_MODEL, 
MACHINE.CS_MANUFACTURER, 
DA.SHIP_DATE AS "Ship Date",
MACHINE.CREATED,
MACHINE.USER,
MACHINE.USER_FULLNAME
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 CS_MODEL, `Ship Date`, MACHINE.NAME
When creating the report enter CS_MODEL for "Break on Columns"

Answered 06/30/2017 by: chucksteel
Red Belt

  • That worked perfect. Thank you!