/build/static/layout/Breadcrumb_cap_w.png
06/03/2019 177 views

I need assistance adding three fields from a Kace wizard report to SQL report.  Below is a copy of each of the SQL statements. The fir

SELECT A23.NAME AS FIELD_23, ASSET_DATA_5.FIELD_31 AS FIELD_31, ASSET_DATA_5.FIELD_264 AS FIELD_264, A83.NAME AS FIELD_83, ASSET_DATA_5.FIELD_25 AS FIELD_25  FROM ASSET_DATA_5 LEFT JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN MACHINE M ON ASSET.MAPPED_ID = M.ID and ASSET.ASSET_TYPE_ID = 5 LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID  LEFT JOIN ASSET_ASSOCIATION J23 ON J23.ASSET_ID = ASSET.ID AND J23.ASSET_FIELD_ID=23

                             LEFT JOIN ASSET A23 ON A23.ID = J23.ASSOCIATED_ASSET_ID

                             LEFT JOIN ASSET_DATA_1 AD23 ON AD23.ID = A23.ASSET_DATA_ID

                              LEFT JOIN ASSET_ASSOCIATION J83 ON J83.ASSET_ID = ASSET.ID AND J83.ASSET_FIELD_ID=83

                             LEFT JOIN ASSET A83 ON A83.ID = J83.ASSOCIATED_ASSET_ID

                             LEFT JOIN ASSET_DATA_3 AD83 ON AD83.ID = A83.ASSET_DATA_ID

                                ORDER BY FIELD_23



Current SQL statement below and we need to add from the top statement. Building  Location, Status, Costcenter needs to add to below SQL statement. 


select distinct UCASE(m.NAME) as NAME, m.USER_FULLNAME, m.OS_NAME, m.CS_MODEL, w.SERVICE_TAG, w.SHIP_DATE, DATEDIFF(Now(), w.SHIP_DATE) as Days_since_ship, m.chassis_type, m.last_sync


 from MACHINE as m, DELL_ASSET as w


 where m.BIOS_SERIAL_NUMBER = w.SERVICE_TAG 

 order by Days_since_ship DESC

1 Comment   [ + ] Show comment

Comments

  • I would suggest using MySQL Workbench to browse the database and determine which fields are which.

All Answers

0

Since the report generated by the wizard is more complicated, it will be easier to add the columns you need from the second. The only table that isn't already available is the DELL_ASSET table, so we'll need to add that. I prefer to use a join statement for that, so it looks like this:

JOIN DELL_ASSET W on W.SERVICE_TAG = M.BIOS_SERIAL_NUMBER

That line gets added after the last JOIN line from the wizard and before the ORDER BY clause.

Once the join is in place, you can reference the columns from the DELL_ASSET table (which we are calling W) in the select clause. The select clause is the comma separated list of column names after the word SELECT and before the word FROM, so we copy those columns from the second query:

distinct UCASE(m.NAME) as NAME, m.USER_FULLNAME, m.OS_NAME, m.CS_MODEL, w.SERVICE_TAG, w.SHIP_DATE, DATEDIFF(Now(), w.SHIP_DATE) as Days_since_ship, m.chassis_type, m.last_sync

And add them to the list in the wizard report. 

Once that is done you can change the ORDER BY statement appropriately. The resulting query should look like this (after some cleanup and formatting):

SELECT A23.NAME AS FIELD_23, ASSET_DATA_5.FIELD_31 AS FIELD_31, 
ASSET_DATA_5.FIELD_264 AS FIELD_264, 
A83.NAME AS FIELD_83, 
ASSET_DATA_5.FIELD_25 AS FIELD_25,
UCASE(M.NAME) as NAME, 
M.USER_FULLNAME, M.OS_NAME, M.CS_MODEL, 
W.SERVICE_TAG, W.SHIP_DATE, DATEDIFF(Now(), W.SHIP_DATE) as 'Days_since_ship', 
M.CHASSIS_TYPE, M.LAST_SYNC
FROM ASSET_DATA_5 
LEFT JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=5 
LEFT JOIN MACHINE M ON ASSET.MAPPED_ID = M.ID and ASSET.ASSET_TYPE_ID = 5 
LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID  
LEFT JOIN ASSET_ASSOCIATION J23 ON J23.ASSET_ID = ASSET.ID AND J23.ASSET_FIELD_ID=23
LEFT JOIN ASSET A23 ON A23.ID = J23.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_1 AD23 ON AD23.ID = A23.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J83 ON J83.ASSET_ID = ASSET.ID AND J83.ASSET_FIELD_ID=83
LEFT JOIN ASSET A83 ON A83.ID = J83.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_3 AD83 ON AD83.ID = A83.ASSET_DATA_ID
JOIN DELL_ASSET W on W.SERVICE_TAG = M.BIOS_SERIAL_NUMBER
ORDER BY Days_since_ship DESC




Answered 06/04/2019 by: chucksteel
Red Belt