WannaCry Patch Compliance Report

• 15 May 13:40 ,Updated the query to show Windows 10 (Build 15063) creators Update as Patched
• 15 May 14:10, Had made a mistake in the last version ,with the update status. fixed now. 
• 15 May 15:00 Expanded the list of updates.

• 16 May 10:00, This doesn't work as expected ,when a update is superseded the old one is set as "Not Requered". Got a new version almost ready that uses Baseline/CI with a WMI query against Win32_QuickFixEngineering, seems to work much better. Keep checking back, i'll post it soon. 
• 16 May 14:30 ,New version of the report that uses baseline available for download.
• 16 May 17:00 ,tweaked the baseline WMI query to support Builds higher than 15063
• 24 May ,Updated the Baseline ,added 3KB's for Windows 7 and fixed the buildnumber "check" (greater than 15053 statement should now work as expected)
• 29.June Added KB4022727  KB4022714 AND KB4022715 to the list (Windows 10 June Updates) Havent updated the CAB file ,just copy the powershell code bellow and update the CI.

I just got back from a one week vacation and the everybody is freaking out about the WannyCry ransomware.
I spend the whole morning working with people hanging over my shoulder watching me write SQL queries to get the information they where looking for.
The vulnerability that WannyCry uses was patched in Bulletin "MS17-010" ,but that update is expired and superseded by other updates that again are expired and superseded...and so on. This makes it hard to tell if computers are patched against WannaCry or not.

So! i created a report to show if machines in a collection are patched against WannaCry or not.
The way the report works is that I've created a list of Updates that contain the patch for the WannyCry vulnerability and if a machine have one of those patches installed it's shows as "Patched" if not it shows as "Not-Patched / Unknown".

The List of Updates that it checks against is easily changed. I've added the updates that i could find that fixes this issue, but this was a rush job so there may be errors in the list. If you find any errors or updates that are missing from the list ,post a comment bellow.

Created a new version of the report that uses Configuration Baselines to determine patch state ,it queries WMI on the machines(Win32_QuickFixEngineering) for one or more of the KB patches that fixes the problem.

New report is a bit more work since you have to deploy the Configuration Baseline (Included in the zip file), but the new report should give better results. Also slower since the baseline needs to be evaluated on the clients.

I've been testing it myself all morning and it seems to work as expected, let me know.
Old version is still available.

Download Report (SUP Version)
(If you still want to use this report ,check out this tip on Reddit. you can set updates not to expire immediately when superseded)

Download New Report (Baseline Version)
(New version have a version number in the footer)

Note! This is the first version of the report and it was created in a rush. If you find mistakes or errors post a comment bellow.
I'll update the report as soon as i can when we find errors or updates that should be added/removed from the the list.

List of update is based on this blog post and the "Microsoft WannaCrypt Customer Guidance Document":
https://www.askwoody.com/2017/how-to-make-sure-you-wont-get-hit-by-wannacrywannacrypt/ 

People don't seem to agree if Windows 10 versions other that Creators Update(1703) is immune or not. For now Windows 10 is part of the report.

Both versions of the report use the same logic to determine if a computer is patched or not.
The SUP version checks against the Software Update Views, but there is a issue with superseded updates if you don't increase the time between the update is superseded and the update expires.

The Baseline version checks for the updates in WMI on the clients and reports back compliance state that shown in the report. This is a better method ,but it's slower. You have deploy the Configuration Baseline and wait for the clients to report back compliance state.

To Deploy the Baseline Configuration 

This baseline only monitors ,it does not remediate anything ,should be safe to deploy.

1. Right Click Configuration Baselines (SCCM Console > Asset and Compliance Settings) and select "Import Configuration Data"
2. Click Add and select the CAB file included in the download (You get a warning that it's not signed ,click yes)
3. You should now have a WannaCry_Patched Baseline in the list ,Right Click it and select Deploy.
4. Choose a Collection and set the Schedule to 1 day or something faster. 

Can't import the baseline? Watch this video to learn how to create it manually.

Note! The Configuration Baseline should be called  "WannaCry_Patched",the rest don't matter
(You can call it something else ,but then you have to edit the report:-))

