SCCM Report: Asset Overview

One of the first things people want to start seeing once they have SCCM in place is richer detail on all of the devices that are being managed. I’ve written this report to provide an overview of all devices.

One key thing to call out is the way I structure reports like this is based around creating a temp table and then joining multiple queries together through the ‘UPDATE’ statement, this allows you to on the fly manage multiple values on sub queries to ensure you’re getting the desired data.

Capture.PNG

 DECLARE @TempTable TABLE(
Hostname varchar (100),
CCMClient varchar (10),
OperatingSystem varchar (100),
Manufacturer varchar (100),
Model varchar (100),
Chassis varchar (100),
Serial varchar (100),
IsVirtual varchar (100),
CPU ntext,
CPUCores int,
CPULogical int,
RAM int,
VolumeSize_C int,
VolumeFree_C int
)

INSERT INTO @TempTable (Hostname, OperatingSystem,CCMClient, IsVirtual)

SELECT
dbo.v_R_System.Name0,
dbo.v_R_System.operatingSystem0,
CASE WHEN dbo.v_R_System.Client0=1 THEN 'Yes' ELSE 'No' END,
CASE WHEN dbo.v_R_System.Is_Virtual_Machine0=1 THEN 'Yes' ELSE 'No' END
FROM
dbo.v_R_System

UPDATE @TempTable
SET Manufacturer = (
SELECT DISTINCT
dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0
FROM
dbo.v_GS_COMPUTER_SYSTEM
INNER JOIN dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 IS NOT NULL)
)

UPDATE @TempTable
SET Model = (
SELECT DISTINCT
dbo.v_GS_COMPUTER_SYSTEM.Model0
FROM
dbo.v_GS_COMPUTER_SYSTEM
INNER JOIN dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_COMPUTER_SYSTEM.Model0 IS NOT NULL)
)

UPDATE @TempTable
SET Chassis = (
SELECT DISTINCT
CASE dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
WHEN '1' THEN 'Other'
WHEN '2' THEN 'Unknown'
WHEN '3' THEN 'Desktop'
WHEN '4' THEN 'Low Profile Desktop'
WHEN '5' THEN 'Pizza Box'
WHEN '6' THEN 'Mini Tower'
WHEN '7' THEN 'Tower'
WHEN '8' THEN 'Portable'
WHEN '9' THEN 'Laptop'
WHEN '10' THEN 'Notebook'
WHEN '11' THEN 'Hand Held'
WHEN '12' THEN 'Docking Station'
WHEN '13' THEN 'All in One'
WHEN '14' THEN 'Sub Notebook'
WHEN '15' THEN 'Space-Saving'
WHEN '16' THEN 'Lunch Box'
WHEN '17' THEN 'Main System Chassis'
WHEN '18' THEN 'Expansion Chassis'
WHEN '19' THEN 'SubChassis'
WHEN '20' THEN 'Bus Expansion Chassis'
WHEN '21' THEN 'Peripheral Chassis'
WHEN '22' THEN 'Storage Chassis'
WHEN '23' THEN 'Rack Mount Chassis'
WHEN '24' THEN 'Sealed-Case PC'
ELSE 'Undefinded' END AS 'Chassis'
FROM
dbo.v_R_System
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0!='12')
)

UPDATE @TempTable
SET Serial = (
SELECT DISTINCT
dbo.v_GS_PC_BIOS.SerialNumber0
FROM
dbo.v_GS_PC_BIOS
INNER JOIN dbo.v_R_System ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PC_BIOS.SerialNumber0 IS NOT NULL)
)

UPDATE @TempTable
SET CPU = (
SELECT DISTINCT
dbo.v_GS_PROCESSOR.Name0
FROM
dbo.v_GS_PROCESSOR
INNER JOIN dbo.v_R_System ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PROCESSOR.Name0 IS NOT NULL)
)

UPDATE @TempTable
SET CPUCores = (
SELECT DISTINCT
dbo.v_GS_PROCESSOR.NumberOfCores0
FROM
dbo.v_GS_PROCESSOR
INNER JOIN dbo.v_R_System ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PROCESSOR.NumberOfCores0 IS NOT NULL)
)

UPDATE @TempTable
SET CPULogical = (
SELECT DISTINCT
dbo.v_GS_PROCESSOR.NumberOfLogicalProcessors0
FROM
dbo.v_GS_PROCESSOR
INNER JOIN dbo.v_R_System ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PROCESSOR.NumberOfLogicalProcessors0 IS NOT NULL)
)

