Eswar Koneti's Blog

Today’s post is going to listing down computers assigned to specific site with count and this will be linked to another report that gives you computer asset information.

If you have referred my previous post on computer asset information http://eskonr.com/2011/12/sccmconfigmgr-report-for-computer-asset-information-serialnumber-manufacturermodelprocessorip-addresshardaware-scan/ OR http://eskonr.com/2009/12/sccm-report-for-computers-asset-information-including-sn-number-and-model-name/ ,then it would be easy for you make it.

You need to create 2 reports 1)Count of Computers from AD site 2) list the computers assigned to specific Site

Create new report from reports node for 2) list the computers assigned to specific Site using the below SQL query(quotes posted in this blog are converted to fancy,please replace them with correct ones in your report)  :

SELECT
A.Name0,
MAX (B.SerialNumber0) AS ‘Serialnumber’,
A.Manufacturer0,
A.Model0, C.Name0 AS ‘Processor’,
D.TotalPhysicalMemory0 AS ‘Memory (KBytes)’,
MAX ( E.Size0 ) AS ‘Size (MBytes)’,
MAX (F.MACAddress0) AS ‘MAC Adress’,
MAX (F.IPAddress0) AS ‘IP Adress’,
G.AD_Site_Name0 AS ‘AD Site’,
MAX (A.UserName0) AS ‘Last user logged in’,
H.Caption0 AS ‘Operating System’,
H.CSDVersion0 AS ‘Service Pack’,
G.Creation_Date0 AS ‘Creationdate in SMS’,
I.LastHWScan
FROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGUR F,
v_R_System G,
v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS I
WHERE
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceID
AND G.AD_Site_Name0=@ADSITE
GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan

Create a prompt for ADSITE with blank query as shown below :

Now create another report 1)Count of Computers from AD site with below SQL query :

Select a.AD_Site_Name0 as [AD Site],Count(a.Netbios_Name0) as [Total Computers] from v_R_System a
group by a.AD_Site_Name0
order by a.AD_Site_Name0

Once done, right click on above report and go to properties of 1)Count of Computers from AD site ,click on link

in link type select link to another report under report select the 2nd report  2) list the computers assigned to specific Site

and click on the prompt (hand symbol) and select column 1 as prompt shown like below.

You are done now !