Here is the Powershell script for the baseline:

$OS = Get-WmiObject -Query "select * from Win32_OperatingSystem"

if ([convert]::ToInt32($os.BuildNumber) -cge 15063)

{

Return $true

}

else

{

$queryresult = Get-WmiObject -query "select * from Win32_QuickFixEngineering
where HotFixID = 'KB4015553' OR HotFixID = 'KB4019215' OR HotFixID = 'KB4015549'
OR HotFixID = 'KB4015552' OR HotFixID = 'KB4012598' OR HotFixID = 'KB4019264'
OR HotFixID = 'KB4012215' OR HotFixID = 'KB4012213' OR HotFixID = 'KB4012212'
OR HotFixID = 'KB4012217' OR HotFixID = 'KB4015551' OR HotFixID = 'KB4019216'
OR HotFixID = 'KB4012216' OR HotFixID = 'KB4015550' OR HotFixID = 'KB4013429'
OR HotFixID = 'KB4019472' OR HotFixID = 'KB4015217' OR HotFixID = 'KB4015438'
OR HotFixID = 'KB4016635' OR HotFixID = 'KB4019473' OR HotFixID = 'KB4015219'
OR HotFixID = 'KB4013198' OR HotFixID = 'KB4012606' OR HotFixID = 'KB4015221'
OR HotFixID = 'KB4019474' OR HotFixID = 'KB4012214' OR HotFixID = 'KB4019265'
OR HotFixID = 'KB4019263' OR HotFixID = 'KB4015546' OR HotFixID = 'KB4022727'
OR HotFixID = 'KB4022714' OR HotFixID = 'KB4022715'"

if ($queryresult)

{

Return $true

}

else

{

Return $false

}

}

Here are some SQL queries to show a Computers status for these updates:

DECLARE @computername VARCHAR(40)
SET @computername = 'Insert Computername here'

SELECT A.resourceid,
       E.name0,
       e.user_name0,
       D.title,
       D.articleid,
       D.daterevised,
       A.status,
       CASE
         WHEN A.status = '0' THEN 'Detection state unknown'
         WHEN A.status = '1' THEN 'Update is not required'
         WHEN A.status = '2' THEN 'Update is required'
         WHEN A.status = '3' THEN 'Update is installed'
         ELSE ''
       END AS StatusName,
       F.statename
FROM   v_update_compliancestatusall A
       INNER JOIN v_updateinfo D
               ON A.ci_id = D.ci_id
       INNER JOIN v_r_system E
               ON A.resourceid = E.resourceid
       LEFT JOIN v_statenames F
              ON A.lastenforcementmessageid = F.stateid
                 AND F.topictype = 402
WHERE
  E.name0 = @computername
       AND D.articleid IN ( '4015553','4019215','4015549','4015552','4012598','4019264','4012215','4012213','4012212','4012217','4015551','4019216','4012216',
'4015550','4013429','4019472','4015217','4015438','4016635','4019473','4015219','4013198','4012606','4015221','4019474','4012214','4019265','4019263','4015546','4022727','4022714','4022715') 
ORDER  BY resourceid,
          D.daterevised DESC,
          F.statename ASC 


Or this one to show all Clients in a collection that are in the process of installing one or more of these updates:

DECLARE @Collection VARCHAR(40)
SET @Collection = 'Insert CollectionID Here'

SELECT A.resourceid,
       E.name0,
       e.user_name0,
       D.title,
       D.articleid,
       D.daterevised,
       A.status,
       CASE
         WHEN A.status = '0' THEN 'Detection state unknown'
         WHEN A.status = '1' THEN 'Update is not required'
         WHEN A.status = '2' THEN 'Update is required'
         WHEN A.status = '3' THEN 'Update is installed'
         ELSE ''
       END AS StatusName,
       F.statename
FROM   v_update_compliancestatusall A
       INNER JOIN v_fullcollectionmembership B
               ON A.resourceid = B.resourceid
       INNER JOIN v_collection C
               ON B.collectionid = C.collectionid
       INNER JOIN v_updateinfo D
               ON A.ci_id = D.ci_id
       INNER JOIN v_r_system E
               ON A.resourceid = E.resourceid
       LEFT JOIN v_statenames F
              ON A.lastenforcementmessageid = F.stateid
                 AND F.topictype = 402