Update @TempTable
SET VolumeSize_C = (
SELECT TOP (1)
dbo.v_GS_LOGICAL_DISK.Size0
FROM
dbo.v_GS_LOGICAL_DISK
INNER JOIN dbo.v_R_System ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_GS_LOGICAL_DISK.Size0 IS NOT NULL) AND
(dbo.v_GS_LOGICAL_DISK.DeviceID0 = N'C:') AND
(dbo.v_R_System.Name0 = HostName)
)

Update @TempTable
SET VolumeFree_C = (
SELECT TOP (1)
dbo.v_GS_LOGICAL_DISK.FreeSpace0
FROM
dbo.v_GS_LOGICAL_DISK
INNER JOIN dbo.v_R_System ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_GS_LOGICAL_DISK.DeviceID0 = N'C:') AND
(dbo.v_R_System.Name0 = HostName)
)

Update @TempTable
SET RAM = (
SELECT
SUM(dbo.v_GS_PHYSICAL_MEMORY.Capacity0)/1024
FROM
dbo.v_GS_PHYSICAL_MEMORY
INNER JOIN dbo.v_R_System ON dbo.v_GS_PHYSICAL_MEMORY.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName)
)

SELECT *
FROM
@TempTable

ORDER BY
Hostname

Or you can download the .rdo here and install. One note using this report you must have the OperatingSystem attribute being discovered by AD System Discovery.

If you have questions or want to see extra detail in these reports feel free to comment below and I can assist in extending.

Advertisements

Part 16: Reporting Services

Reporting Services are essential for getting data out of SCCM, this provides operational insights and assists in supporting and managing the platform.

  1. Install SQL Reporting Services feature – We already compeleted this in PART 4: INSTALLING SQL 2016.
  2. Open Reporting Services Configuration Manager – Start > All Programs Microsoft SQL Server 2016 > Reporting Services Configuration Manager
  3. Click Connect
    2017-09-25_17-13-40.png
  4. Click database
    2017-09-25_17-15-30.png
  5. Click change database
    2017-09-25_17-17-55.png
  6. Select ‘create a new report server database’
    2017-09-25_17-19-18.png
  7. Test Connection and click next
    2017-09-25_17-22-28.png
  8. Click Next
    2017-09-25_17-23-42.png
  9. Click Next
    2017-09-25_17-24-25.png
  10. Click Next
    2017-09-25_17-25-11.png
  11. Confirm successful
    2017-09-25_17-26-34.png
  12. Select Web Service URL and click apply
    2017-09-25_17-32-11.png
  13. Select Web Portal URL and click apply
    2017-09-25_17-34-01
  14. Open SCCM Console
  15. Browse to Administration > Sites Configuration > Servers and Site System Roles
  16. Select Add Site System role
  17. Click Next
    2017-09-25_17-43-57.png
  18. Click Next
    2017-09-25_17-44-40.png
  19. Select Reporting Service point and click next
    2017-09-25_17-45-09.png
  20. Click ‘Verify’ and set your reporting services account.
    2017-09-25_17-48-29.png
  21. Click Next
  22. Confirm successful
    2017-09-25_17-49-40.png
  23. After about 5 minutes you should start seeing reports populated under Monitoring > Reports.
    2017-09-25_17-55-42.png

Part 7: Software Update Point & SCUP (With HTTPS)

If you’re looking to manage patches with SCCM, and lets face it why wouldn’t you be, then you’ll need to install the software update point role. In this post we’ll install and configure everything you need to get started including the System Center Update Publisher which allows you to deploy non Microsoft updates via SCCM.

In Part 3: Prep & Pre-reqs we installed WSUS, lets get to configuring everything.

Continue reading

Part 6: Upgrading SCCM Current Branch

Now that you have ConfigMgr setup it’s time to upgrade it to the latest version. This is a relatively straight forward process and applies to all versions of current branch from 1511 onward. In the last post I installed 1606 so that’s what we’ll be using.

NB: You must have the Service Connection point installed and configured to upgrade.

At a glance:

  1. Confirm no operational issues with SCCM sites
  2. Review new SCCM version requirements, 1702 for example removes support for 2008 server. So you will need to upgrade these sites to 2012 or 2016 before upgrading.
  3. Patch, patch, patch!
  4. Uninstall any deprecated SCCM Sites system roles before upgrading
  5. Disable DB replicas on all primary sites (if you’re using them)
  6. Disable maintenance tasks
  7. Run Pre-req check for update
  8. Backup DBs (CAS and Primary)
  9. Test DB Backups
  10. Backup any custom .mof files
  11. Restart all Site Systems
  12. Upgrade
  13. Deploy new SCCM Admin Console
  14. Reconfigure DB Replicas
  15. Upgrade Clients
  16. Reconfigure clients

