/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


KACE SQL reporting question

03/22/2017 1325 views
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/FieldField Nameas Field Type
ASSET_DATA_5.FIELD_10003LocationASSET_1
ASSET_DATA_5.FIELD_10002DepartmentASSET_2
ASSET_DATA_5.FIELD_10006Property NameASSET_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

Comments


All Answers

1
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

Answered 03/22/2017 by: JasonEgg
Red Belt

  • Thanks! I will try this out tomorrow.
  • That didn't seem to work, but I am going to tweak it and see if I am successful.
    • Are you getting an error or no results?
      • 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")
  • 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
    • 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
      • Ah, I now realize you have Location, Department, and Property as assets themselves, as opposed to simply fields for computer assets
    • 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.
1
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

Answered 03/23/2017 by: JasonEgg
Red Belt

  • 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")
    • 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)
      • 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?
    • 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
      • 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
      • Not exactly sure how I can mark this answered. Newb status.

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