Wednesday, June 29, 2016

v_GS_OPERATING_SYSTEM not updating correctly


I'm working on my Windows 10 Report ,but i've run into a strange problem and i'm wondering if anyone else sees the same thing.
The problem seems to be that v_GS_OPERATING_SYSTEM SQl view is not updating correctly. lots of machines that have been upgraded to Windows 10 still show their old Operating system.
v_r_System seems to be correct and i have verified that the machines are running windows 10


If anyone running Configmgr 1511 or 1602 could run this query and see if they have the same problem that would be great.
Reply here or on twitter.

SELECT A.name0,
       A.user_name0,
       A.client_version0,
       D.caption0,
       A.build01,
       A.osbranch01,
       B.NAME,
       B.state,
       C.value,
       C.localeid,
       D.installdate0,
       A.operating_system_name_and0
FROM   v_r_system A
       LEFT OUTER JOIN vsms_windowsservicingstates B
                    ON B.build = A.build01
                       AND B.branch = A.osbranch01
       LEFT OUTER JOIN vsms_windowsservicinglocalizednames C
                    ON B.NAME = C.NAME
       LEFT OUTER JOIN v_gs_operating_system D
                    ON A.resourceid = D.resourceid
WHERE  A.build01 LIKE '10.%' 



Add  And D.Caption0 not like '%10%' to the end of the query to only show entries with the wrong caption

Btw! here's a sneak peak of the Windows 10 Report I'm working on




Tuesday, June 28, 2016

How we use Configmgr OSD Prestage to deploy 2500 laptops with almost no network load

This post got a bit longer than intended ,jump to the bottom to read about how we use prestage task sequence to deploy 2500 laptops in a very short time period across 16 sites from one central Distribution Point with no network load and no interaction other than powering them on.

This may not be a good solution for  everyone ,but it works very well for us.
We're happy since we can make changes to the image at any time ,we're no longer screwed if we find a flaw in the image after the vendor have applied the image. takes the pressure off.
The vendor is happy ,the prestage image only takes 3 min 20 sec to apply from USB compared to some of the other customers image that take 1 hour pr machine. No bottlenecks in their factory line
The site admins are happy since they can deploy as fast as they like without worrying about the network link

I will post more details on how to set this up soon ,or even better I'll see if i can get my colleague to do the write up since he's the brains behind this.

I work for a Troms County Council in Norway and part of my job is to supply services to our 16 upper secondary school. Troms County has since 2006 bough laptops to all students when they start on the first year. The student use the laptop for the 2-3 years of school and after they are done they get to keep the laptop.
Each year we buy 2500+- new laptops intended for the new students that start in August. As soon as school starts the pressure is on to get the laptops distributed as fast as possible so that they can start using them.

Since 2006 we have had many different ways of deploying OS and applications to the laptops. some of them worked OK and some were complete disasters (remember this was 2006 ,before MDT and Configmgr). We've always had the computers branded, imaged and unboxed by the vendor, but the machines still needed to be named ,joined to the domain ,assigned to the student in the asset system and loaded with the correct software for their class on site.

2006 - 2007

First year the solution was a dual boot XP Home - XP Pro solution ,the idea was that they used the XP Pro for school work and XP Home for personal stuff. Add in some sketchy Boot Manager software that hid the XP Home partition when booted in XP pro and vice versa.
As you might have guessed that didn't work very well. The Dual boot software often corrupted one or both of the partitions causing lots of trouble.

2007 - 2009

We dropped the dual boot stuff after one year and went with the newly released Windows Vista.
...yeah, nuff said.
The only thing worth mentioning is that we got the requirement that the image should be made available to the student on a DVD, so we actually got 2500 dual layer DVD's printed.

2009 - 2014

Jump to Windows 7 and MDT and things start to get usable. but still required a lot of work when the machines arrived on site.

We got Configmgr 2007 around 2010 ,but it didn't change much. We stilled used "wds style" images for this process. they were created in mdt, but was basically fat images that got loaded on the machines.
Remember the machines are imaged 2 months before school begins so we don't know witch student get what laptop, how many for each class and so on. So the computers are loaded with a generic image.
That being said ,this solution probably lived a few more years that it should have, since this only happens once a year it was easier and faster to do the same procedure as the year before rather that come up with something better.


 2014 - Present


This is probably what you came here to read.
Now we use the prestaged-content feature of Configmgr 2012 to image the laptops. we've used this system for the last two years and we're now preparing for the 3rd round.

The big advantages of this system is:

  • No network load (all content is pre staged on the disk)
  • The task sequence is very fast 15 minutes from start to finish
  • The deployment is zero-touch (light touch if you count the power button)
  • Applying the prestage image takes only 3minutes 20 seconds pr machine so the vendor is very happy.
  • We can still make changes to the drivers and apps after the machines are pre staged. If we decide to update java ,will download only the new java package ,the rest is loaded from disk.

The system consist of 3 task sequences:


1st Task Sequence (Create Prestage)

The first task sequence is just a stripped down copy of the task sequence you want to use where you've removed all the unnecessary stuff that this model don't need. Like drivers for other models.
This task sequence is only used for deciding what content to prestage on the computers.

2nd Task Sequence (Deploy Prestage)

This is the one that we send to the vendor. it's the one that does all the work.
This task sequence partitions the disk ,loads the prestage image and boot image on the machines.
This task sequence have a lot of steps that run different scripts and commands to hadle all the disk stuff.


3rd Task Sequence (Deploy)

The third task sequence can be your normal task sequence, this is the task sequence the computers run once they come on site ,since everything is prestage it runs very fast. (15 minutes). We use thin images so the 15 minutes include installing Configmgr Client ,Office ,Flash ,Java and a dictionary application. 


So here is how it works.
  1. We create a prestage image that we send to our vendor.
  2. The vendor unpacks all the computers, add anti theft sticker ,image them and put them in backpacks.
  3. We get a asset file that we import into Configmgr. The machines are imported into a collection that have a required OSD advertised to it.
  4. Someone at the school plugs as many machines as they can handle into the network and starts powers them on. (Since there's no network load he can start as many wants)
  5. The machines boot and immediately start the OS Deployment.
    (boot image is slightly modified so that absolutely no keypresses are necessary) 
  6. The computers run the required task sequence in 15 minutes ,once they're finished they remove themselves from the prestage collection.
  7. The Asset system reads computers from a custom table in Configmgr  ,so they just have to assign the computer to the student's username.

We use a custom version of my (old) OSD Overview report to monitor the deployment. 
Since we know it's supposed to take 15-16 minutes we have added a color rule to the databar that gradually changes from green to yellow the further away from 15 minutes it gets. Any errors are colored red so that they are easy to spot.


Next post will contain details on how to create the 3 task sequences.
If you can't wait read the guide we used here

Got any interesting imaging stories ,post them below? 

Tuesday, June 21, 2016

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


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:-)

Wednesday, June 15, 2016

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.

This powershell script takes all report files(.rdl) ,creates the folder structure on the server ,uploads the reports and changes the datasources.

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
        }
    }

Tuesday, June 7, 2016

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.








Wednesday, June 1, 2016

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.


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)