KACE SQL reporting question
Hello,
I have a report that gives me a single line entry for my expiring dell warranties. What I am trying to accomplish is to also show custom asset info on each line "Location", "Department", and "Property Name" to be able to send each department head info on what machines they need to replace. I have this SQL script below was obtained through another post and am not sure how to add the "Location", "Department", and "Property Name" to the report. I also ran a report to show me what the Table/Field, Field Name, and Field Type are. Any help is appreciated I am not fluent enough in SQL to begin to write the code.
| Table/Field | Field Name | as Field Type |
| ASSET_DATA_5.FIELD_10003 | Location | ASSET_1 |
| ASSET_DATA_5.FIELD_10002 | Department | ASSET_2 |
| ASSET_DATA_5.FIELD_10006 | Property Name | ASSET_10 |
SELECT DISTINCT(MACHINE.NAME),
USER_LOGGED,
DA.SERVICE_TAG,
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"
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
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
JasonEgg
7 years ago
How about this:
SELECT DISTINCT(MACHINE.NAME),
USER_LOGGED,
DA.SERVICE_TAG,
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",
AD5.FIELD_10003 as 'Location',
AD5.FIELD_10002 as 'Department',
AD5.FIELD_10006 as 'Property Name'
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
LEFT JOIN ASSET A on MACHINE.ID = A.MAPPED_ID OR A.NAME = MACHINE.BIOS_SERIAL_NUMBER
LEFT JOIN ASSET_DATA_5 AD5 on AD5.ID = A.ID
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
Probably can be sped up by modifying the joins but depends on your environment
Comments:
-
Thanks! I will try this out tomorrow. - breakfix 7 years ago
-
That didn't seem to work, but I am going to tweak it and see if I am successful. - breakfix 7 years ago
-
Are you getting an error or no results? - JasonEgg 7 years ago
-
Here is the error:
mysqli error: [1054: Unknown column 'AD5.FIELD_10003' in 'field list'] in EXECUTE( "SELECT DISTINCT(MACHINE.NAME), USER_LOGGED, DA.SERVICE_TAG, 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", AD5.FIELD_10003 as 'Location', AD5.FIELD_10002 as 'Department', AD5.FIELD_10006 as 'Property Name' 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 LEFT JOIN ASSET A on MACHINE.ID = A.MAPPED_ID OR A.NAME = MACHINE.BIOS_SERIAL_NUMBER LEFT JOIN ASSET_DATA_5 AD5 on AD5.ID = A.ID WHERE MACHINE.CS_MANUFACTURER like 'Dell%' GROUP BY MACHINE.NAME ORDER BY "Ship Date", MACHINE.NAME LIMIT 0") - breakfix 7 years ago
-
Are you sure "Location" is located in ASSET_DATA_5.FIELD_10003 ? That's the column it can't find (In the query, I have made and "alias" for the table, which is AD5). You can view the whole table with this query:
SELECT * FROM ASSET_DATA_5 - JasonEgg 7 years ago-
I'm not exactly sure that Location corresponds to that column as I found a SQL query to return that info. I can view the SQL that shows me the info I want from the assets Location, Property, and Department. Here is that SQL
SELECT A10003.NAME AS FIELD_10003, A10002.NAME AS FIELD_10002, GROUP_CONCAT(DISTINCT A10006.NAME SEPARATOR '\n') AS FIELD_10006 FROM MACHINE LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_ASSOCIATION J10003 ON J10003.ASSET_ID = ASSET.ID AND J10003.ASSET_FIELD_ID=10003
LEFT JOIN ASSET A10003 ON A10003.ID = J10003.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_ASSOCIATION J10002 ON J10002.ASSET_ID = ASSET.ID AND J10002.ASSET_FIELD_ID=10002
LEFT JOIN ASSET A10002 ON A10002.ID = J10002.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_ASSOCIATION J10006 ON J10006.ASSET_ID = ASSET.ID AND J10006.ASSET_FIELD_ID=10006
LEFT JOIN ASSET A10006 ON A10006.ID = J10006.ASSOCIATED_ASSET_ID GROUP BY MACHINE.ID ORDER BY FIELD_10003 - breakfix 7 years ago-
Ah, I now realize you have Location, Department, and Property as assets themselves, as opposed to simply fields for computer assets - JasonEgg 7 years ago
-
Also I am currently working with KACE support as my kbox isnt allowing any mySQL connections. I am assuming it might be a little easier to view all fields through there. - breakfix 7 years ago
-
much easier, yes! - JasonEgg 7 years ago
Posted by:
JasonEgg
7 years ago
This is copypasta from your query above and my former query, so it's not optimized but it might do the trick:
SELECT MACHINE.NAME,
USER_LOGGED,
DA.SERVICE_TAG,
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",
A10003.NAME AS "Location",
A10002.NAME AS "Department",
GROUP_CONCAT(DISTINCT A10006.NAME SEPARATOR '\n') AS "Property Name"
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
LEFT JOIN ASSET A ON MACHINE.ID = A.MAPPED_ID OR A.NAME = MACHINE.BIOS_SERIAL_NUMBER
LEFT JOIN ASSET_ASSOCIATION J10003 ON J10003.ASSET_ID = A.ID
AND J10003.ASSET_FIELD_ID = 10003
LEFT JOIN ASSET A10003 ON A10003.ID = J10003.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J10002 ON J10002.ASSET_ID = A.ID
AND J10002.ASSET_FIELD_ID = 10002
LEFT JOIN ASSET A10002 ON A10002.ID = J10002.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J10006 ON J10006.ASSET_ID = A.ID
AND J10006.ASSET_FIELD_ID = 10006
LEFT JOIN ASSET A10006 ON A10006.ID = J10006.ASSOCIATED_ASSET_IDWHERE MACHINE.CS_MANUFACTURER LIKE 'Dell%'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
EDIT: I added the information I forgot initially
Comments:
-
I really appreciate all of your assistance on this but unfortunately that did not work.
mysqli error: [1054: Unknown column 'A10006.NAME' in 'field list'] in EXECUTE( "SELECT MACHINE.NAME, USER_LOGGED, DA.SERVICE_TAG, 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", A10003.NAME AS "Location", A10002.NAME AS "Department", GROUP_CONCAT(DISTINCT A10006.NAME SEPARATOR '\n') AS "Property Name" 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 LEFT JOIN ASSET A ON MACHINE.ID = A.MAPPED_ID OR A.NAME = MACHINE.BIOS_SERIAL_NUMBER LEFT JOIN ASSET_ASSOCIATION J10003 ON J10003.ASSET_ID = A.ID AND J10003.ASSET_FIELD_ID = 10003 LEFT JOIN ASSET A10003 ON A10003.ID = J10003.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_ASSOCIATION J10002 ON J10002.ASSET_ID = A.ID AND J10002.ASSET_FIELD_ID = 10002 LEFT JOIN ASSET A10002 ON A10002.ID = J10002.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_ASSOCIATION J10006 ON J10006.ASSET_ID = A.ID AND J10006.ASSET_FIELD_ID = 10006 WHERE MACHINE.CS_MANUFACTURER LIKE 'Dell%' GROUP BY MACHINE.NAME ORDER BY "Ship Date", MACHINE.NAME LIMIT 0") - breakfix 7 years ago-
Try removing the line that starts "GROUP_CONCAT..." (and the preceding comma). FYI you can "comment out" code in SQL with "# [code line]" or "-- [code line]" or "/* [code block] */" (w/o quotes) - JasonEgg 7 years ago
-
Removing the line "GROUP_CONAT..." and the preceding comma worked. It just does not show "Property Name" as I assume the GROUP_CONCAT line would try to grab that info? - breakfix 7 years ago
-
I missed a line that could fix it. Keep the GROUP_CONCAT. Add this line right before the WHERE clause:
LEFT JOIN ASSET A10006 ON A10006.ID = J10006.ASSOCIATED_ASSET_ID - JasonEgg 7 years ago-
Incredible! Thank you for your time on this. Really made my life a lot easier. Here is the tested working SQL.
SELECT MACHINE.NAME,
USER_LOGGED,
DA.SERVICE_TAG,
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",
A10003.NAME AS "Location",
A10002.NAME AS "Department",
GROUP_CONCAT(DISTINCT A10006.NAME SEPARATOR '\n') AS "Property Name"
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
LEFT JOIN ASSET A ON MACHINE.ID = A.MAPPED_ID OR A.NAME = MACHINE.BIOS_SERIAL_NUMBER
LEFT JOIN ASSET_ASSOCIATION J10003 ON J10003.ASSET_ID = A.ID
AND J10003.ASSET_FIELD_ID = 10003
LEFT JOIN ASSET A10003 ON A10003.ID = J10003.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J10002 ON J10002.ASSET_ID = A.ID
AND J10002.ASSET_FIELD_ID = 10002
LEFT JOIN ASSET A10002 ON A10002.ID = J10002.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J10006 ON J10006.ASSET_ID = A.ID
AND J10006.ASSET_FIELD_ID = 10006
LEFT JOIN ASSET A10006 ON A10006.ID = J10006.ASSOCIATED_ASSET_ID
WHERE MACHINE.CS_MANUFACTURER LIKE 'Dell%'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME - breakfix 7 years ago -
Not exactly sure how I can mark this answered. Newb status. - breakfix 7 years ago