Continue reading

Part 4: Installing SQL 2016

In the previous posts we’ve setup the lab and done the prep work for the SCCM Primary site. In my lab I’m installing SQL on the same server as the Primary Site server (SCCM-P01). There’s a fair bit of healthy debate as to whether it’s better to co-host or have a dedicated standalone SQL server. I’ve done both and can say that in my experience any performance improvement is negligible for the size environments I’ve seen it in.

So let’s get to it, jump on the server you’re going to install SQL on.

  1. Download SQL Server Standard, I’m using 2016. You can use any of the versions listed here.
  2. Run Setup.exe
  3. Click New Installation
    2017-04-24_14-40-43.png
  4. Enter product key details and click next.
    2017-04-24_21-19-39
  5. Accept license terms and click next
  6. Check use microsoft update and click next
    2017-04-24_21-21-47.png
  7. Check all updates and click next
    2017-04-24_21-22-50.png
  8. Review pre-req check and click next
    2017-04-24_21-27-42.png
  9. Check database engine services and reporting services  and change the feature installation directory to the SQL directory, mines ‘E:\’
    2017-04-24_21-32-11.png
  10. Specify an instance, I’m using the default.
    2017-04-24_21-34-19.png
  11. Set all services to start with the service account created for SQL earlier ‘SA_SCCM_SQL’ and automatic except the SQL Server Browser
    2017-04-24_22-32-03.png
  12. Select the collation tab and set it to ‘SQL_Latin1_General_CP1_CI_AS’. This is critical and if it’s configured incorrectly it can lead to a failed installation, unsupported by Microsoft and may prevent updates installing for SCCM.
    2017-04-24_22-37-59.png
  13. Add SCCM Server Admins to administrators list
    2017-04-25_9-19-57.png
  14. Under Data Directories tab change the locations to the below directories.
    2017-04-25_9-23-17.png
  15. Under the TempDB tab change the data directory for the TempDB to your tempDB volume and the log.
    2017-04-25_9-25-17.png
  16. On Reporting Services select install only
    2017-04-25_9-27-29.png
  17. Click Install
  18. Confirm all components installed successfully
    2017-04-25_9-35-08
  19. Set SPN by running the following commands:
    setspn -A MSSQLSvc/SCCM-P01:1433 LAB\SA_SCCM_SQL
    setspn -A MSSQLSvc/SCCM-P01.lab.local:1433 LAB\SA_SCCM_SQL

    2017-04-25_9-40-58.png

  20. SQL 2016 doesn’t install management studio as part of the install so you need to download and install manually. You can download it here.
  21. Click Install
    2017-04-25_15-36-35.png
  22. Click Close
    2017-04-25_15-46-43.png
  23. Configure Memory allocation
  24. Open SQL Server Management Studio (with an account that has admin rights to your SQL instance)
  25. Right click the server in object explorer and select properties
    2017-04-25_17-51-13.png
  26. Select memory and change the minimum to 8192 and the maximum to 12288 (should be 80% of the servers memory)
    2017-04-25_18-41-19.png
  27. Open SQL Server Configuration Manager
  28. Browse SQL Server Network Configuration>Protocols for instance and right click TCP/IP>Properties
    2017-04-25_18-44-34.png
  29. Configure protocol as per the below
    2017-04-25_18-46-55.png
  30. Select IP Addresses tab
  31. Under IP1 set to the below settings
    2017-04-25_18-51-20
  32. All other IP entries and IP All should be configured as per the below
    2017-04-25_18-52-10.png
  33. Dynamic ports should be configured as per the below
    2017-04-25_18-53-27
  34. Restart the SQL Server Service
    2017-04-25_18-54-26.png
  35. Ready for ConfigMgr!

Part 1: Hyper-V, Networks, DC, DNS

This post will really only be applicable where you are running this up at home or a lab. Please don’t use this as guidance on building a production domain…

Some details on my hypervisor spec –

CPU – Intel Xeon E5-1650 @ 3.5GHz
RAM – 64GB
C:\ – 256GB SSD
V:\ – 1TB SSD

