Regionalised SCCM Reports

The organisation i work for has 4 regions, these regions have changed over time as the business has moved and shifted. Meaning we don’t have the 4 region structure replicated in our Active Directory. This causes me alot of headaches when building reports. I am frequently asked for reports containing only data from specific regions – so I needed an easy way to ‘Regionalise’ my queries…

In the past, I’ve used several methods to include either the AD Site or the OU the machine is in. However – these methods have their drawbacks :

  • AD Site changes for laptops when they move around
  • The table in SCCM which has OU information for a machine contains multiple rows per machine, and our AD structure doesn’t represent the 4 regions – we have 5 top level OU’s

The table which has the OU information for a particular machine is stored in the following format, with a line for each node in the path :

  • COMPANY.COM/EUROPE
  • COMPANY.COM/EUROPE/COUNTRY
  • COMPANY.COM/EUROPE/COUNTRY/OFFICE
  • COMPANY.COM/EUROPE/COUNTRY/OFFICE/COMPUTERS

The easiest way I have found to tackle this problem is using a temporary table in the SQL query. We add the top level OU’s to this table so that we can join this table to the rest of the data in the query. If you ammend the below to OU names that match the ones in your SCCM DB you will be able to run it – however if you do, you will notice that we still have multiple rows per machine (which we dont want!)

-- Create Temporary Table to Store the OU's for each 'Region'
CREATE TABLE #Regions(
	RegionID int,
	RegionName NVARCHAR(50),
	RegionOU NVARCHAR(100)
)

-- Insert Data into Temporary Table
INSERT INTO 
	#Regions (RegionID, RegionName, RegionOU)
VALUES
	(0, 'AMX', 'COMPANY.COM/AMERICAS'),
	(1, 'AUS', 'COMPANY.COM/AUSTRALASIA'),
	(2, 'EAR', 'COMPANY.COM/EAST ASIA'),
	(3, 'EMEA', 'COMPANY.COM/EUROPE'),
	(4, 'EMEA', 'COMPANY.COM/AFRICA')

SELECT
	sys.Netbios_Name0 as [Hostname],
	ou.System_OU_Name0 as [OU],
	reg.RegionName

FROM
	v_R_System_Valid as sys
	INNER JOIN
	v_RA_System_SystemOUName as ou
		ON 
			sys.ResourceID = ou.ResourceID
	LEFT OUTER JOIN
	#Regions as reg 
		ON
			ou.System_OU_Name0 = reg.RegionOU

The beauty of doing it this way, is that; the only thing you need to do – to remove the unwanted rows is add a ‘WHERE’ clause to your SQL query.

The final query would look something like this :

-- Create Temporary Table to Store the OU's for each 'Region'
CREATE TABLE #Regions(
	RegionID int,
	RegionName NVARCHAR(50),
	RegionOU NVARCHAR(100)
)

-- Insert Data into Temporary Table
INSERT INTO 
	#Regions (RegionID, RegionName, RegionOU)
VALUES
	(0, 'AMX', 'COMPANY.COM/AMERICAS'),
	(1, 'AUS', 'COMPANY.COM/AUSTRALASIA'),
	(2, 'EAR', 'COMPANY.COM/EAST ASIA'),
	(3, 'EMEA', 'COMPANY.COM/EUROPE'),
	(4, 'EMEA', 'COMPANY.COM/AFRICA')

SELECT
	sys.Netbios_Name0 as [Hostname],
	ou.System_OU_Name0 as [OU],
	reg.RegionName

FROM
	v_R_System_Valid as sys
	INNER JOIN
	v_RA_System_SystemOUName as ou
		ON 
			sys.ResourceID = ou.ResourceID
	LEFT OUTER JOIN
	#Regions as reg 
		ON
			ou.System_OU_Name0 = reg.RegionOU
WHERE 
-- This clause removes unwanted rows
	reg.RegionOU IS NOT NULL

-- These lines are required to drop (remove) the temporary table now that we have finished with it. If you are working on a query and it doesnt fully execute, you may be left in a situation where the temporary table is not removed.
GO
DROP TABLE #Regions
Advertisements

