I am trying to get a report that shows what software is installed on each machine by Department. This is the code I came up with so far, but my syntax is off. Not sure where to go from here. Any help you can provide will be greatly appreciated.
SELECT ASSET.NAME AS ASSET_NAME,R27.NAME AS FIELD_R27,R24.NAME AS FIELD_R24,R24_L10001.NAME AS R24_FIELD_10001 FROM ASSET_DATA_2 LEFT JOIN ASSET ON ASSET_DATA_2.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=2 LEFT JOIN ASSET_ASSOCIATION JR27 ON JR27.ASSOCIATED_ASSET_ID = ASSET.ID AND JR27.ASSET_FIELD_ID=27
LEFT JOIN ASSET R27 ON R27.ID = JR27.ASSET_ID
LEFT JOIN ASSET_DATA_5 RD27 ON RD27.ID=R27.ASSET_DATA_ID LEFT JOIN ASSET_ASSOCIATION JR24 ON JR24.ASSOCIATED_ASSET_ID = ASSET.ID AND JR24.ASSET_FIELD_ID=24
LEFT JOIN ASSET R24 ON R24.ID = JR24.ASSET_ID
LEFT JOIN ASSET_DATA_6 RD24 ON RD24.ID=R24.ASSET_DATA_ID LEFT JOIN LABEL R24_L10001 ON R24_L10001.ID = RD24.FIELD_10001 ORDER BY ASSET_NAME,FIELD_R27,FIELD_R24
GROUP_CONCAT(DISTINCT A2.NAME SEPARATOR '\n') AS LICENSES,
COUNT(DISTINCT J3.ASSOCIATED_ASSET_ID) AS APPROVED_COUNT,
COUNT(DISTINCT MACHINE.ID) AS UNAPPROVED_COUNT,
GROUP_CONCAT(DISTINCT MACHINE.NAME SEPARATOR '\n') AS MACHINES
from ASSET
LEFT JOIN ASSET_ASSOCIATION J21 ON J21.ASSET_ID = ASSET.ID AND J21.ASSET_FIELD_ID=21
LEFT JOIN ASSET A21 ON A21.ID = J21.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSOCIATED_ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2
LEFT JOIN ASSET A2 ON A2.ID = J2.ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J3 ON
J3.ASSET_ID = A2.ID
AND J3.ASSET_FIELD_ID = 3
LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = ASSET.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT
ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = ASSET.ASSET_DATA_ID))
LEFT JOIN MACHINE on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
where (ASSET.ASSET_TYPE_ID = 6)
AND NOT EXISTS (SELECT 1 FROM ASSET C, ASSET_ASSOCIATION J3, ASSET_ASSOCIATION J2
WHERE C.ASSET_TYPE_ID=5 AND C.MAPPED_ID=MACHINE.ID
AND J3.ASSET_ID=J2.ASSET_ID
AND J3.ASSOCIATED_ASSET_ID = C.ID
AND J3.ASSET_FIELD_ID = 3
AND J2.ASSOCIATED_ASSET_ID = ASSET.ID
AND J2.ASSET_FIELD_ID = 2)
GROUP BY ASSET.ID
HAVING LICENSES IS NOT NULL
order by ASSET.NAME
Answers (2)
Whats the error your getting?
The trouble with custom asset reports is that each Kbox database is different.
That makes it very difficult for someone else to troubleshoot.
Comments:
-
This is the error:
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_CONCAT(DISTINCT A2.NAME SEPARATOR '\n') AS LICENSES,
COUNT(DISTINC' at line 6] in EXECUTE(
I'm trying to combine two reports that give similar info into one that gives me what I need.
D - DragonCrone 11 years ago -
It looks like your missing the last "T" in DISTINCT - dchristian 11 years ago
You have a lot of things out of order, for instance, this section:
GROUP_CONCAT(DISTINCT A2.NAME SEPARATOR '\n') AS LICENSES,
COUNT(DISTINCT J3.ASSOCIATED_ASSET_ID) AS APPROVED_COUNT,
COUNT(DISTINCT MACHINE.ID) AS UNAPPROVED_COUNT,
GROUP_CONCAT(DISTINCT MACHINE.NAME SEPARATOR '\n') AS MACHINES
Needs to be above the join statements, or rather the join statements need to come after the FROM ASSET. There's also another FROM ASSET_DATA_2 line further up in the code. It looks like you combined a few reports into one, but things aren't put together correctly.
The general syntax for a query is
select fields (a list of comma separated column names)
from table
joins to other tables
where certain things are true
ordered by fields
Comments:
-
I am very rusty on SQL. I haven't touched it in over 20 years, so I appreciate any hints, help or suggestions. I'll see what I can do to get it in the correct order and repost the code if I still need assistance. Thanks. - DragonCrone 11 years ago