WHERE  C.collectionid = @Collection
       AND A.status != 3
       AND F.statename IS NOT NULL  
       AND D.articleid IN ( '4015553','4019215','4015549','4015552','4012598','4019264','4012215','4012213','4012212','4012217','4015551','4019216','4012216',
'4015550','4013429','4019472','4015217','4015438','4016635','4019473','4015219','4013198','4012606','4015221','4019474','4012214','4019265','4019263','4015546','4022727','4022714','4022715') 
ORDER  BY resourceid,
          D.daterevised DESC,
          F.statename ASC 

Creating Report Subscriptions

Small followup post to the report i released yesterday

The reason i released that particular report is that i saw a discussion somewhere (Reddit or the winadmins slack) on that people outside the Configmgr team never use reports or any other means of staying updated on how well the clients are managed.
I can understand that for a sysadmin/manager that don't work with Configmgr on a daily basis the wall of reports with vague names can be confusing and hard to use.

Half of the solution to this is creating custom reports that are easy to use and read, the other part of the solution is subscriptions. I don't expect that the users them self are going to find the relevant reports and setup subscriptions ,I create the subscription for them.

We have 18-19 sites that have their own local IT-staff ,they get the "Collection Summary" report for their site(Collection) by email every Monday. I also have  a few other reports that i send out on a regular basis.
Some of them are just lists of things that are broken. I call them "fix lists" that are so simple that anyone can start working on them ,they don't have to be an Configmgr expert and know where to find deployment errors in the console.
The report bellow is sent to the helpdesk team leader every Monday ,he then splits it into support cases for the team to take care of.

The picture is the actual list that will be sent out tomorrow. It used to be loooong ,but after we started doing this it has shrunk down to 8 machines.

In our experience 1603 errors needs to be fixed manually, but a lot of the other error codes sort them self out. That's why I only list 1603 errors. 

1603 Errors report.
The color squares are indicators for when the machine last was online. (start working on the green ones ,the red ones are of the network.)

Here is another example of a fix list that i send to the helpdesk. All computers that have a Windows Update that's in an error state.
 Lot if these reports are ugly ,I haven't bothered making them look good.

Windows Update Errors for a Collection. 

The reports i send to the helpdesk and other IT people tend to be error focused. This is stuff you need to fix!

I also have i few reports that i sent to managers, but they tend to emphasis on the good parts:-)
Like this one for application deployment success rate.

Application Deployment Successrate (Also does Software Update and Task sequences)

Collection Summary Report ,3 in one Report! (Windows Update ,Endpoint Protection ,OS and Computer Model)

Finally got around to tweaking and translating my "Collection Summary" Report.

I created this report to replace several other reports that i force feed our site admins on a weekly basis:-)
The report is designed to be an e-mail report, sent out once a week that gives them a quick overview of whats going on at their site(collection).
This report have 3 sections ,Computer Details(OS,Model and Inactive Clients), Endpoint Protection and Windows Update.

I decided to focus on these 3 tings in the first version of this report. There are probably hundred of other things i could have put in the report that would have been useful ,but can't have everything in one report.

Collection Summary Report

Download

Note! The Collection Parameter dropdown list only shows Endpoint Enabled Collections. This is because Endpoint summary data is only collected for Endpoint Collections.
(Right Click the Collection --> Properties -->Alerts Tab --View this Collection in the Endpoint Protection Dashboard)

The report is divided in 3 sections ,Computers ,Endpoint Protection and Windows Update.

Computers:

The OS and Model chart should be pretty self explanatory ,but on the boxes on the right is meant to give some information on how many of the clients are inactive.

Endpoint Protection

The two graphs show Antivirus pattern age and Engine version. 

The list in the middle give a quick count of malware detected in the collection the last 14 days.

The boxes on the right shows summarized endpoint data ,how many endpoint clients in collection ,how many inactive clients and how many clients that are "at risk".

