Re: Need assistance with query

Using that query, you can't do that (or at not easily). I'm going to suggest that you keep thing simple and use drilldowns.



Either drop the need to do all OU at one try or drop one of the columns.





"Dan Lewis" <waynelewis@xxxxxxxxx> wrote in message news:61dcf6cf-58a6-4599-a803-7ff4be8bca02@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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@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 :^( )

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
    ... for and the query that you are looking for is a complicated query to write ... 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)