• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5429
  • Last Modified:

Creation of an SCCM Report for Computers By Model

I am attempting to create a report in SCCM to display Computers by Model.  This report needs to display the Computer Name, Login ID, Computer Model, Service Tag (Serial Number).

I've been unable to find a report that successfully gathers this information.  I have seem some SQL statements that have this information but the report does not work wit hthe statement.  Is anyone able to provide this SQL statement for this report with the information above?

Thank you!
0
grant_paul
Asked:
grant_paul
  • 4
  • 3
1 Solution
 
NJComputerNetworksCommented:
0
 
grant_paulAuthor Commented:
Ok, this report works...

select
    Model0,
    Count(*)
from
    dbo.v_GS_COMPUTER_SYSTEM CS,
    dbo.v_FullCollectionMembership FCM
Where
    CS.ResourceId = FCM.ResourceId
    And CollectionID = 'SMS00001'
Group by
    Model0

Unfortunately it's incomplete as it does not breakdown the specfic computer names, login IDs, and Service tags as requested in the original question.

This query is helpful to show a count though.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
NJComputerNetworksCommented:
0
 
grant_paulAuthor Commented:
Are you able to include steps as to how to implement the SQL view?

Thanks!
0
 
matrixnzCommented:
Copy and Paste the Report SQL Statement Below into a new report.
Click Prompts... Button
Click New Prompt Icon
Enter Name: ID
Enter Prompt Text: Please specify a collection ID
Uncheck Allow an empty value
Check Provide SQL Statement
Click Edit SQL Statement... Button
Copy and Paste the Prompt SQL statement below
The Report is now complete, close dialogue boxes and run.

Cheers
*** Report SQL Statement Below Here ***
SELECT
Distinct
  SYS.Netbios_Name0,
  SYS.User_Name0,
  MOD.Manufacturer0,
  MOD.Model0,
  BIO.SerialNumber0
FROM v_R_System SYS
  JOIN v_GS_COMPUTER_SYSTEM MOD on SYS.ResourceID = MOD.ResourceID
  JOIN v_GS_PC_BIOS BIO on SYS.ResourceID = BIO.ResourceID
  JOIN v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
WHERE
  FCM.CollectionID=@ID
ORDER BY SYS.Netbios_Name0
*** Report SQL Statement Above Here ***

*** Prompt SQL Statement Below Here ***
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
*** Prompt SQL Statement Above Here ***

Open in new window

0
 
grant_paulAuthor Commented:
matrixnz,

This is absolutely incredible!!!!!

This is EXACTLY what I'm looking for! Thank you so much for your help on this!
0
 
grant_paulAuthor Commented:
FANTASTIC!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now