SSRS Search Filter

Hi

This is just a quick post to show how you can create a search filter to limit a long list ,in this case software info collected by Configmgr.

Combine this with multi valued parameters and you have a good method to show all software versions, even if the vendor don't keep the publisher name consistant. 

In my case, if i didn't have the search filter the Publisher list would be 3338 entries long ,not very user friendly...

So what we do is to add a search filter that we use in the query that populates the Publisher Parameter. We take the input from the searchfilter and use it in the query for the next Publisher list with a LIKE statement (We use a replace statement to translate the wildcard to "%" )

SELECT DISTINCT publisher0 AS Publisher
FROM   v_add_remove_programs
WHERE  publisher0 LIKE Replace(@SearchFilter, '*', '%') 

With the use of multi valued parameters we can select the Publishers we want. Just remember with multi valued parameters you have to use the IN operator.

So to get the the values for the application list parameter we do the following.

SELECT DISTINCT displayname0 AS Displayname
FROM   v_add_remove_programs
WHERE  publisher0 IN ( @Publisher ) 

So now we can just create the main query with two IN Operators. Where Publisher is in the publisher list and Displayname in the Sofware List.

SELECT name0,
       B.*
FROM   v_r_system A
       JOIN v_add_remove_programs B
         ON A.resourceid = B.resourceid
WHERE  B.publisher0 IN ( @Publisher )
       AND B.displayname0 IN ( @Software ) 

Last ,build your report.
Here I've just created a simple tablix with result just to show you that it works:-)

How-to add a collection selection to almost any report.

This seems to be a common question so i desired to make a quick blog post a about it.
There are three parts to this ,first the part that you add to your query:

....AND ResourceID in (select ResourceID from dbo.v_FullCollectionMembership Where CollectionID = @CollectionID)

Notice: If your query have a where clause ,then you just tack it on with the AND operator. If your query does not have a where clause you just replace the AND with a WHERE and paste it in your query.

Next you need a query to get a list of collections ,this is needed to create a dropdown list with the collection names. Without this you just get a textbox where you need to type in the collection ID ,not very user friendly.

Create a new dataset called "Collections" using the following query:

SELECT     Name, CollectionID
FROM         v_Collection where CollectionType = '2'
Order by Name asc

TIP: Prefix the collections that you want to use in your reports with something ,then you can filter the collection list by adding a "WHERE Name like '%Something%' " to the query above.

Last you need a Parameter called "CollectionID" that gets it's available values from the query you just created.

Now you have the a working CollectionParameter in your report with a nice dropdown list.

If you're new to report building ,here is an example:

Lets say you have a simple query in your report to show all "Dell" computers in your environment.

Select A.Name0,A.User_Name0,B.Manufacturer0,B.Model0
from v_R_System A
join v_GS_COMPUTER_SYSTEM B on A.ResourceID = B.ResourceID
Where B.Manufacturer0 Like '%DELL%' 

To limit this query to only return computers from a specific collections you simply add the query snippet at the top of this post to your existing query.

Select A.Name0,A.User_Name0,B.Manufacturer0,B.Model0
from v_R_System A
join v_GS_COMPUTER_SYSTEM B on A.ResourceID = B.ResourceID
Where B.Manufacturer0 Like '%DELL%' AND ResourceID in (select ResourceID from dbo.v_FullCollectionMembership Where CollectionID = @CollectionID)

Add the parameter(CollectionID) and it's dataset and your done!

Hope this helps.

Followup post to The SSRS Bulk script

Just a quick followup post to the Configmgr Bulk upload report script i posted a few days ago.
Got a question for a generic script to Bulk upload reports to SQL Reporting Services. With small modifications my script can be used.
I've just commented out the "change datasource" parts which is the only part thats specific for Configmgr ,the rest should work for any SSRS installation.


Download it here:ReportUploader.ps1

Powershell Script - Bulk import SSRS Reports and update datasources

June 15: New version of this script available ,get it here:
new-version-of-my-ssrs-bulk-upload

For my first post of 2015 i give you a script that i wrote.
What this script does is it takes all .rdl files in a folder and imports them to your report server and changes the reports datasource to your Configmgr shared datasource.
This is useful if you need to import a lot of reports or if you're not that confident with SSRS.

The plan is to rework some of my old reports ,create some new ones and create a report pack that people easily can plug in to their Configmgr environment.
The report pack version one is going to contain some reports for Client Details  ,OS Deployment ,Application Deployement and Software Update so stay tuned!




To give you a preview to test with I've bundled it with my two of my Client Reports. The Make & Model Report posted here earlier and a Computer Reboot Report that I've revamped for the occasion.

