Jump to content


  • 0
MRaybone

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

Question

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 by MRaybone

Share this post


Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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 

Share this post


Link to post
Share on other sites

  • 0

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

Share this post


Link to post
Share on other sites

  • 0

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.