Hypervisor

  1. Install Windows Server 2016 Datacenter, media can be downloaded here.
  2. Set a static IP address, mines 192.168.0.100 as it’s sitting on my home network.
  3. Give your server a name, mines HV01 as in hypervisor 1.
  4. Install Hyper-v, open PowerShell and run the below:
    Install-WindowsFeature –Name Hyper-V -IncludeManagementTools -Restart

    2017-03-13_12-24-54

  5. Open Hyper-V Manager
    2017-03-13_12-44-09
  6. Open Hyper-V Settings
    2017-03-13_12-46-35
  7. Change Virtual Hard Disks to ‘V:\Hyper-V\Virtual Hard Disks’
    2017-03-13_12-52-24
  8. Change Virtual Machines to ‘V:\Hyper-V\Virtual Machines’
    2017-03-13_12-56-22.png
  9. Open Virtual Switch Manager
    2017-03-13_17-55-53.png
  10. Select New virtual network switch, external and click Create Virtual Switch
    2017-03-13_17-53-35
  11. Give it a name and select external network.
    2017-03-13_17-59-32.png
  12. Install data deduplication, open PowerShell and run the below:
    Import-Module ServerManager
    Add-WindowsFeature -name FS-Data-Deduplication

    2017-03-13_15-24-50

  13. In Server Manager browse to File and Storage Services>Volumes
    2017-03-13_15-29-10.png
  14. Right click on the volume you’re storing your VM’s on, ‘V:’ in my case and select ‘Configure data deduplication’.
    2017-03-13_15-35-24
  15. Change Data Deduplication to Virtual Desktop Infrastructure and then click Set Deduplication Schedule.
    2017-03-13_15-39-04.png
  16. Configure an appropriate schedule, mines pretty aggressive because it’s a lab and I’m not fussed when it runs.
    2017-03-13_15-43-55.png
  17. Install all Windows Updates

Domain Controller 1

  1. Create a new Virtual Machine with the below
    Name: DC01
    Generation: 2
    Startup Memory: 1024
    Use Dynamic memory for this VM: Yes
    Connection: vNet External
  2. Install Windows Server 2016 Standard
  3. Set a static IP address, mines 192.168.0.101
  4. Give your server a name, mines DC01 as in Domain Controller 1
  5. Install Active Directory Domain Services, DNS, open PowerShell and run the below:
    Install-windowsfeature AD-Domain-Services
    Install-Windowsfeature DNS

    2017-03-13_22-05-34.png

  6. In Server Manager click on the flag and run Promote this server to a domain controller.
    2017-03-13_22-06-49
  7. Select Add a new forest and enter a domain name and click Next.
    2017-03-13_22-09-20
  8. Enter a password and click Next.
    2017-03-13_22-12-32
  9. Click Next.
    2017-03-13_22-14-06.png
  10. Set an NetBIOS name and click next.
    2017-03-13_22-15-31.png
  11. Configure locations, I’m using the defaults.
    2017-03-13_22-17-02.png
  12. Review options and click Next.
    2017-03-13_22-18-16.png
  13. Review pre-requisite check and click install.
    2017-03-13_22-24-25
  14. Server will be restarted
  15. Launch DNS from Server Manager
    2017-03-16_17-41-14.png
  16. Select DC01>Forwarders>Edit
    2017-03-16_17-43-23.png
  17. Enter Google or another external DNS server, I’m using 8.8.8.8 then hit enter.
    2017-03-16_17-45-58.png
  18. Confirm you can resolve your new domain with nslookup.
    2017-03-16_17-48-12

Domain Controller 2

  1. Create a new Virtual Machine with the below
    Name: DC02
    Generation: 2
    Startup Memory: 1024
    Use Dynamic memory for this VM: Yes
    Connection: vNet External
  2. Install Windows Server 2016 Standard
  3. Set a static IP address, mines 192.168.0.102
  4. Give your server a name, mines DC02 as in Domain Controller 2
  5. Join the server to your newly created domain
  6. Install Active Directory Domain Services, DNS, open PowerShell and run the below:
    Install-windowsfeature AD-Domain-Services
    Install-Windowsfeature DNS

    2017-03-13_22-05-34.png

  7. In Server Manager click on the flag and run Promote this server to a domain controller.
    2017-03-16_18-00-20.png
  8. Select Add a domain controller to an existing domain, enter your domain name and click Next.
    2017-03-16_18-02-01.png
  9. Enter a password and click next.
    2017-03-16_18-03-58.png
  10. Click Next
    2017-03-16_18-04-56.png
  11. Click Next
    2017-03-16_18-09-02
  12. Set paths and click Next
    2017-03-16_18-09-59.png
  13. Review config and click next.
    2017-03-16_18-10-19.png
  14. Confirm pre-reqs are met and click install.
    2017-03-16_18-12-00
  15. Confirm you can resolve your new domain with nslookup.
    2017-03-16_17-48-12