This is a test ,so i would appreciate if you post bellow if you get it to work(or not).  

Download the Zip file and run the Script with the following parameters:
Download: Upload Reports.ps1

The Script takes 3 parameters:

• webserviceURL: The base URL of your report server. In many cases the site server.(Note! no /report at the end)
• Sourcefolder: Probably self explanatory ,but anyway... folder where your .rdl files are located
• Reportfolder:  The folder you want to create in SSRS and/or upload to

& '.\Upload Reports.ps1' -webServiceUrl "http://reportserver" -reportFolder "Larsen Reporting" -SourceDirectory "d:\Demo"

This should give you a folder in SSRS called "Larsen Reporting" with two reports in it. The reports should in theory be ready to run since the script automatically updates the datasource. 

Download: Upload Reports.ps1

<#
.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    : 1.03 ,05.jan-2015
    Author     : Thomas Larsen - thomas.larsen@tromsfylke.no
    Requires   : Version 3
.LINK
    Blog
      http://larsenconfigmgr.blogspot.com

.PARAMETER webServiceUrl
Base URL to the report server ,usualy 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.

.Example
& '.\Upload Reports.ps1' -webServiceUrl "http://Reportserver.domain.local" -reportFolder "Larsen Reports -SourceDirectory "c:\ReportsToUpload"

#>
 

Param(
  [Parameter(Mandatory=$True)]
   [string]$webServiceUrl,
 
   [Parameter(Mandatory=$True)]
   [string]$reportFolder,

   [Parameter(Mandatory=$True)]
   [string]$SourceDirectory
   )

#$webServiceUrl = "http://reportserver.domain.local"
#$reportFolder = "Larsen Reports"
#$SourceDirectory = "C:\ReportstoUpload"

$ErrorActionPreference = "Continue"
Write-Host "Thomas Larsen - Januar 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 Magenta
Write-Host "Creating Proxy, connecting to : $webServiceUrl/ReportServer/ReportService2005.asmx?WSDL"
Write-Host ""
$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl'/ReportServer/ReportService2005.asmx?WSDL' -UseDefaultCredential
$reportPath = "/"


#######
#Get Configmgr shared datasource
$Items = $ssrsProxy.listchildren("/", $true) | where {$_.Type -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.Type 
Write-Host "Path:" $item.Path

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


##########################################    
#Create Folder
        Write-host ""
        try
        {
            $ssrsProxy.CreateFolder($reportFolder, $reportPath, $null)
            Write-Host "Created new folder: $reportFolder"
        }
        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
            }
        }


#############################
#For each RDL file in Folder

foreach($rdlfile in Get-ChildItem $SourceDirectory -Filter *.rdl)
{
Write-host ""


#ReportName
 $reportName = [System.IO.Path]::GetFileNameWithoutExtension($rdlFile);
 write-host $reportName -ForegroundColor Green 
 #Upload File
     try
    {
        #Get Report content in bytes
        Write-Host "Getting file content of : $rdlFile"
        $byteArray = gc $rdlFile.FullName -encoding byte
        $msg = "Total length: {0}" -f $byteArray.Length
        Write-Host $msg
 
        $reportFolder = $reportPath + $reportFolder
        Write-Host "Uploading to: $reportFolder"
 
        #Call Proxy to upload report
        $warnings = $ssrsProxy.CreateReport($reportName,$reportFolder,$force,$byteArray,$null) 
        if($warnings.Length -le 1) { Write-Host "Upload Success." -ForegroundColor Green }
        else { write-host $warnings | % { Write-Warning "Warning: $_" }}
  
    }
    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]
 {
            if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
            {
                Write-Error "Report: $reportName already exists." 
            }
            else
            {
                $msg = "Error uploading report: $reportName. Msg: '{0}'" -f $_.Exception.Detail.InnerText
                Write-Error $msg
            }
  
 }