You can click the plus sign in the At Risk box to show a list of clients and whats wrong with them.

Windows Update

Windows Update shows you Compliant vs Non-Compliant pie chart and a Last scan graph. while the boxes on the right shows deployment and scan errors.

The Deployment and Scan Error Boxes can be clicked to bring up a details list ,giving some info on the problem. 

There's a tool-tip on the error codes that tries to resolve the error code.

The query for the Compliant vs Non-Compliant might not be what you expect it to be ,it's tuned to show what the site admins should worry about.

If you look at the where statement bellow I'll explain the reasoning behind this query.

• We only look at the status "need update" ,if a client haven't evaluated an update yet and don't know if it needs it or not(unknown state) we just assume it'll be OK.
• We only look at updates with severity of "low" or higher ,unclassified updates don't count:-)
• We only look at deployed updates. If I haven't deployed the update they don't need to worry about it.
• We don't care about expired and superseded updates!
• We don't count update newer that 14 days ,giving the us a "graceperiod" where the clients can install the update before they're considered non-compliant. 

WHERE a.status = 2 AND b.severity >= 2

AND b.daterevised < dateadd(day,-14,getdate()) 

AND b.isDeployed = 1 AND b.isExpired = 0 AND b.isSuperseded = 0

This can  be tweaked to match your SLA.
If you don't have an SLA just ask yourself this ,how long after you've deployed an update before you expect that all clients have it installed. if the answer is 30 days ,set the grace-period to 30 days:-)

The Grace-period and minimum Severity is easily changed in the report. The if you need to change the other things you have to edit the query. 

If you don't like the Grace-period concept,just set it to zero:-)

Windows 10 - Editions and Versions Report (in lack of a better name)

Hi

May 02: Quick Update ,seems to be a small "bug" with how Windows 10 1703 (Creators Update) registers in Configmgr DB. For some reason the "branch" field is a empty string, not 0 as expected (or at least NULL instead of empty string).
have created a new version of the report (Download it here) as a work around for this issue.

Created this report a few months ago, but didn't release it because I  wasn't sure my reverse engineering was correct until Windows 1607 was released.
But now Windows 10 1607 is released and everything seems to line up.

This report needs Configmgr 1602 to run (maybe it'll work on 1507 ,but haven't tested it)

The report is a details report for Windows 10 ,It can shed some light on Editions,Versions ,update rings and "state". As with most of my reports you can run it against a specific collection to limit the result and contrary to the console, it gives you a detailed list of witch computer is running what.

Download Report

 & '.\Upload Reports v3.ps1' -webServiceUrl http://yourreportserver.fqdn

(Read more about the script and additional parameters here)

Added some parameters to the report to enable you to show ex. only computers on "Current Branch" or only computers that have reached "End of life".

If you found it useful ,feel free to spread the word:-)

Let me know what you think in the comments below or on Twitter. 

Distribution Point Dashboard Reports

 Latest version is 0.5
(to update just rerun the script with the "-overwrite $true" parameter)

June 27 - have made some small adjustments to the reports.

• The color rule for the package error was wrong ,it's now fixed.
• Added Package type to the details report. 

June 21 - Configmgr have skewed the dates by one day ,have corrected the dates in the query.


Had some trouble with content distribution to my distribution points last week. The patch Tuesday update package had failed to distribute to two of my DP's causing some clients to fail on installing patches.
The problem solved itself after some time,but it gave me an idea of a Distribution Point Dashboard, similar to my OSD Dashboard. The though is to have a single pane of glass to quickly give the information i need about my distribution points.

This is the result. as you can see I'm of the monochrome colour scheme for now, It's summer ,time for some more color!

Distribution Point Dashboard & Distribution Point Group Dashboard

Distribution Point Details

Download Distribution Point Reports.zip

In the zip file you'll find a script to upload the reports and three reports in a sub folder. If you run the script with only the webservice parameter i'll upload the reports to ../Larsen Reports/Distribution Point/ folder and change the data source to your Configmgr Server

 & '.\Upload Reports v3.ps1' -webServiceUrl http://yourreportserver.fqdn