References:
Windows Server 2012 Hyper-V Best Practices (In Easy Checklist Form)

 

SCCM SQL Queries: Software Update Compliance by Software Update Group

This is particularly useful for charting compliance of your environment over a period of time by each Software Update Group, I’ve split it out into the total compliance, servers and workstations so that they can individually be charted. The table cane modified as well in the declared @TempTableVariable table and using the ‘UPDATE’ statement.

If charting, use a line graph to chart each of the individual columns as values.Feel free to ask questions if you need a hand getting this working 😉


Declare @TempTableVariable TABLE(
SoftwareUpdateGroup VARCHAR(500),
TotalCompliance INT,
ServerCompliance INT,
WorkstationsCompliance INT)

INSERT INTO @TempTableVariable (SoftwareUpdateGroup)
SELECT
dbo.v_AuthListInfo.Title
FROM
dbo.v_AuthListInfo
ORDER BY
dbo.v_AuthListInfo.Title

--Total Compliance
UPDATE @TempTableVariable SET TotalCompliance=(
SELECT
round((CAST(SUM (
CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) AS FLOAT)/COUNT(*) )*100,2) AS 'Success %'
FROM
v_Update_ComplianceStatusAll UCS
INNER JOIN v_r_system sys on ucs.resourceid=sys.resourceid
INNER JOIN v_UpdateCIs on ucs.resourceid=v_UpdateCIs.CI_ID
INNER JOIN v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
INNER JOIN v_collection coll on coll.collectionid=fcm.collectionid
INNER JOIN v_AuthListInfo LI on ucs.ci_id=li.ci_id
WHERE
v_UpdateCIs.IsDeployed =1 AND
v_UpdateCIs.IsExpired=0 AND
v_UpdateCIs.InUse=1 AND
v_UpdateCIs.IsTombstoned=0 AND
v_UpdateCIs.IsEnabled=1 AND
(li.title=SoftwareUpdateGroup)
GROUP BY
li.title)

--Server Compliance
UPDATE @TempTableVariable SET ServerCompliance=(
SELECT
round((CAST(SUM (
CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) AS FLOAT)/COUNT(*) )*100,2) AS 'Success %'
FROM
v_Update_ComplianceStatusAll UCS
INNER JOIN v_r_system sys on ucs.resourceid=sys.resourceid
INNER JOIN v_UpdateCIs on ucs.resourceid=v_UpdateCIs.CI_ID
INNER JOIN v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
INNER JOIN v_collection coll on coll.collectionid=fcm.collectionid
INNER JOIN v_AuthListInfo LI on ucs.ci_id=li.ci_id
WHERE
v_UpdateCIs.IsDeployed =1 AND
v_UpdateCIs.IsExpired=0 AND
v_UpdateCIs.InUse=1 AND
v_UpdateCIs.IsTombstoned=0 AND
v_UpdateCIs.IsEnabled=1 AND
sys.operatingSystem0 LIKE '%SERVER%' AND
(li.title=SoftwareUpdateGroup)
GROUP BY
li.title )

--Workstations Compliance
UPDATE @TempTableVariable SET WorkstationsCompliance=(
SELECT
round((CAST(SUM ( CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) AS FLOAT)/COUNT(*) )*100,2) AS 'Success %'
From
v_Update_ComplianceStatusAll UCS
INNER JOIN v_r_system sys on ucs.resourceid=sys.resourceid
INNER JOIN v_UpdateCIs on ucs.resourceid=v_UpdateCIs.CI_ID
INNER JOIN v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
INNER JOIN v_collection coll on coll.collectionid=fcm.collectionid
INNER JOIN v_AuthListInfo LI on ucs.ci_id=li.ci_id

WHERE
v_UpdateCIs.IsDeployed =1 AND
v_UpdateCIs.IsExpired=0 AND
v_UpdateCIs.InUse=1 AND
v_UpdateCIs.IsTombstoned=0 AND
v_UpdateCIs.IsEnabled=1 AND
sys.operatingSystem0 NOT LIKE '%SERVER%' AND
(li.title=SoftwareUpdateGroup)
GROUP BY
li.title )

SELECT
SoftwareUpdateGroup,
TotalCompliance,
ServerCompliance,
WorkstationsCompliance

FROM
@TempTableVariable
ORDER BY
SoftwareUpdateGroup