Report to list machine details with minimum memory requirements

[nickh2308]

 

Hello, I am new to SQL and I would like to run a report for machines which have CPU under 1ghz and memory under 2gb.I have got an existing report I want to edit.

 

I would like to add the machine name, IP address, computer brand and model and user ID for the Bad Devices.

 

I have attached my text file with my code.

 

Thanks for the help.

 

 

query.txt

[GarthMJ]

First off you need to fix this query to NOT use the SQL table. you Need to use the SQL views instead. It is bad practice to use SQL tables.

 

Adding PC, Manufacturer, model to this report will make this report not all that helpful as you will have 1 rows for each PC within the collections. What exactly are you looking to do?

 

The details you are looking for can be found within the v_GS_Computer_System view.

I never recommend adding IP address to reports as this will cause duplicate rows.

[nickh2308]

Thanks Garth.

 

I am trying to get a table which lists computers from different collections with less than 1GB memory and 1GHZ processor.

 

I am trying to get an output to display the collections listing the machines with computer name, brand, model, user name.

[GarthMJ]

Again Don't use that tables. This may cause problems with your CM environment.

 

EXACT what would you like this report to look like? Post a screenshot example in excel of what you are looking for.

 

As you have it now, it just doesn't make any sense to do what you are asking for.

[nickh2308]

Hi Garth,

 

I am unable to attach a spreadsheet. This is how I would like the report to look with the results of computers with memory under 1gb and CPU under 1ghz.

 

I would like headings up at the top like:

 

Collection Name: Computer Name: Manufacturer: Model: Username: IP Address:

 

 

[GarthMJ]

This report will get you started.

http://smsug.ca/blogs/garth_jones/archive/2008/02/04/basic-all-in-one.aspx

 

There are lots of this type of thing on my blog. BTW, I never recommend adding IP address to a report.

[nickh2308]

Thanks Garth. I have got everything sorted besides the Collection row.

 

I am not sure which tables to pull the collection info from to list which Collection each computer is a member of.

[GarthMJ]

You can get the collection info. from the v_collections and v_Fullcollectionmebership view.

[nickh2308]

Thanks Garth. This is what I have but it tends to freeze. Is the collection part of the code correct? I am getting stuck here. Appreciate your help.

SELECT  distinct 
 CS.name0 as 'Computer Name', 
 CS.domain0 as 'Domain', 
 CS.UserName0 as 'User', 
 BIOS.SerialNumber0 as 'Bios serial', 
 CS.Manufacturer0 as 'Manufacturer', 
 CS.Model0 as 'model', 
 OS.Caption0 as 'OS', 
 RAM.TotalPhysicalMemory0 as 'Total Memory', 
 sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size', 
 sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space', 
 CPU.CurrentClockSpeed0 as 'CPU Speed',
v_collection.name as 'Collection Name'
 
from  
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID

 right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID  
 right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID 
 right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID 
 right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID    
 right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID ,

v_fullcollectionmembership join v_collection on v_collection.collectionid = v_FullCollectionMembership.collectionid 
join v_r_system sys on sys.resourceid = v_fullcollectionmembership.resourceid 


where 
RAM.TotalPhysicalMemory0 < 2097152
group by 
 CS.Name0, 
 CS.domain0,
 CS.Username0, 
 BIOS.SerialNumber0, 
 SE.SerialNumber0,
 CS.Manufacturer0, 
 CS.Model0, 
 OS.Caption0, 
 RAM.TotalPhysicalMemory0, 
 CPU.CurrentClockSpeed0,
 v_collection.name