Re: Need assistance with query

On Nov 10, 8:40 pm, Dan Lewis <waynele...@xxxxxxxxx> wrote:
On Nov 10, 8:43 am, "Garth" <S...@xxxxxxx> wrote:





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

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

news:c3869995-825c-4f57-9f05-784d6913d3e1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 :^(   )

Haha, no problem.  I waited till I got home and checked the links
out.  Very helpful information.  I'll give a go tomorrow and post my
results.  I didn't know you could build the queries for the reports in
that manner.  That should make it much easier to get the results I'm
looking for.- Hide quoted text -

- Show quoted text -

Ok, from the example on your website, how can that be modified to
display the report broken out by OU instead of just a single total
line (below doesn't work, just something I tried):
-- Printer Vars
Declare @HP int -- HP Printers
Declare @OKI int -- OKIData Printer
Declare @XRX int -- Xerox Printer
Declare @ESI int -- ESI Printer boxes
Declare @LXK int -- Lexmark Printer

-- PCs Vars
Declare @WS int
Declare @Ser int

-- Other Vars
Declare @Total int
Declare @Dif int

-- Popular each Var.
set @WS = (SELECT Count(v_GS_SYSTEM.SystemRole0)FROM v_GS_SYSTEM
v_GS_SYSTEM WHERE (v_GS_SYSTEM.SystemRole0 != 'Server'))
set @Ser = (SELECT Count(v_GS_SYSTEM.SystemRole0)FROM v_GS_SYSTEM
v_GS_SYSTEM WHERE (v_GS_SYSTEM.SystemRole0 = 'Server'))
set @Total = (SELECT Count(v_R_System.ResourceType)FROM v_R_System
v_R_System)
set @HP = (SELECT Distinct Count(v_R_System.Name0)FROM v_R_System
v_R_System where v_R_System.Name0 like 'NPI%' and
v_R_System.Operating_System_Name_and0 Is Null)
set @OKI = (SELECT Distinct Count(v_R_System.Name0)FROM v_R_System
v_R_System where v_R_System.Name0 like 'OKI%' and
v_R_System.Operating_System_Name_and0 Is Null)
set @XRX = (SELECT Distinct Count(v_R_System.Name0)FROM v_R_System
v_R_System where v_R_System.Name0 like 'XRX%' and
v_R_System.Operating_System_Name_and0 Is Null)
set @ESI = (SELECT Distinct Count(v_R_System.Name0)FROM v_R_System
v_R_System where v_R_System.Name0 like 'ESI%' and
v_R_System.Operating_System_Name_and0 Is Null)
set @LXK = (SELECT Distinct Count(v_R_System.Name0)FROM v_R_System
v_R_System where v_R_System.Name0 like 'LXk%' and
v_R_System.Operating_System_Name_and0 Is Null)
Set @Dif = @Total - (@WS+@Ser+@hp+@OKI+@XRX+@ESI+@LXK)

Select
System_OU_Name0 as 'OUs',
@WS as 'Workstations',
@Ser as 'Server',
@HP as 'HP Printers',
@OKI as 'OKI Data Printers',
@XRX as 'Xerox Printers',
@LXK as 'Lexmark Printers',
@ESI as 'ESI Printer Boxes',
@Dif as 'Other IP Devices',
@Total as 'Total IP Devices'
from v_RA_System_SystemOUName
GROUP BY v_RA_System_SystemOUName.System_OU_Name0
ORDER BY v_RA_System_SystemOUName.System_OU_Name0

.

Relevant Pages

  • Re: Need assistance with query
    ... Using that query, you can't do that. ... Declare @OKI int -- OKIData Printer ... Declare @ESI int -- ESI Printer boxes ...
    (microsoft.public.sms.admin)
  • Re: Strange SQL results
    ... declare @t1 table ... id int null ... Notice how I'm allowing nulls for id, since I don't have your DDL. ... I did not tried Join query, but I got proper data set by running following ...
    (microsoft.public.sqlserver.server)
  • variable substitution vs. executing dynamic strings
    ... WHY is the second query so much ... (This is actually an sproc where the start / end variables are passed_ The ... declare @start int ...
    (microsoft.public.sqlserver)
  • Re: Is this an MSSQL bug?
    ... > I have the following query on MSSQL 2000. ... > declare @t table(i int identity, j int, k int) ... > An explicit value for the identity column in table '@t' can only be ...
    (microsoft.public.sqlserver.programming)
  • Re: Really tough ADO Stored Procedure Question. Please Help!!!
    ... @lScenarioID_CopyFrom int, ... DECLARE @ErrMSG varchar--This is the max msg size ... ROLLBACK TRANSACTION ... SELECT @lRowCountHolder = MIN ...
    (microsoft.public.sqlserver.odbc)
Loading