Collecting Custom Properties / Fields Using the Registry and Hardware Inventory

I recently implemented a mechanism for collecting warranty end dates using a script which added the date to a registry key, then using hardware inventory to collect the key into SCCM SQL DB.

This is useful if there is information about a client machine which is not stored in WMI.

It worked great and proved a good method of collecting data, In this post i will cover the whole process in detail :

Step 1 – The Script

The first stage is creating a powershell, vbscript or bat file to grab the custom property you are looking for and write it to a registry key. I won’t go into the detail of doing that in this post. I am using a script which reads the setupact.log file in C:\Windows\Panther and returns whether the machine firmware is BIOS or EFI (UEFI) – then writes the info to a reg key. I couldn’t find a field in the registry or in WMI on Windows 7 which stores this information. (Hence the script and this post!)

Step 2 – The Deployment

The script needs to be deployed and run on every machines we want to collect information from. I find it easiest to create a source folder, place the powershell script in there and create a bat file ‘wrapper’ to call the powershell script and temporarily change the execution policy.

powershell.exe -ExecutionPolicy Bypass -NoLogo -NonInteractive -NoProfile -WindowStyle Hidden -File "script.ps1" >> "%temp%\script.log"

For the detection method, use the following :

  • Setting Type: Registry
  • Hive: HKEY_LOCAL_MACHINE (or other if you are using different hive)
  • Key: SOFTWARE\CompanyName\YourKey (this is the key you have written your info too)
  • Value: NameOfKey
  • Data Type: String (or other if you are using different data type)
  • I used “This registry setting must satisfy the following rule to indicate the presence of this application”
  • Operator “Not equal to”
  • Value: <blank>

Sometimes my script would fail to gather the info required but would create the key with null value, i didnt want my detection rule to be satisified by this. (I know i was lazy and should have added more error checking the script! But this had the same outcome….)

Step 3 – The Mof File

Great! We have the info in the registry, but now we want to add it to the SCCM DB – for ease of querying and reporting etc…

This method was inspired by this brilliant post – https://sccmguru.wordpress.com/2014/04/24/hp-and-lenovo-warranty-information-in-configuration-manager-2012-r2/

I suggest using the methods described in the above post to create your MOF file and import the changes into ConfigMgr.

Step 4 – The Result

Once you have deployed the hardware inventory custom settings, the deployment has run on some machines and hardware inv has run you should have some results! If not, something is wrong, retrace your steps and check for mistakes…

Windows Driver Overview

I have spent a lot of time working on a solution to update drivers on existing machines lately. Once I started working on this, I realised that I knew less about drivers than i thought i did. I thought i would write this article with a summary of technical details about windows drivers.

Who Produces Drivers, Microsoft?
Drivers are produced by Independent Hardware Vendors (IHV) and then submitted to Microsoft to gain Windows Hardware Certification (to be signed). System manufacturers such as Dell and HP (OEMs) do not produce drivers, but publish the ones for their systems produced by IHV’s. Sometimes OEM’s will work with IHV’s to improve drivers or customize them to interact with other components.

Types of Driver

  • InBoxDrivers which come with the Operating System are called “InBox Drivers” – with each version of Windows, Microsoft adds more and more of these drivers.
  • Windows UpdateWhen a driver is going through the Windows Hardware Certification process, the IHV has the option to add the driver to Windows Update. This means that you can search for a PNP ID (more about these later) here and find a nice signed driver to install on your system.
  • Exe PackageThese are drivers which have been re-packaged by an IHV or OEM into a convenient format to download and double click to install. These are great for normal users, but for system manager hoping for automation they are a nightmare. On occasion the original clean driver files can be extracted from the exe, but they may require user input to install correctly. Which is a problem when you want a silent installation for deployment.
  • FakeThere are numerous websites pretending to offer a magic tool to scan and update all drivers on your system – unless this tool is made by your system builder (Dell, HP, Lenovo, MSI, ASUS etc…) DO NOT USE! Read this article on How-To Geek for more information.

