/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 1 year ago
Red Belt
1

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 1 year 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