/build/static/layout/Breadcrumb_cap_w.png

Creating a Report using SQL

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. 

1 Comment   [ + ] Show comment
  • 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? - DirtySoc 6 years ago

Answers (1)

Answer Summary:
Posted by: chucksteel 6 years ago
Red Belt
2

Top Answer

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"


Comments:
  • That worked perfect. Thank you! - DJSlater 6 years ago

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