List Preparation for SQL Query

I often need to query a database using a where in filter and a list of items. The items are generally in a list format in an excel or csv file, once copied they are on different lines. This is how I convert to a usable string for an SQL query filter using Notepad++ :

  • Paste list into Notepad++
  • Open the Find and Replace dialog (Ctrl+F)
  • Change search mode to ‘Extended’
  • Find what : \r\n
  • Replace with : ‘,’
  • Add an quote mark at beginning and end of line
Screenshot of Find and Replace dialog in Notepad++

Then you can paste this string into a SQL ‘where in’ clause between the brackets.

Advertisements

Quick Tip – SCCM SQL Secret

Have you ever wanted some information from the SCCM SQL DB, which you can access in the console but can’t find the data easily in the DB? Helpfully, all the data you access in the console is retrieved from the site server – which keeps a record of the queries.

Open the SCCM Console and access the data in the console, this can be loading a list of devices or even a status message query.

Look at the log file “SMSProv.log” located in %Program Files%\Microsoft Configuration Manager\Logs. You should be able to find the query that the site server ran to get the data.

\\SCCMSITESERVER\drive$\%Program Files%\Microsoft Configuration Manager\Logs\SMSProv.log

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

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();
}