Software Report
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
-
You would need to join the MACHINE table and then include a where statement that limited the results to just server OS versions. - chucksteel 11 years ago
Answers (1)
Please log in to answer
Posted by:
dugullett
11 years ago
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
Comments:
-
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") - Mariusja 11 years ago-
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. - dugullett 11 years ago
-
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") - Mariusja 11 years ago-
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. - dugullett 11 years ago
-
How do I do that can you show me an example please? - Mariusja 11 years ago
-
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. - dugullett 11 years ago
