Modify report to select from Collection instead of single System...

[MRaybone]

Hi all,

 

I have a report which currently provides some info about a particular selected system, and I'd like to edit it to report on the contents of a particularly selected collection instead. I have pretty much 0 SQL knowledge so was hoping someone here could show me the edit I need as I'm unsure on the joins and syntax etc. required...?

 

Here's the query:

select sys.netbios_name0, mem.banklabel0 [bank Label], mem.capacity0 [Capacity (MB)], mem.FormFactor0 [Form Factor],
MEM.memorytype0 [Memory Type], mem.tag0 [TAG], mem.devicelocator0 [Device Locator], mem.speed0 [speed (MHz)] from v_gs_physical_memory
as MEM inner join v_r_system as SYS on SYS.resourceid=MEM.resourceid
where
sys.netbios_name0 = @compname
order by MEM.tag0

select MEMA.MemoryDevices0 [# of Slots], MEMA.MaxCapacity0 [Max. Capacity (MB)] from v_gs_physical_memory_array as MEMA
inner join v_r_system as SYS on SYS.resourceid=MEMA.resourceid
where
sys.netbios_name0 = @compname

select mema.memoryDevices0 - Count(mem.tag0) [Number of Free Slots available] from v_gs_physical_memory as MEM
inner join v_r_system as SYS on SYS.resourceid=MEM.resourceid
inner join v_gs_physical_memory_array as MEMA on sys.resourceid=mema.resourceid
where
sys.netbios_name0 = @compname
group by mema.memorydevices0

Prompt:

begin
if (@__filterwildcard = '')
 SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS ORDER By SYS.Netbios_Name0
else
 SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS
 WHERE SYS.Netbios_Name0 like @__filterwildcard
 ORDER By SYS.Netbios_Name0
end

[The original source was from: http://myitforum.com/cs2/blogs/skissinger/archive/2008/09/01/physical-memory-and-memory-slots-hardware-inventory-extension.aspx]

 

Many thanks,

MRaybone.

Edited February 15, 2010 by MRaybone

[Eswar Koneti]

Here is the report which prompts you yo specify the collection.

 

select sys.netbios_name0, mem.banklabel0 [bank Label], mem.capacity0 [Capacity in MB], mem.FormFactor0 [Form Factor],
MEM.memorytype0 [Memory Type], mem.tag0 [TAG]from v_gs_physical_memory as MEM
inner join v_r_system as SYS on SYS.resourceid=MEM.resourceid inner join v_FullCollectionMembership c ON SYS.resourceid=c.resourceid
where
CollectionID= @coll
order by MEM.tag0

select sys.netbios_name0,MEMA.MemoryDevices0 [Total Number of Memory Slots] from v_gs_physical_memory_array as MEMA
inner join v_r_system as SYS on SYS.resourceid=MEMA.resourceid inner join v_FullCollectionMembership c ON SYS.resourceid=c.resourceid
where
CollectionID= @coll
group by sys.netbios_name0,MEMA.MemoryDevices0

select sys.netbios_name0,mema.memoryDevices0 - Count(mem.tag0) [Number of Free Slots available] from v_gs_physical_memory as MEM
inner join v_r_system as SYS on SYS.resourceid=MEM.resourceid
inner join v_gs_physical_memory_array as MEMA on sys.resourceid=mema.resourceid inner join v_FullCollectionMembership c ON SYS.resourceid=c.resourceid
where
CollectionID= @coll
group by sys.netbios_name0,mema.memorydevices0

 

Prompt for @coll is given below:

begin
if (@__filterwildcard = '')
 SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
else
 SELECT DISTINCT CollectionID, Name FROM v_Collection
 WHERE CollectionID like @__filterwildcard
 ORDER BY Name
end 

[MRaybone]

Many thanks, that's excellent!!

[mr_bogus]

hi,

 

i was able to follow the instructions above but i got an error when i tried to run the created report. below is the exact error. hope you could help me on this. thanks in advance

 

 

An error occurred when the report was run. The details are as follows:

Invalid object name 'v_gs_physical_memory'. Error Number: -2147217865 Source: Microsoft OLE DB Provider for SQL Server Native Error: 208

[newnoob]

I have a SQL query and try to insert those in as below but it show error of invalid CollectionID Please help. I have o knowledge of SQL:

 

SELECT

DISTINCT(CPU.SystemName0) AS [system Name],

CPU.Manufacturer0 AS Manufacturer,

CPU.Name0 AS Name,

COUNT(CPU.ResourceID) AS [Number of CPUs],

CPU.NumberOfCores0 AS [Number of Cores per CPU],

CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]

FROM [dbo].[v_GS_PROCESSOR] CPU

where

CollectionID= @coll

GROUP BY

CPU.SystemName0,

CPU.Manufacturer0,

CPU.Name0,

CPU.NumberOfCores0,

CPU.NumberOfLogicalProcessors0

begin

if (@__filterwildcard = '')

SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name

else

SELECT DISTINCT CollectionID, Name FROM v_Collection

WHERE CollectionID like @__filterwildcard

ORDER BY Name

end