Query to count devices in KACE Organizations
I need create a sql report to count all devices in each organization, but not in SMA Systems, in another sql tool(HeidiSQL).
We have 30 organization and this view need get the ID, Name, Description and Computer Count.
Please see what I got so far:
SELECT ID,NAME,DESCRIPTION,
(select distinct count(ID) FROM ORG1.MACHINE)as 'Computer Count'
from KBSYS.ORGANIZATION
I believe it is necessary to create a loop, but I haven't got it yet.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
4 years ago
The KUID_ORGANIZATION table appears to contain the KUID for each machine and which org it belongs to. I say appears because my SMA doesn't have orgs enabled, so I can't verify. Try this:
SELECT ID,NAME,DESCRIPTION,
COUNT(KUID) as 'Computer Count'
from KBSYS.ORGANIZATION
join KBSYS.KUID_ORGANIZATION on KUID_ORGANIZATION.ID = ORGANIZATION.ID
GROUP BY ORGANIZATION.ID
Comments:
-
You're right! Each KUID appers in this table KUID_ORGANIZATION and the organization ID too. I just made a small adjustment to the query and it worked very well. See:
SELECT ID,NAME,DESCRIPTION,
COUNT(KUID) as 'Computer Count'
from KBSYS.ORGANIZATION
join KBSYS.KUID_ORGANIZATION on KUID_ORGANIZATION.ORGANIZATION_ID = ORGANIZATION.ID
GROUP BY ORGANIZATION.ID - Anderson Jose Klaus 4 years ago
