/build/static/layout/Breadcrumb_cap_w.png
10/29/2019 125 views

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

Comments


All Answers

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


Answered 10/30/2019 by: chucksteel
Red Belt

  • 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

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