Adding fields from Wizard report to SQL Report
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
-
I would suggest using MySQL Workbench to browse the database and determine which fields are which. - ondrar 5 years ago
Answers (1)
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