##Change Datasource
$rep = $ssrsProxy.GetItemDataSources($reportFolder+"/"+$reportName)
$rep | ForEach-Object {
$proxyNamespace = $_.GetType().Namespace
    $constDatasource = New-Object ("$proxyNamespace.DataSource")
    $constDatasource.Name = $DataSourceName
    $constDatasource.Item = New-Object ("$proxyNamespace.DataSourceReference")
    $constDatasource.Item.Reference = $DataSourcePath

$_.item = $constDatasource.Item
$ssrsProxy.SetItemDataSources($reportFolder+"/"+$reportName, $_)
Write-Host "Changing datasource `"$($_.Name)`" to $($_.Item.Reference)"
}

#Something in the foreach loop keeps adding forward slashes to the parameter evertime it runs. 
#Strips slashes from the parameter.  
$reportFolder = $reportFolder -replace "/",""
}

Write-host ""
Write-host "Finished" -ForegroundColor Magenta
Write-host ""

Silverlight missing from WSUS

As you might have noticed there's currently no update for Silverlight available for WSUS or Configmgr. Took me a while to figure it out ,so i thought I'd write a short post about it to save others some time.

You might have noticed that your clients is prompting you to update silverlight ,but when you check WSUS or SUP you can't find any silverlight updates.
The reason for this is that in the Desember 2014 updates(KB3011970) there was update containing Silverlight (v5.1.31211.0) ,but this patch got revoked pretty quick because of some Active X issues.
When they released this they expired KB2977218 from July containing Silverlight v5.1.30514 so now there is currently no update for Silverlight available WSUS / Configmg.

So to summerize:
KB2977218  released in July 2014 containing Silverlight 5.1.30514 is now expired.
KB3011970  released in December 2014 containing Silverlight 5.1.31211 was revoked

So if your clients is running an older version of silverlight your users will be prompted to upgrade silverlight when they visit a silverlight page.
5.1.30514 is listed as the latest release on Microsoft ,but the update containing this version is expired so you can't deploy as you normally would ,trough WSUS.

For now you just have to live with the "Silverlight is requesting an Update" messages or  to deploy it manually.

MS Bulletin Quick Compliance Check

Ok ,it's been a while since my last post so i thought I'd share something useful and relevant.

Microsoft released a couple of out-of-band patches a few days ago that you should deploy asap.

To keep track of the deployment of patches belonging to a specific bulletin ID i created this quick and dirty report. Still some work left on this report ,but might still be useful 

The report gives a quick overview on how compliment your environment is to a bulletin-ID.

Update: Have made some changes to the report ,Updated the colors(last one looked like a traffic light) and added "Last Status Message" to give it some more details.
Grab the new version  ,link below the picture.

MS Bulletin Compliance.RDL

The reports defaults to MS14-068 and clients that have been active in the last 30 days ,both values can be changed.

As always you have to change the datasource to suit your environment ,check my earlier post on details on how to do this

This is just a quick draft ,going to add a few fetures to it when i have time

•  the Report don't consider clients with status "Not Applicable".
  For now it's just required vs installed

Hope you find it useful.

Here's the Query ,note it's a simple query ,but with quite a few joins :

SELECT B.name0, 
       E.caption0, 
       CASE 
         WHEN E.producttype0 = 2 THEN 'Server' 
         WHEN E.producttype0 = 3 THEN 'Server' 
         WHEN E.producttype0 = 1 THEN 'Client' 
       END AS ClientType, 
       C.bulletinid, 
       C.articleid, 
       C.title, 
       D.statename, 
       H.statename, 
       F.lastactivetime 
FROM   v_updatecompliancestatus A 
       INNER JOIN v_r_system B 
               ON A.resourceid = B.resourceid --To get Computername 
       INNER JOIN v_updateinfo C 
               ON A.ci_id = C.ci_id --To get Details about the Update 
       INNER JOIN v_statenames D 
               ON A.status = D.stateid 
                  AND D.topictype = 500 --To get the installstatus 
       INNER JOIN v_gs_operating_system E 
               ON A.resourceid = E.resourceid --To get OS info 
       INNER JOIN v_ch_clientsummary F 
               ON A.resourceid = F.resourceid --To get Client last active time 
       INNER JOIN v_statenames H 
               ON A.lastenforcementmessageid = H.stateid 
                  AND H.topictype = 402 --To get State Messages 
WHERE  C.bulletinid = @BulletinID 

Keep Track of your OS Deployments Part 2 (Drill-down Report)

 As promised ,here's the drill-down report for the OSD - Overview Report i posted last week. This OSD Details Report shows the run time and status of each step in a task sequence.

This report can either be run standalone or as a drilldown report for my OSD - Overview report that i posted last week.

This report is brand new so there might still be some kinks that needs to be sorted out.

OSD-Details.rdl

Features

If you run it standalone you get two dropdown list ,first you select the Task Sequence and Advertised collection ,then you get a list of only the computers that have ran that deployment(not every computer in your site as in the default reports)

The top part of the report tries to resolve some basic information about the computer to help troubleshoot. The information available will vary ,an unknown client won't have any information in the database an will be blank.

It will try to get Make and Model ,Active Directory DN and Chassis Type.(The picture will change based on chassis type)

Stuff you might want to change

This report might need some work to display correctly ,therefore I've included an unfiltered view on page two of the report that you can use to fine tune your report.
The problem is that some steps have a StepID 0 or some other problem that's going to mess with the run time calculation.
For instance "Install Collection Software" will show a total run time for the action ,but each software will have it's own step with StepID 0 with it's own run time ,so if you include both it will look like the step took twice as long to complete.
Another Problem is that some steps like "Restart Computer" and "Setup Windows and Configuration Manger" show up twice ,it seems like the first line shows run time without the reboot and the second line shows time with the reboot. I haven't figured out how to fix this yet so for now you have to live with for now.(let me know if you figure out how to fix this....)

To get the report to work import it into your enviroment and change "datasource1"

To finetune the report you can edit the last line in Dataset1.

the "Not IN" clause is essentially a filter ,the 11xxx numbers are the LastMessageIDs you don't want to include.

AND tse.laststatusmessageid NOT IN 

( 11138, 11139, 11140, 11141,11142,11143,11902,11904,11905 )

you might want to remove the 11902, 11904 and 11905 ,they are for the Install based on task sequence variable step.

Link the Reports

To link this report to the OSD -Overview report open the OSD-Overview in Report Builder and right click [Computername] in the Tablix and select properties.

Under Actions ,change the settings to point to this report.

Do the same for the databar in the tablix...

Hope these reports can help you shed some light on your OS Deployments ,let me know how it works out:-)

Here is the complete Query:

SELECT tse.step                                                           AS 
       TSStep, 
       tse.laststatusmessageid, 
       CASE 
         WHEN ( tse.laststatusmessageid >= 11120 
                AND tse.laststatusmessageid <= 11127 ) THEN tse.groupname 
         ELSE tse.actionname 
       END                                                                AS 
       NAME, 
       CASE 
         WHEN ws.timezoneoffset IS NULL THEN 
         Dateadd(minute, 120, tseprev.executiontime) 
         ELSE Dateadd(minute, ws.timezoneoffset, tseprev.executiontime) 
       END                                                                AS 
       StartTime, 
       CASE 
         WHEN ws.timezoneoffset IS NULL THEN 
         Dateadd(minute, 120, tse.executiontime) 
         ELSE Dateadd(minute, ws.timezoneoffset, tse.executiontime) 
       END                                                                AS 
       EndTime, 
       --dateadd(minute,ws.TimezoneOffset,tseprev.ExecutionTime) as StartTime,  
       --dateadd(minute,ws.TimezoneOffset,tse.ExecutionTime) as EndTime,  
       CASE WHEN Datediff(dd, tseprev.executiontime, tse.executiontime) > 0 THEN 
       Cast( 
       Datediff(dd, tseprev.executiontime, tse.executiontime) AS VARCHAR(2)) + 
       'd ' 
       ELSE '' END 
       + CONVERT(CHAR(8), tse.executiontime - tseprev.executiontime, 108) AS 
       TotalTime, 
       tse.exitcode, 
       tse.actionoutput 
FROM   v_taskexecutionstatus tse 
       INNER JOIN (SELECT Max(executiontime) AS Time, 
                          advertisementid, 
                          resourceid 
                   FROM   v_taskexecutionstatus 
                   WHERE  laststatusmessageid = 11140 
                          AND step = 0 
                   GROUP  BY advertisementid, 
                             resourceid) tsstart 
               ON tse.advertisementid = tsstart.advertisementid 
                  AND tse.resourceid = tsstart.resourceid 
                  AND tse.executiontime > tsstart.time 
       LEFT OUTER JOIN v_taskexecutionstatus tseprev 
                    ON tse.advertisementid = tseprev.advertisementid 
                       AND tse.resourceid = tseprev.resourceid 
                       AND tseprev.executiontime >= tsstart.time 
                       AND tseprev.executiontime < tse.executiontime 
                       AND ( ( tse.step = 0 
                               AND tseprev.step = 0 
                               AND tseprev.laststatusmessageid = 11140 ) 
                              OR ( tse.step > 0 
                                   AND tseprev.step = tse.step - 1 
                                   AND tseprev.laststatusmessageid NOT IN ( 
                                       11138, 
                                       11139, 11140, 11141, 
                                       11142, 
                                       11143 ) ) ) 
       JOIN v_r_system sys 
         ON tse.resourceid = sys.resourceid 
       LEFT OUTER JOIN (SELECT resourceid, 
                               timezoneoffset 
                        FROM   v_gs_workstation_status) ws 
                    ON ws.resourceid = sys.resourceid 
WHERE  tse.advertisementid = @AdvertID 
       AND sys.netbios_name0 = @ComputerName 
       AND tse.laststatusmessageid NOT IN ( 11138, 11139, 11140, 11141, 
                                            11142, 11143, 11902, 11904, 11905 )