(Read more about the script and additional parameters here)

About the reports

DP and DP Group Report are almost the same ,the only difference is the controls. If you only have a few distribution points or you don't use distribution point groups ,go ahead an use this one.
If you have many distribution points the DP Group Report is a better choice, it allows you to first select a distribution point group and then select DP's from that group.

The Reports have a "Visual Limit" of about 10 DP's. The report will still work, but the graph might not be very readable with stats for 200 DP's on it:-)
The main part of the report is usage reporting for the DP's, but bellow that there a summary where i tried to show some useful info about the distribution points, like package status ,installed features and disk usage.

DP Details is as the name suggest for displaying details for a single distribution point. Displays a lot of the same information as the two others ,but it has details for all the packages on that DP. The Packages are sorted so that any problems are at the top.
It's linked up as a sub report for the two others ,but it can be run stand alone also.

Summary

Have had this report for two days and already it's paid of

• Found out that a few of our DP's where running low on disk space  
• One of our very remote Distribution Points had 6 packages that where "failed" and one forever "in-progress"
• Our Endpoint Protection signature package was well overdue for a cleanup ,it contained 1732 files and was 2GB in size!!

If this report helped you spot and solve some problems/errors ,post bellow.

As always ,post questions and comments bellow and if you like this report feel free to spread the word on Twitter ,Facebook and so on:-)

Powershell Script - New Version of my SSRS Bulk Upload

This Powershell script takes all report files(.rdl) in a folder ,creates the folder structure on the server ,uploads the reports and changes the data sources.

If you liked the first version of this script ,you'll love the new version.
The first version was written to solve a problem and nothing more, but turned out that it was something the  SSRS community was waiting for as it is one of the most read posts on my blog.

The new version has been completely rewritten to make it more robust and in the process i've added a feature or two.

• It now uses the Reporting Service 2010 Web Service
• It's now structurally a better script. Easier to understand and work with.
• Supports Folder structure
• Can easily be modified to upload all kinds of elements like images,pdf+

The script is designed for Configmgr reports ,but the only part that's specific to Configmgr is the part that searches for the data source. To work with other data sources you just need to modify the search criteria or just "hard code" in the name of the shared data source you want to use.

How It works

The new script has default values on some of the parameters to make it easier to use.
only required parameter is the URL(-webserviceUrl) to the reporting server.

& '.\Upload Reports v3.ps1' -webServiceUrl http://yourSSRSserver

Default it will look in the script folder for reports ,upload them to a folder called "Larsen Reports" and wont overwrite if report already exist ,but all this can be changed by setting additional parameters.

  & '.\Upload Reports v3.ps1' -webServiceUrl http://yourSSRSserver -SourceDirectory d\Reports  -ReportFolder Demo -overwrite $true

In this example i just threw a few reports in a folder/sub folder structure. Some reports on the root and some in the sub directories.

I run the script assigning the -reportFolder paramenter to Demo.

As you can see in the screenshot under it first creates the folder structure, then it searches for *.rdl files and upload them to the correct folder/ sub folder and changes the data source.

Everything should be ready to use!

Looking at the Report server, it has created the folders Demo and the 3 sub folders. The reports are uploaded in the right place.

..and the data source has been changed to the correct one.

Download: Upload Reports v3.zip

<#
.SYNOPSIS
    Bulk import SSRS Reports from a folder and update the reports data source.
.DESCRIPTION
    This script takes all the RDL files in a folder and imports them into a SSRS and updates the reports data source to the Configmgr shared data source.
.NOTES
    File Name  : Upload Reports.ps1
    Version    : 3.00 ,13.jun-2016
    Author     : Thomas Larsen - thomas.larsen@tromsfylke.no
    Requires   : Version 3
.LINK
    Blog
      http://larsenconfigmgr.blogspot.com

.PARAMETER webServiceUrl
Base URL to the report server ,usually the Configmgr Site server

.PARAMETER reportFolder
 Report Server folder where the reports are imported to, the script creates the folder if it's not there.
 
.PARAMETER SourceDirectory
The local folder where the .rdl files are located.

