/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (2)

Posted by: dchristian 12 years ago
Red Belt
1

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 12 years ago
  • It looks like your missing the last "T" in DISTINCT - dchristian 12 years ago
Posted by: chucksteel 12 years ago
Red Belt
0

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 12 years ago

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