Re: SMS Reporting Query question

Tech-Archive recommends: Fix windows errors by optimizing your registry

You are you looking for a final line with total for each of RAM? Keep to the
KISS idea and try this..

select distinct
case v_GS_Computer_system.model0
when 'Virtual Machine' then 'Virtual'
when 'VMware Virtual Platform' then 'Virtual'
when 'Evo D310v' then '1GB PC2700'
when 'Evo D500 USDT' then 'Need to examine system'
when 'Evo D510 CMT' then '1GB PC2700'
when 'Evo D510 SFF' then '1GB PC2700'
else 'Unknown'
end as 'Ram Type'
, count(v_GS_Computer_system.model0) as 'Total'

from
v_gs_computer_system,
v_GS_x86_PC_memory,
v_GS_processor,
v_gs_video_controller,
v_GS_Operating_system

WHERE
v_gs_operating_system.resourceid = v_gs_computer_system.resourceid
and v_gs_computer_system.resourceid = v_gs_computer_system.resourceid
and v_GS_x86_PC_memory.resourceid = v_gs_computer_system.resourceid
and v_GS_processor.resourceid = v_gs_computer_system.resourceid
and v_GS_video_controller.resourceid = v_gs_computer_system.resourceid
and v_GS_Operating_system.caption0 NOT like '%2000 Server%'
and v_GS_Operating_system.caption0 NOT like '%Server 2003%'
and v_GS_video_controller.CurrentHorizontalResolution0 <> NULL
and v_GS_x86_PC_memory.totalphysicalmemory0 < '1024768'

Group by
v_GS_Computer_system.model0

Order by
[RAM Type]




"UnderCoverGuy" <UnderCoverGuy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EBF1F65F-8F6B-4EED-B8E2-2AFD2EC2FE58@xxxxxxxxxxxxxxxx
Thanks Garth. Order by works great, but would happen to know if it is
possible to SUM with Order By like you can (apparently) with Group By? I
was
thinking that may accomplish what I need to do. When I change Order By to
Group By on my original query statement, it doesn't work and I get the
error:

Column 'v_gs_computer_system.UserName0' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP
BY
clause.

Thanks................................................................


"Garth" wrote:

yes.. you should be able to add [RAM Type] to the Order by line like
this.

Order by
[RAM Type], v_gs_computer_system.name0


"UnderCoverGuy" <UnderCoverGuy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:CE6AB37C-E479-456E-8CA3-553AA1CF6B8A@xxxxxxxxxxxxxxxx
Good morning. I initialy posted this question in another thread and
Garth
helped quite a bit (thanks Garth), but I wasn't able to figure it out,
so
I
found a different approach that may work (if I can get it to work).

In the query below, is it possible to GROUP BY the "RAM Type" (aka ,
case
v_GS_Computer_system.model0)? If so, I am thinking that I can SUM the
column
"RAM Quantity", but haven't been able to figure out how to make it
work.
I
think that this may provide me with a tally of system types and total
up
for
me the quantity of memory. Any thoughts or help is greatly
appreciated.

Thanks (in advance),
UCG

----------------------------------------------------------------------------------

select distinct
v_GS_Computer_system.Name0 AS "PC Name"
, v_GS_Computer_system.UserName0 AS "User last logged on"
, v_GS_Operating_System.caption0 AS "OS"
, v_gs_Operating_system.csdversion0 AS "SP Level"
, v_GS_Computer_system.manufacturer0 AS "Make"
, v_GS_Computer_system.model0 AS "Model"
, v_GS_x86_PC_memory.totalphysicalmemory0 AS "RAM Installed"

, case v_GS_Computer_system.model0
when 'Virtual Machine' then 'Virtual'
when 'VMware Virtual Platform' then 'Virtual'
when 'Evo D310v' then '1'
when 'Evo D500 USDT' then 'Unknown'
when 'Evo D510 CMT' then '1'
when 'Evo D510 SFF' then '1'
else 'Unknown'
end as 'RAM Quantity'

, case v_GS_Computer_system.model0
when 'Virtual Machine' then 'Virtual'
when 'VMware Virtual Platform' then 'Virtual'
when 'Evo D310v' then '1GB PC2700'
when 'Evo D500 USDT' then 'Need to examine system'
when 'Evo D510 CMT' then '1GB PC2700'
when 'Evo D510 SFF' then '1GB PC2700'
else 'Unknown'
end as 'Ram Type'

, v_gs_processor.maxclockspeed0 AS "CPU Speed"

, cast(v_gs_video_controller.CurrentHorizontalResolution0 as Varchar) +
'
x
' + cast(v_gs_video_controller.CurrentVerticalResolution0 as varchar)
AS
"Video Resolution"

from
v_gs_computer_system,
v_GS_x86_PC_memory,
v_GS_processor,
v_gs_video_controller,
v_GS_Operating_system

WHERE
v_gs_operating_system.resourceid = v_gs_computer_system.resourceid
and v_gs_computer_system.resourceid = v_gs_computer_system.resourceid
and v_GS_x86_PC_memory.resourceid = v_gs_computer_system.resourceid
and v_GS_processor.resourceid = v_gs_computer_system.resourceid
and v_GS_video_controller.resourceid = v_gs_computer_system.resourceid
and v_GS_Operating_system.caption0 NOT like '%2000 Server%'
and v_GS_Operating_system.caption0 NOT like '%Server 2003%'
and v_GS_video_controller.CurrentHorizontalResolution0 <> ""
and v_GS_x86_PC_memory.totalphysicalmemory0 < '1024768'

Order by
v_gs_computer_system.name0

----------------------------------------------------------------------------------






.

Relevant Pages