.PARAMETER Overwrite
Set to True if Script should overwrite Reports it they already exist.

.Example

& '.\Upload Reports.ps1' -webServiceUrl "http://Reportserver.domain.local"
Takes the report files from current directory(And Subdirectories) and uploads them the to a folder on SSRS called Larsen Reports(Default)

.Example

& '.\Upload Reports.ps1' -webServiceUrl "http://Reportserver.domain.local" -reportFolder "Larsen Reports -SourceDirectory "c:\ReportsToUpload" -overwrite $True
All Parameters ,select Sourcefolder Destination Folder ,Overwrite if exist


#>

Param(
   [string]$webServiceUrl,
      
   [string]$reportFolder = "Larsen Reports",

   [string]$SourceDirectory = $PSScriptRoot,

   [bool]$overwrite = $false
   )

Write-Host "Thomas Larsen - December 2015 - http://larsenconfigmgr.blogspot.com" -ForegroundColor Cyan
Write-Host "This Script is provided AS-IS, no warrenty is provided" 
Write-host ""

#Connect to SSRS
Write-Host "Reportserver: $webServiceUrl" -ForegroundColor DarkMagenta
Write-Host "Estabishing Proxy connection, connecting to : $webServiceUrl/ReportServer/ReportService2010.asmx?WSDL"
Write-Host ""

$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl'/ReportServer/ReportService2010.asmx?WSDL' -UseDefaultCredential

#######
#Get Configmgr shared datasource
$Items = $ssrsProxy.listchildren("/", $true) | where {$_.TypeName -eq "Datasource" }
foreach ($item in $items)
    {
    #Check to see if Datasource name patches Guid Pattern
    if ($item.name -match '{([a-zA-Z0-9]{8})-([a-zA-Z0-9]{4})-([a-zA-Z0-9]{4})-([a-zA-Z0-9]{4})-([a-zA-Z0-9]{12})}' -and $item.path -like '/ConfigMgr*')
        {
        Write-Host "Datasource:" $item.Name -ForegroundColor Magenta  
        Write-host "Type:" $item.TypeName 
        Write-Host "Path:" $item.Path

        #Save parameters for later use.
        $DatasourceName = $item.Name
        $DatasourcePath = $item.Path
        }
    }

function SSRSFolder ([string]$reportFolder,[string]$reportPath)
{
##########################################    
#Create Folder     
        try
        {
            $ssrsProxy.CreateFolder($reportFolder, $reportPath, $null) | out-null
            if($reportPath -eq '/')
            {
            Write-Host "Folder `"$reportpath$reportFolder`" Created"
            }
            else
            {
            Write-Host "Folder `"$reportpath/$reportFolder`" Created"
            }
        }
        catch [System.Web.Services.Protocols.SoapException]
        {
            if ($_.Exception.Detail.InnerText -match "rsItemAlreadyExists400")
            {
                Write-Host "Folder: $reportFolder already exists."
            }
            else
            {
                $msg = "Error creating folder: $reportFolder. Msg: '{0}'" -f $_.Exception.Detail.InnerText
                Write-Error $msg
            }
        }
}

