I have the following report/query that is a default report in KACE:

Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count  from (SOFTWARE, MACHINE_SOFTWARE_JT) 
where SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
and not IS_PATCH
GROUP BY DISPLAY_NAME
order by DISPLAY_NAME

This displays the info I am looking for but the question I have is can we have one query that will return this info for only my SERVER environment? I will also need this for all my workstations (Windows 7 ,XP ,etc ) machines.

I want to have my server’s separate from my workstations.

 

1 Comment   [ + ] Show Comment

Comments

  • You would need to join the MACHINE table and then include a where statement that limited the results to just server OS versions.
Please log in to comment

Answers

0

Change the OS_NAME to match what you need.

Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

from SOFTWARE S

LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

WHERE not S.IS_PATCH

AND M.OS_NAME LIKE '%SERVER%'

GROUP BY DISPLAY_NAME

order by DISPLAY_NAME
Answered 07/02/2013 by: dugullett
Red Belt

  • Thanks for this it is working perfect. Just another question:
    We have 15 organizations and I need to run these reports for each of them. Isn't there a way that I can run from system? If I run this from system I get the following error :

    mysql error: [1146: Table 'KBSYS.SOFTWARE' doesn't exist] in EXECUTE(
    "select * from (Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    from SOFTWARE S

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    ) as T order by DISPLAY_NAME LIMIT 0")
    • I have one ORG so it's hard for me to say. You should be able to change the FROM SOFTWARE S line to FROM ORG1.SOFTWARE, ORG2.SOFTWARE, and so on.
  • Still getting the same error

    mysql error: [1146: Table 'KBSYS.MACHINE_SOFTWARE_JT' doesn't exist] in EXECUTE(
    "select * from (Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    union Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count

    FROM ORG24.SOFTWARE, ORG17.SOFTWARE, ORG18.SOFTWARE, ORG16.SOFTWARE, ORG9.SOFTWARE

    LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON S.ID = MSJT.SOFTWARE_ID

    LEFT JOIN MACHINE M ON MSJT.MACHINE_ID = M.ID

    WHERE not S.IS_PATCH

    AND M.OS_NAME LIKE '%SERVER%'

    GROUP BY DISPLAY_NAME

    ) as T order by DISPLAY_NAME LIMIT 0")
    • It looks like it's looking for MACHINE_SOFTWARE_JT in KBYS which is where it does not exist. Each ORG should also contain that table, so I'm assuming you will need to do JOINs for each.
  • How do I do that can you show me an example please?
    • It's hard to say since I only have one ORG so I can't really test.

      You can try something like this. I'm thinking you need to break each ORG out.

      FROM ORG24.SOFTWARE S24
      LEFT JOIN ORG24.MACHINE_SOFTWARE_JT MSJT24 ON S24.ID = MSJT24.SOFTWARE_ID
      LEFT JOIN ORG24.MACHINE M24 ON MSJT24.MACHINE_ID=M24.ID
      WHERE not S24.IS_PATCH
      AND M24.OS_NAME LIKE '%SERVER%'
      GROUP BY S24.DISPLAY_NAME


      FROM ORG17.SOFTWARE S17
      LEFT JOIN ORG17.MACHINE_SOFTWARE_JT MSJT17 ON S17.ID = MSJT17.SOFTWARE_ID
      LEFT JOIN ORG17.MACHINE M17 ON MSJT17.MACHINE_ID=M17.ID
      WHERE not S17.IS_PATCH
      AND M17.OS_NAME LIKE '%SERVER%'
      GROUP BY S17.DISPLAY_NAME

      ...and so on.
Please log in to comment
Answer this question or Comment on this question for clarity