Jump to content


  • 0
nickh2308

Report to list machine details with minimum memory requirements

Question

 

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

Share this post


Link to post
Share on other sites

8 answers to this question

Recommended Posts

  • 0

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.

Share this post


Link to post
Share on other sites

  • 0

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.

Share this post


Link to post
Share on other sites

  • 0

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:

 

 

Share this post


Link to post
Share on other sites

  • 0

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

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.