WTF is PNP?
PnP stands for Plug and Play and is a standardization system that makes the detection of hardware in a system easier. More Information
Vendors should incorporate unique ID’s into hardware components. These consist of several sections of ID. (Hardware ID, Vendor ID, SubSystem ID).

e.g. PCI\VEN_1000&DEV_0001&SUBSYS_00000000&REV_02

Woah – Slow Down, PNP ID?
To see the hardware id of a device in a system – do the following…

  • Open Device Manager
  • Right Click on a Device from the Tree and Select Properties
  • Change to the Details Tab
  • Select Hardware ID for the Property

Hardware IDs

How Does Windows Match Drivers and Hardware IDs?
The simple answer to this, is that each driver has a list of Hardware ID’s that it is compatible with integrated into the files of the driver (the *.inf files).

Extract from an *.inf file.  For the geeks out there - Intel's Vendor ID = 8086 :-)
Extract from an *.inf file. For the geeks out there – Intel’s Vendor ID = 8086 🙂

I’m Confused!
You are not alone… The OS get’s confused as well, what with all these ID’s, OEM’s, IHV’s etc… So how do Microsoft handle the issue? – Driver ranking!

Driver Ranking is used to choose an appropriate driver from a list, when there may be multiple drivers applicable. To do this, Windows sorts the drivers and gives them a rank.

What Criteria does it use?
I’m not going to go into detail about this, but the following is the basic guide for more information see – How Does Windows Rank Drivers

  • Signing
  • Plug and Play ID Match
  • Driver Date
  • Driver Version

Before any drivers are loaded by the operating system, they must be ‘staged’ into the DriverStore. The driverstore is a secure location of drivers, which the operating systems dip’s into to find drivers for devices which require them. DPInst.exe can be used to ‘stage’ the drivers – once staged they will be ranked and if they are the lowest rank will be loaded by the OS.

DPInst.exe

I have a found a utility that makes automated driver updates on machines via SCCM (or other methods) very easy. I will publish another article later in the week about it, with a Hot-To guide on updating drivers safely.

ConfigMgr Distribution Point Priority

Using System Center – Configuration Manager 2012 R2 we recently had some issues with routing, iscsi interfaces and content distribution. We needed to speed up distribution to a specific distribution point for some corrupt packages in order to get software installed at an Office in the Middle East.

So we needed the content to ‘jump’ the queue. There are no options in the SCCM Console to change DP priority order (that I have found), but luckily Microsoft have provided a way of doing it.

Note : As noticed by flatfour67 in the comments this may not work for DP’s assigned to secondary sites.

The following PoSH (Powershell) will list your Distribution Points and the Priority Assigned to them.

$dpinfo = Get-WmiObject -Query "SELECT NALPath, Priority, SiteCode, TransferRate, Description FROM SMS_DistributionPointInfo" -Namespace "ROOT\SMS\site_SITECODE" -ComputerName "MP_ServerName" | select NALPath, Priority, SiteCode, TransferRate
$dpinfo | Out-GridView
## Yes it could be done on one line, but I like doing it this way. After i have closed the GridView window i am still able to access the data without having to query WMI on the server again...

Default priority is 200, anything lower has a higher priority (content will be sent here first) and vice versa. Available values are 1 -> 300.

So How Do I Change the Priority?

Continue reading “ConfigMgr Distribution Point Priority”

Unable to Pin SCCM Support Center to Taskbar in Windows 7

I use this great tool for SCCM troubleshooting – http://blogs.technet.com/b/configmgrteam/archive/2015/01/26/january-2015-update-to-configuration-manager-support-center-has-been-released.aspx

Its like CMTrace on Speed!

But there is an issue with it, for some reason I couldn’t pin the Icon to my Start Bar. Which i want to do because I use a few times each day (we have too many things going wrong i know!)

For some reason M$ have implemented a list of reserved words, and if you have any of these in the application title you are unable to pin the program… The list is in the registry :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\FileAssociation\AddRemoveNames

