/build/static/layout/Breadcrumb_cap_w.png

Created report to show label computers are in plus warranty/expiration date but report pulling computers in label I don't want

I want this report to only show computers for facstaff - it is also pulling computers I don't want because they are also in the fac/staff OU

I want Machines-Facstaff 

I don't want Machines-Conference Rooms or Machines-Student GA Workers

I can get this to work if I don't have the warranty information in the report - any help would be appreciated

SELECT DISTINCT(MACHINE.NAME), 

MACHINE.CS_MODEL, 

MACHINE.CS_MANUFACTURER, 

MACHINE.Chassis_Type,

MACHINE.Ram_total,

CONCAT(LABEL.NAME),

DA.SHIP_DATE AS "Ship Date",

DATE_ADD(DA.SHIP_DATE, INTERVAL 4 YEAR) as "Replacement Date"

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

JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID

JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID

WHERE MACHINE.CS_MANUFACTURER like 'Dell%'

AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID  

AND LABEL.TYPE <> 'hidden'

and LABEL.NAME like '%Machines - FacStaff%'

ORDER BY "Replacement Date", LABEL.NAME


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: JasonEgg 3 years ago
Red Belt
0

Top Answer

I just ran the query against my instance using MySQLWorkbench and supplying one of my label names and the results look okay: only got devices from that label. Does the query work as you intend if you run it through Workbench or Toad?

A few notes:

I'm guessing you meant "CONCAT(LABEL.NAME)" to mean "in this column list all the labels applied to this device"?  But for that, you'll need to use "GROUP_CONCAT()" and a "GROUP BY" clause. As it stands, you can remove CONCAT entirely since it's not doing anything.

In the WHERE clause you have "MACHINE_LABEL_JT.LABEL_ID = LABEL.ID" but this is unnecessary since you already stated it in the ON clause when joining the LABEL table.

In the ORDER BY clause you use "Replacement Date" but SQL is interpreting that as a literal string so it won't change the order at all. To order by that field, alias the field without quotes and use that alias in the ORDER BY clause. So you could replace "Replacement Date" with Replacement_Date 


Comments:
  • Yeah this didn't work I believe part of the problem is with our AD structure and how the labels are named. I am going to recreate a few labels to test and see if that will work. Thanks so much for the answer appreciate it - scarpent 3 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