Function SSRSItem ([string]$ItemType,$item,[string]$folder)
 {
 Write-host ""
 #ReportName
 if ($ItemType -ne "Resource")
 {
 $ItemName = [System.IO.Path]::GetFileNameWithoutExtension($item);
 }
 else
 {
 $ItemName = $item.Name
 }
 write-host $ItemName -ForegroundColor Green 

 #Upload File
     try
    {
        #Get Report content in bytes
        Write-Host "Getting file content of : $item"
        $byteArray = Get-Content $item.FullName -encoding byte
        $msg = "Size: {0} KB" -f "{0:N0}" -f ($byteArray.Length/1KB) 
        Write-Host $msg 
        
        Write-Host "Uploading to: $folder"
 
        #Sets property for images(only png)
        $type = $ssrsProxy.GetType().Namespace
        $datatype = ($type + '.Property')              
        $property =New-Object ($datatype);
        if ($ItemType -eq "Resource" -and $item -like "*.png")
        {
        $property.Name = "MimeType"
        $property.Value = “image/png”
        }
        else
        {
        $property = $null
        }

        #Call Proxy to upload report  
        $warnings =@();    
        $ssrsProxy.CreateCatalogItem($ItemType,$itemName,$folder,$overwrite,$byteArray,$property,[ref]$warnings) | out-null
        if($warnings.Length -le 1) { Write-Host "Upload Success." -ForegroundColor Green }
        else 
        {        
            foreach ($message in $warnings)
                {
                if ($message.Code -ne "rsDataSourceReferenceNotPublished")
                    {
                    write-host "$($message.Severity) $($message.Code) $($message.Message)" -ForegroundColor Yellow
                    }
                }
        }
    }
    catch [System.IO.IOException]
    {
        $msg = "Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message
        Write-Error msg
    }
    catch [System.Web.Services.Protocols.SoapException]
 {
    $caught = $false
            if ($_.Exception.Detail.InnerText -match 'rsItemAlreadyExists400')
            {
                $caught = $true
                Write-Host "Report: $itemName already exists." -ForegroundColor Red
            }
            if ($_.Exception.Detail.InnerText -match 'CoretechSSRS')
            {
                $caught = $true
                Write-Host "Cant't find Reporting Extention File." -ForegroundColor Red
            }
            elseif ($caught -eq $false)
            {
                $msg = "Error uploading report: $reportName. Msg: '{0}'" -f $_.Exception.Detail.InnerText
                Write-Error $msg
            }
  
 }
}

