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
Answers (1)
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
