SCCM report for Computers Asset information including SN Number and model name

SMS/SCCM report with Asset Details information:

I had a question from my boss to get list of computers that consists of computer name,model ,last boot up time and other important information.You can also customize it according to ur needs but the basis information that gives from below report.

This reports provides an asset manager’s summary of computers matching keyword filters on computer name, SMS site, domain, top console user, operating system, manufacturer, or model etc.

SELECT     TOP 100 PERCENT
dbo.v_GS_COMPUTER_SYSTEM.Name0 AS [Computer Name],
                      MAX(dbo.v_GS_COMPUTER_SYSTEM.UserName0) AS [Top console User], dbo.v_GS_PC_BIOS.SerialNumber0, dbo.v_R_System.AD_Site_Name0,
                      dbo.v_R_System.Resource_Domain_OR_Workgr0, dbo.v_R_System.SMS_Unique_Identifier0,
                      dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack],
                      dbo.v_GS_COMPUTER_SYSTEM.Model0, dbo.v_GS_PC_BIOS.Manufacturer0, MAX(dbo.v_GS_PROCESSOR.Name0) AS Processor,
                      dbo.v_GS_COMPUTER_SYSTEM.NumberOfProcessors0, MAX(dbo.v_GS_LOGICAL_DISK.FreeSpace0) AS [Free Disk(MB)],
                      MAX(dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0) AS [Physical Memory (KBytes)],dbo.v_GS_OPERATING_SYSTEM.InstallDate0
FROM         dbo.v_GS_LOGICAL_DISK INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM INNER JOIN
                      dbo.v_GS_PC_BIOS ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_GS_PC_BIOS.ResourceID INNER JOIN
                      dbo.v_GS_PROCESSOR ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_GS_PROCESSOR.ResourceID INNER JOIN
                      dbo.v_GS_X86_PC_MEMORY ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_GS_X86_PC_MEMORY.ResourceID ON
                      dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_GS_X86_PC_MEMORY.ResourceID INNER JOIN
                      dbo.v_GS_OPERATING_SYSTEM INNER JOIN
                      dbo.v_R_System ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID ON
                      dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID
GROUP BY
dbo.v_GS_COMPUTER_SYSTEM.Name0, dbo.v_GS_PC_BIOS.SerialNumber0,
                      dbo.v_R_System.AD_Site_Name0, dbo.v_R_System.Resource_Domain_OR_Workgr0, dbo.v_R_System.SMS_Unique_Identifier0,
                      dbo.v_GS_OPERATING_SYSTEM.CSDVersion0, dbo.v_GS_PC_BIOS.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.NumberOfProcessors0,
                      dbo.v_GS_OPERATING_SYSTEM.Caption0, dbo.v_GS_COMPUTER_SYSTEM.Model0,dbo.v_GS_OPERATING_SYSTEM.InstallDate0

computers with Particular Application installed and running on the computers (ex:Microsoft Office 2007 and F- Secure Management):

SELECT
c.Name0,
a.DisplayName0,
b.CollectionID

FROM
v_GS_ADD_REMOVE_PROGRAMS a,
v_FullCollectionMembership b,
v_R_System c

WHERE a.ResourceID = b.ResourceID
AND a.ResourceID = c.ResourceID
AND b.CollectionID=@collID
AND (a.DisplayName0 =’F-Secure Management Agent’ OR a.DisplayName0 = ‘Microsoft Office Professional Plus 2007′)

GROUP BY
c.Name0,
a.DisplayName0,
b.CollectionID

ORDER BY a.DisplayName0

Create a Prompt for @CollectionID with the below query:

select CollectionID,Name from v_Collection

In place of F-Secure Management Agent and MS office ,you might need to replace the required application names which you are looking for and this can be findout from default avialable reports or by looking at Resource explorer on any computer which is installed.

List of computers which are boot up(Boot time) last 1 week :

Select V_R_System.Name0, v_GS_OPERATING_SYSTEM.LastBootUpTime0 From V_R_System Inner Join v_GS_OPERATING_SYSTEM On v_GS_OPERATING_SYSTEM.ResourceId = V_R_System.ResourceId Where DatePart(DD,v_GS_OPERATING_SYSTEM.LastBootUpTime0) >= 7

Report for Machines that Exist Twice in the Database:

SELECT * FROM (SELECT name0,COUNT(*) cnt FROM v_R_SYSTEM GROUP BY name0) DERIVEDTBL
WHERE (cnt > 1)

Report for Count of machines that are discovered by different Discovery methods in SMS/SCCM:

select distinct b.AgentName,count(*) from v_R_System a join v_AgentDiscoveries b on b.ResourceId=a.ResourceId group by agentname

Report which list the computers based on the subnet information:

select a.name0,b.IP_Subnets0 from v_R_System a,dbo.v_RA_System_IPSubnets b
where a.ResourceID=b.ResourceID and b.IP_Subnets0 in ('10.10.10.0','10.66.84.0')
group by a.name0,b.IP_Subnets0

You can add multiple subnets in the query syntax after 10.66.84.0 also you can add column names for the computer to be listed in the report as well.