Function SSRSDatasource ([string]$ReportPath,$DataSourceName,$DataSourcePath)
{
$report = $ssrsProxy.GetItemDataSources($ReportPath)
ForEach ($Source in $report)
    {
    $proxyNamespace = $Source.GetType().Namespace
        $constDatasource = New-Object ("$proxyNamespace.DataSource")
        $constDatasource.Name = $DataSourceName
        $constDatasource.Item = New-Object ("$proxyNamespace.DataSourceReference")
        $constDatasource.Item.Reference = $DataSourcePath

   $Source.item = $constDatasource.Item
    $ssrsProxy.SetItemDataSources($ReportPath, $Source)
    Write-Host "Changing datasource `"$($Source.Name)`" to $($Source.Item.Reference)"
    }
}

##Create Folder Structure
#Create Base Folder

Write-Host "Creating Folders Structure:" -ForegroundColor DarkMagenta
SSRSFolder $reportFolder "/"

ForEach($Folder in Get-ChildItem $SourceDirectory -Directory)
    {
    #Add each folder in the sourcefolder to the reporting service 
    SSRSFolder $Folder.Name /$reportFolder
    }

     
#Upload Reports in root folder
ForEach ($rdlfile in Get-ChildItem $SourceDirectory -Filter *.rdl )
{
SSRSItem Report $rdlfile /$reportFolder

#Change Report Datasource
$ReportPath = "/" + $reportFolder + "/" +  $rdlfile.BaseName 
SSRSDatasource $ReportPath $DataSourceName $DataSourcePath
}

##For Each folder
 
ForEach($Folder in Get-ChildItem $SourceDirectory -Directory)
    {
    #Add each folder in the sourcefolder to the reporting service 
    #SSRSFolder $Folder.Name /$Basefolder 
    
    #Add reports in the folder
    ForEach ($rdlfile in Get-ChildItem $Folder -Filter *.rdl )
        {
        SSRSItem Report $rdlfile /$reportFolder/$($folder.Name) 

        #Change Report Datasource
        $ReportPath = "/" + $reportFolder + "/" + $folder.Name + "/" + $rdlfile.BaseName 
        SSRSDatasource $ReportPath $DataSourceName $DataSourcePath
        }
    }

SSRS Report Speed Problem. How to increase the speed of your SSRS Reports.

Backstory

I tried to show my colleague one of my new reports, but when he tried to open the report it wouldn't load. He could enter the parameters just fine, but the report itself just hung at the "Loading" screen.
Tried the same on my computer and the same thing happened. When i ran the report on the server itself either from the browser or from Report Builder everything worked fine.
My first thought was that this was a speed issue so i started investigating. The problem in this case turned out to be a IE11 problem (setting the report site to comparability mode fixed that), but while I was investigating the issue I found something else that was very interesting.

The Problem

The issue is something called SQL Parameter sniffing and you can read more about it here, Essentially the problem is with the way SQL tries create a execution plan based on the size of the result, but when the query uses parameters the result may vary in size and the execution plan may be far from optimal.
There are several ways to get around this, the easiest way seems to be to add "optimize for unknown" to the query but that requires SQL 2008 R2 or above and may cause a problem for some. But i found a simple workaround while browsing stack exchange that seems to work just fine.

The Solution

The trick is to double up the parameters. Instead of using the @SSRS parameters directly you map the SSRS parameters to SQL Parameters and use the SQL parameters in the query.
I couldn't find a way to do this with multi-value parameters, but it looks like you only need to do this to some of the parameters to bypass the problem.

In the SQL snippit under i take the two SSRS parameters (@Timeperiod and @DefaultTimezone) and create two SQL parameters for them (@TimeperiodVar and @DefaultTimezoneVar) and tie them together. Then I replace the ssrs parameters with the new SQL parameters everywhere they're used in the query. 

DECLARE @TimeperiodVar DATETIME DECLARE @DefaultTimezoneVar INT
--set @AdvertisementIDVar = @AdvertisementID  SET @TimeperiodVar = @Timeperiod SET @DefaultTimezoneVar = @DefaultTimezone; 

The Result

Have a look at the Execution speed on the screenshot under(The Total time is in seconds and the rest is in milliseconds.).

The "OSD Dashboard" is the standard one and "OSD Dashboard Demo" has the new parameter system:

As you can see, I managed to cut the execution time for this report a lot!!
I managed to cut the SQL query time from between 22 seconds down to 0-5 seconds(yesterday it was 47+- seconds for the first one).
SSRS does some caching and so the results may vary, but around one second is what it takes when i run the query in SQL Management Studio.

So this little issue with IE11 Compability mode lead me down a path that enabled me to increase execution speed roughly 27x!

Have tested a few others reports and it seems very promising , I will defently use this on all my reports from now on.

OSD Details ,Part 2 of my OSD Dashboard Report

As promised, here is part 2 for my OS Deployment Dashboard.

It's designed to be a drill trough report from my OSD Dashboard,but you can also run it stand alone.
The query has been  completely rewritten from my last report so it should be a lot more reliable than the last version.

Download Reports.zip

To upload the report ,download the zipfile and extract to a folder. you can upload the rdl files the old fashion way or you can test drive the new version on my upload script.

To run the script you only need to specify the url to the reporting server(normally the site server)

The script will create a new folder called "Larsen Reports"(can be set with parameter), upload the reports and change the datasource to your configmgr server. 

& '.\Upload Reports v3.ps1' -webServiceUrl http://your-report-server 

Staring with the controls ,the "Deployments" field lists all task sequences and deployments in a readable format. Next the "Computer" will filter to only show computers that have run that deployment.

Lastly for the "Start Date" and "End Date" you just need to make sure the computers OS Deployment are between these dates. (These two parameters are mostly used by the Dashboard to select the correct deployment when there are several attempts for a single machine.) 

The top of the report shows some basic info about the machine, name ,DN and hardware model if they are available.

The part your most likely interested in is the step by step status for the OS Deployment.

It'll show you a nicely formatted (if i may say so myself:-) ) list  with the details for each step. in the task sequence and how long time each step took.

The list is quite minimalist ,but a lot of info is available as tooltips. Execution status on the Group And Action Name and Exit Code and Action Output on the Step number.

If a group or action is skipped the text is over lined to illustrate that. If an action has an exit code other than 0 (Success) the line is colored red so any errors should be easy to spot.

If you have any trouble displaying the graphics in the report ,try restarting the SSRS Service.

As always, hope you like the report. Post comments and questions below.

If you think others might find this usefull ,feel free to share a link on twitter ,facebook and forums. (this blog is tiny so not many people have seen this yet)