To fix the issue, just remove “Support” from the list. You still won’t be able to pin it via right click on the start menu – but opening the program then choosing “Pin to Taskbar” from its context menu will work.

Running Batch SSRS (SQL Server) Reporting Services Reports using Powershell

We recently needed to run a one page report 10,000 times. Using previous methods of nesting reports using Report Builder and SSRS didn’t work. We encountered timeouts and memory issues on the server. We could have spent time changing timeout values and figuring out how to overcome this using SSRS. But we found an easier way….

Previously I had written a small C# application using the Microsoft Report Viewer Runtime (a winforms component) to run a batch of reports, create a folder structure and output to PDFs in the respective folders. I decided to re-use this solution, but tailor it to the new reports that we needed.

I encountered an issue with my visual studio and needed to get this batch report sorted, so we looked into using powershell. Below is an anonomised version of the script we used.

It uses the Microsoft Report Viewer Runtime 2012 (dll is v10) to call the report and export to PDF. Using Powershell to interact with .NET is easy and allows you to leverage the short hand coding nature of powershell with the wide array of easy to use libraries that .NET provides.

## Reference ReportViewer Library
[void] [System.Reflection.Assembly]::Load("Microsoft.ReportViewer.WinForms, `
Version=10.0.0.0, Culture=neutral, `
PublicKeyToken=b03f5f7f11d50a3a")</code>

## Report viewer object
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer

## Set Report Processing to server mode
$rv.ProcessingMode = "Remote"

## Set the report server base url
$rv.ServerReport.ReportServerUrl = "http://servername/reportserver"

## Set the report path
$rv.ServerReport.ReportPath = "/base folder/folder/ReportName"

## Create an array with all the required ids
$ids = (0..10000)

## Loop through all the ids
foreach ($id in $ids)
{
# Parameters array
$params = new-object 'Microsoft.Reporting.WinForms.ReportParameter[]' 1
$params[0] = new-Object Microsoft.Reporting.WinForms.ReportParameter("ParameterName", $id, $false)

# Set Report Parameters
$rv.ServerReport.SetParameters($params)

# Refresh / Run Report
$rv.RefreshReport()

# Out vars
$mimeType = $null
$encoding = $null
$extension = $null
$streamids = $null
$warnings = $null

# PDF file generation
$bytes = $rv.ServerReport.Render("PDF", $null,
[ref] $mimeType,
[ref] $encoding,
[ref] $extension,
[ref] $streamids,
[ref] $warnings);

## Output to file
$file = "C:\FolderName\$id.pdf";

$fileStream = New-Object System.IO.FileStream($file,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);


$fileStream.Close();
}

Remote Access (VNC) to OSD Build During WinPE

We have been seeing more and more issues with OSD and the latest model Dell computers, i work for an organisation with quite a few small sites and there are not I.T. specialists at each site.

Trying to explain to someone on the phone how to copy files via command line to a USB stick (when the log could be in 1 of about 7 places! – we use MDT) is difficult and time consuming.

We needed a better way….

At first I started looking into a tool I heard about a few years back called DaRT

There was some confusion in my organisation about whether we would / wouldn’t be licensed for the Microsoft Desktop Optimization Pack (which DaRT is part of). It also requires changing the boot image used during OSD, which is not ideal as we use one TS globally and trying to schedule the distribution of a new boot image is difficult.

So i put it on the back-burner, then today we had more issues with OSD and this requirement became more urgent for me. I suddenly realised that VNC server can run in a User Context, so maybe it can be run during WinPE.

A quick google found this excellent article : http://syswow.blogspot.de/2012/05/remote-control-during-sccm-osd-without.html

Within 30 minutes I had VNC server running during our OSD build and I could connect to it successfully. We had already been using BGInfo to stamp the hostname and IP details on the background, so connecting was easy!

Update
I have found some issues with this method, if the hard disk is not formatted when you start the TS then it doesn’t copy the content for VNC from the package and the commandline step fails. I have tried a few things to fix this with no success. Next is to not bother putting the VNC content into a package and copying it directly from a network share.