Re: Need assistance with query

On Nov 1, 6:38 pm, "Garth" <S...@xxxxxxx> wrote:
Try this.http://smsug.ca/blogs/garth_jones/archive/2008/11/01/ou-count-of-pcs-...

This query will give you a count of all PC that meet a requirement for each
OU.

"Dan Lewis" <waynele...@xxxxxxxxx> wrote in message

news:bb6840b9-a587-4a4f-8580-0dea2f3ffb1e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



I'm trying to build a report that will show each OU in AD and a count
of all computers in that OU and a count of computers that meet some
requirement in that OU:

SELECT OU.System_OU_Name0, count(*) as 'Count'
FROM  v_R_System as SYS
JOIN v_RA_System_SystemOUName as OU on SYS.ResourceID = OU.ResourceID
JOIN AnotherView on SYS.ResourceID = AnotherView.ResourceID
WHERE AnotherView.SomeCriteria0 like "%99%"
group by OU.System_OU_Name0 order by OU.System_OU_Name0

I can't figure out how to get it to show a count of ALL systems in the
OU.  Any help is appreciated.- Hide quoted text -

- Show quoted text -

That helped. This is what I came up with, and it works:

select distinct a.System_OU_Name0, count(a.ResourceID) as 'All
Workstations', count(b.ResourceID) as 'XXXX',
cast(100*(cast(count(b.ResourceID)as float)/cast(count(a.ResourceID)
as float)) as numeric(5,2)) as 'Percent'

from v_RA_System_SystemOUName a

left join v_2NDVALUE0 b on b.ResourceID = a.ResourceID
and b.ImageRevision0 like "%value%"

where a.System_OU_Name0 = "ouname"
group by a.System_OU_Name0 order by a.System_OU_Name0


Now, I want to add another column that shows the number of systems
that have office 2007 installed, so I tried this:

select distinct a.System_OU_Name0, count(a.ResourceID) as 'All
Workstations', count(b.ResourceID) as 'XXXX',
cast(100*(cast(count(b.ResourceID)as float)/cast(count(a.ResourceID)
as float)) as numeric(5,2)) as 'Percent', count(c.ResourceID) as
'Office2K7'

from v_RA_System_SystemOUName a

left join v_2NDVALUE0 b on b.ResourceID = a.ResourceID
and b.ImageRevision0 like "%value%"

left join v_GS_INSTALLED_SOFTWARE c on c.ResourceID = a.ResourceID
and c.ProductName0 like "%office%2007%"

where a.System_OU_Name0 = "ouname"
group by a.System_OU_Name0 order by a.System_OU_Name0

But, this is not working. The numbers come out completely wrong.
.

Relevant Pages

  • Re: Need assistance with query
    ... This query will give you a count of all PC that meet a requirement for each ... > of all computers in that OU and a count of computers that meet some ... Workstations', countas 'XXXX', ... as float)) as numeric) as 'Percent', countas ...
    (microsoft.public.sms.admin)
  • Re: Inventory Report
    ... Workstations ... I have my computers in the WORKSTATIONS OU. ... So I started looking into your example query. ... > (OperatingSystemNameandVersion not like "Microsoft Windows NT ...
    (microsoft.public.sms.inventory)
  • SMS Collections
    ... We have been trying to create a query in SMS 2003 that does the following: ... select ResourceId, ResourceType, Name, SMSUniqueIdentifier, ... Unfortunately, while it seems to work at only gathering Workstations, it ... keep all of the remaining computers. ...
    (microsoft.public.sms.admin)
  • Re: Need assistance with query
    ... for and the query that you are looking for is a complicated query to write ... Workstations', countas 'XXXX', ... as float)) as numeric) as 'Percent', countas ... specific OU that have Office 2007 and the other criteria, ...
    (microsoft.public.sms.admin)
  • Re: Need assistance with query
    ... I don't think that you have your query right based on what you are looking for and the query that you are looking for is a complicated query to write and test. ... Workstations', countas 'XXXX', ... as float)) as numeric) as 'Percent', countas ... specific OU that have Office 2007 and the other criteria, ...
    (microsoft.public.sms.admin)
Loading