SMS Reporting Query question

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

  • Re: SMS Reporting Query question
    ... "RAM Quantity", but haven't been able to figure out how to make it work. ... when 'Evo D500 USDT' then 'Unknown' ...
    (microsoft.public.sms.admin)
  • SMS Reporting question
    ... possible to tally columns based on a column value? ... when 'Evo D500 USDT' then 'Unknown' ... end as 'RAM Quantity' ...
    (microsoft.public.sms.admin)
  • Re: SMS Reporting Query question
    ... In the query below, is it possible to GROUP BY the "RAM Type" (aka, case ... "RAM Quantity", but haven't been able to figure out how to make it work. ... when 'Evo D500 USDT' then 'Unknown' ...
    (microsoft.public.sms.admin)
  • Re: SMS Reporting Query question
    ... Thanks Garth. ... possible to SUM with Order By like you can with Group By? ... In the query below, is it possible to GROUP BY the "RAM Type" (aka, case ... when 'Evo D500 USDT' then 'Unknown' ...
    (microsoft.public.sms.admin)
Loading