Re: Need assistance with query

Yes, just ask them to allow that site. It's just Canada you know. They are
everyone's friend. I can't think of a single country that dislikes Canada!

Or if they refuse, or need 10 forms signed in triplicate; connect to
smsug.ca from outside of your company's firewall.

--
Standardize. Simplify. Automate.


"Garth" wrote:

There are hundreds of queries on that site, I would ask your firewall team
to remove the block.


"Dan Lewis" <waynelewis@xxxxxxxxx> wrote in message
news:c3869995-825c-4f57-9f05-784d6913d3e1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Nov 8, 9:16 pm, "Garth" <S...@xxxxxxx> wrote:
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. I can't test it because you are using views that i don't have
such as "v_2NDVALUE0"

Take a look at this

http://smsug.ca/blogs/garth_jones/archive/2007/05/11/342.aspx

and I think this is more what you are looking for

http://smsug.ca/blogs/garth_jones/archive/2007/06/24/431.aspx

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

news:0c99f942-3d40-4987-bbd8-a7be58d71b1a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Nov 7, 3:08 pm, "Garth" <S...@xxxxxxx> wrote:





I'm curious why you think the numbers are all wrong?

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

news:96c24d56-e5e1-417d-b192-fd086d9e92ea@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.

Because they are :^)

I'm not sure what is happening, but the numbers are very exaggerated.
If I build an individual query to show the number of systems in a
specific OU that have Office 2007 and the other criteria, the numbers
are correct, but this report is not. If I understood more about how
sql queries worked, I might be able to follow the flow of the query I
posted. It logically makes sense to me and seems like it should work,
but after I added the 2nd join (v_GS_INSTALLED_SOFTWARE), it goes
crazy on me. It throws off the counts for a and b. What I want is:

count(a.ResourceID) = ALL systems in the OU
count(b.ResourceID) = the systems in the OU that meet the criteria for
the first join
count(c.ResourceID) = the systems in the OU that have Office 2007
installed

a and b are working, but not when I add c.- Hide quoted text -

- Show quoted text -

Can you post the information from those links (they are blocked for
me :^( )

.

Relevant Pages

  • 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
    ... There are hundreds of queries on that site, I would ask your firewall team to remove the block. ... for and the query that you are looking for is a complicated query to write ... > 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)
  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
Loading