How To Setup Report Builder to Create or Edit ConfigMgr CB Reports.

There are a few tricks to getting Report Builder to be able to edit or create reports in your ConfigMgr SSRS environment. I have had to do this several times, as I often change workstations, so I thought I’d finally blog it.  I’ll cover a few things in this post to help you get started.
In this Post, I’ll cover the simple setup of SQL Report Builder, connecting it into your ConfigMgr SSRS (Written and tested with CB 1610), and testing the queries to make sure you have permissions.

  1. Download and install SQL Report Builder
    1. Download HERE
    2. To Install, just follow the wizard.
  2. Export the SQL Cert on your ConfigMgr DB server.
    image
    1. Right Click on the ConfigMgr SQL Server Identification Cert and choose Export
    2. Yes, export Private Key (Keep this in a secure location)
    3. Leave file Format Screen defaulted, and click Next
      image
    4. Recommend Using a password to protect the Certificate
      image
    5. Save the Cert somewhere secure for use later.
  3. Connecting SQL RB to your CM SSRS Database
    1. Launch SQL RB 2016, (Make sure you launch the software as a user that has RIGHTS to modify Reports, you might have to check permissions.  My account is a ConfigMgr Admin, so it does have rights, but you can grant others less rights to work on reports, using the Security roles in ConfigMgr) – Good Blog article about setting up roles HERE
    2. Now, lets open a canned report - File –> Open
    3. Point it at your SSRS URL String, you can get this by pulling up your Reports in IE, then grab the URL and modify it.  Delete everything after “Reports” and change “Reports” to “ReportServer”
      image
      image
    4. You should now see your Site Code Folder, go ahead and drill down to Hardware – General, Computer Information for a specific computer and click open
      image
    5. It should now load that report in the Builder and you can see the Data Source & DataSets
      image
    6. Go ahead and look at the DataSet0 Properties, this will be the Query used to gather the data for the report.
      image
    7. Go ahead and click Query Designer, you should be prompted for username / password, choose “Use the current Windows user”.  At this point, I get an error: Unable to connect to data source – The certificate chain was issued by an authority that is not trusted.
      image
    8. Remember that Cert we exported, this is when it comes into play. 
      1. Right Click on that Cert and Install (You can leave Report Builder open while you do this)
      2. Choose Local Machine, click Next, then insert the password you chose.
        image image
      3. Click “Place all certificates in the following store and click browse, choose “Trusted Root Certification Authorities.
        image
      4. Click Finish, and it will then give you the “Success” box.
    9. Going back to the Report Builder, click Query Builder again, and choose “Use current logged on user”, it should now work and bring you to the query designer.
      1. To test the Query, you can hit the red Exclamation mark
      2. It will now prompt for 2 pieces of information, @UserSIDs & @variable (Computer Name)
        1. @UserSIDs, this one is a bit of fun, you’ll need to get your SID for your account, then convert it to what SSRS is looking for. – PowerShell Time
          [wmi] "win32_userAccount.Domain='YourDomain',Name='YourAccount'"
          image
        2. Grab the SID from there and go back to Report Builder
          Open the properties of the “DataSetAdminID” dataset
          Go to Query Designer, click the Red exclamation Mark, when prompted, enter the SID
          image
          This will give you the UserSIDs
          image
          Save your UserSID in a safe place, as you’ll need it for working with other canned reports.
    10. Going back into the DataSet0 Query, run the query and insert info
      image
      Click OK
    11. Now you’ll see the data pulled via the Query:
      image
  4. At this point, you’re able to connect, run queries, and see how it’s working.  You can now start modifying the Queries to add additional info, remove info, modify how the report itself looks, etc.

At a later date, I’ll go over importing reports, building one from scratch, and modifying the canned reports. – If you’re interested, please leave comments on any suggestions you’d like to see pertaining to ConfigMgr reports.

 

Other worthy notes:

  1. To disable the RBA (@UserSIDs) stuff, check out this link: https://blogs.technet.microsoft.com/michaelgriswold/2014/09/17/disabling-rbac-during-custom-report-creation/
  2. Report Builder Tutorials – This is where I learned a lot of what I know (Besides just messing around) - https://msdn.microsoft.com/en-us/library/dd239338.aspx

Dell Warranty Reporting via ConfigMgr - Dell Warranty API

Update 11/3 – Contact info for Dell API key has changed: (Info directly from Dell API Support team)
New process - The customer should create ticket via TechDirect
https://techdirect.dell.com;
Alternatively they can reach out to us at
APIs_TechDirect@Dell.com

Update 10/20  - Nickolaj posted his Dell Right Click tool, so if you’ve take then time to get the Dell API, you should add his tool for individual lookups in the Console.

Update 8/18 – Finally received our Production Key.  In the Meantime @Geodesicz also modified the script to run in either Sandbox or Production mode.  It will try if the Key can be used on Dells production API environment, and move forward if it can, otherwise, if errors, then falls back to the SandBox API. – I’ve pasted the updated Script in below and updated the latest version in the download.

Update 7/15 - Working with Dell, found we were using the older API, needed to be using v4 instead of v2 API.  Currently rewriting entire Script to use new API calls.  Will update this Post once the new script is done and tested with the updated API URL.  Will provided updates scripts and Reports once complete.

Update 7/11 - Modified Script to use the Actual Dell API URL instead of their SandBox URL. (Updated Text below & Download Zip File)

 

Recently I attended MNSCUG meeting where Jason Sandys presented on ConfigMgr database.  He showed his OSD Info Script that would write information to WMI so that you could then capture it with the hardware inventory to report back on additional information.  That got me thinking about what else I'd like to have in WMI to report on... Dell Warranty Info!

Started looking for a Dell Warranty Script to get me started, found one HERE. Used that to sparks some other ideas.  Contacted Dell to get the Dell Warranty API access process started. – https://techdirect.dell.com – Create a ticket, or alternatively, contact APIs_TechDirect@Dell.com.
Once you’ve contacted Dell to get the conversation started, you'll have to fill out some questionnaire, etc., but eventually, you'll get your access and own personal API Key.

I worked with my friend @Geodesicz (Mark) who is a PowerShell wizard, and he was able to build me the script I wanted.  Pulls Dell Warranty Info from Dell's warranty API and places that info into WMI on the workstation.  We are using a new namespace called ITLocal, which was already on the machines since we had implemented Jason Sandys OSDInfo Script the week before and decided to use that namespace for consistency.

Download Our Script HERE - Code shown below:

<#
Write Dell Warranty Information to WMI V2.2
Queries Dell Web Service API V4 for Warranty Info
Creates Custom WMI Namespace and Class
Writes Warranty Info to WMI
Requires PowerShell V5
---------------
You can also add this custom class to be collected by Configuration Manager hardware inventory
---------------
Script written by Mark Godfrey (http://www.tekuits.com/blog) and Gary Blok (http://garytown.com/) - MN.IT
#>

[CmdletBinding()]
Param(
    [Parameter(Mandatory=$true,Position=1,HelpMessage="APIKey")]
    [ValidateNotNullOrEmpty()]
    [string]$APIkey
)

# Get Service Tag of Local Machine
$ServiceTag = Get-WmiObject -Class Win32_Bios | select -ExpandProperty SerialNumber
      
# Query Web Service API
Try
{
    $URL1 = "https://api.dell.com/support/assetinfo/v4/getassetwarranty/$ServiceTag"
    $URL2 = "?apikey=$apikey"
    $URL = $URL1 + $URL2
    $Request = Invoke-RestMethod -URI $URL -Method GET
}
Catch [System.Exception]
{
    Write-Output "Production API URL failed, switching to sandbox API"
    $URL1 = "https://sandbox.api.dell.com/support/assetinfo/v4/getassetwarranty/$ServiceTag"
    $URL2 = "?apikey=$apikey"
    $URL = $URL1 + $URL2
    $Request = Invoke-RestMethod -URI $URL -Method GET
}
$Warranties = $Request.AssetWarrantyResponse.assetentitlementdata | where ServiceLevelDescription -NE 'Dell Digitial Delivery'
$AssetDetails = $Request.AssetWarrantyResponse.assetheaderdata

# Set Vars for WMI Info
$Namespace = 'ITLocal'
$Class = 'Warranty_Info'

# Does Namespace Already Exist?
Write-Verbose "Getting WMI namespace $Namespace"
$NSfilter = "Name = '$Namespace'"
$NSExist = Get-WmiObject -Namespace root -Class __namespace -Filter $NSfilter
# Namespace Does Not Exist
If($NSExist -eq $null){
    Write-Verbose "$Namespace namespace does not exist. Creating new namespace . . ."
    # Create Namespace
       $rootNamespace = [wmiclass]'root:__namespace'
    $NewNamespace = $rootNamespace.CreateInstance()
    $NewNamespace.Name = $Namespace
    $NewNamespace.Put()
    }

# Does Class Already Exist?
Write-Verbose "Getting $Class Class"
$ClassExist = Get-CimClass -Namespace root/$Namespace -ClassName $Class -ErrorAction SilentlyContinue
# Class Does Not Exist
If($ClassExist -eq $null){
    Write-Verbose "$Class class does not exist. Creating new class . . ."
    # Create Class
    $NewClass = New-Object System.Management.ManagementClass("root\$namespace", [string]::Empty, $null)
    $NewClass.name = $Class
    $NewClass.Qualifiers.Add("Static",$true)
    $NewClass.Qualifiers.Add("Description","Warranty_Info is a custom WMI Class created by Gary Blok(@gwblok) and Mark Godfrey(@geodesicz) to store Dell warranty information from Dell's Warranty API.")
    $NewClass.Properties.Add("ComputerName",[System.Management.CimType]::String, $false)
    $NewClass.Properties.Add("Model",[System.Management.CimType]::String, $false)
    $NewClass.Properties.Add("ServiceTag",[System.Management.CimType]::String, $false)
    $NewClass.Properties.Add("ServiceLevelDescription",[System.Management.CimType]::String, $false)
    $NewClass.Properties.Add("ServiceProvider",[System.Management.CimType]::String, $false)
    $NewClass.Properties.Add("StartDate",[System.Management.CimType]::String, $false)
    $NewClass.Properties.Add("EndDate",[System.Management.CimType]::String, $false)
    $NewClass.Properties.Add("ItemNumber",[System.Management.CimType]::String, $false)
    $NewClass.Properties["ItemNumber"].Qualifiers.Add("Key",$true)
    $NewClass.Put()
    }

# Write Class Attributes
$Warranties | ForEach{
    $wmipath = 'root\'+$Namespace+':'+$class
    $WMIInstance = ([wmiclass]$wmipath).CreateInstance()
    $WMIInstance.ComputerName = $env:COMPUTERNAME
    $WMIInstance.Model = "Dell " + ($AssetDetails.MachineDescription)
    $WMIInstance.ServiceTag = $AssetDetails.ServiceTag
    $WMIInstance.ServiceLevelDescription = $PSItem.ServiceLevelDescription
    $WMIInstance.ServiceProvider = $PSItem.ServiceProvider
    $WMIInstance.StartDate = ($PSItem.StartDate).Replace("T00:00:00","")
    $WMIInstance.EndDate = ($PSItem.EndDate).Replace("T23:59:59","")
    $WMIInstance.ItemNumber = $PSItem.ItemNumber
    $WMIInstance.Put()
    Clear-Variable -Name WMIInstance
    }

Syntax: WriteDellWarranty2WMI.ps1 -APIKey Y0UR@P1K3Y  (Get your APIKey from Dell)
image

Make a Package & Program and push it out to your dell Computers. (remember to have Windows Manage Framework 5 already installed on your workstations).

image

We also added this to OSD, like so:
image

 

Here is a capture of the info in WMI after the Script has been run: (Using Coretech WMI Browser)
image

Once in WMI, we have to add this to our Hardware Inventory:

Go into the Default Client Settings, Hardware Inventory -> Set Classes -> Add... -> Connect to WMI namespace root\ITLOCAL
image

Once Connected, you should see the Warranty_Info

image

After you check the box, and click OK, it should show up in your classes list:
image

I unchecked everything here, because I only want to apply this to my Dell Workstations.
I opened our Dell Client Workstation Settings (We have one that applies only to Dell Workstations, which I borrowed the idea from Mike Terrill after reading this Post about inventory bios settings.

image

Ok, now wait for your Hardware Inventory cycles to run and the data to populate in your ConfigMgr DB.

Ok, Building reports... Fun with SQL Report Builder!
Mark & I have created two quick and easy reports for now, just as proof of concept, but will probably find ways to pull this data into other reports as well.

  1. Dell Warranty Expired Report - This report shows all of the Computers in ConfigMgr that have expired Warranties:
    image
    You can click on the + next to the computer name to expand if has more than 1 warranty associated with it.
  2. Dell Warranty Expires between dates - This report will let you pick dates and show you the computers that will expire. (If computer has more than 1 warranty, it will only use the warranty with the latest end date)
    image
  3. Dell Warranty Info for specific Computer: Super simple report that lets you put in a computer name and get the info:
    image

 

We created a new folder called Hardware - Warranty, created our reports there.  I've added those 3 reports to the ZIP file you can download with the Script.

Just import them into your system, as long as you kept the namespace the same as the one in our script, it should work fine.  Update your Data source in each report, and you should be set.

In the 2 reports, I have it so if you click on the computer name, it links to a hardware report with more info about that specific computer.  I think it's a built in ConfigMgr report, so it should still work, if not, just delete that action:
image

 

I really hope I didn't miss anything, there was a lot of parts to this.  Mark will also be blogging this, since it was a joint project.  He'll probably have more info about the Powershell stuff itself.

Couple of things to remember... ConfigMgr DB is NOT the same as a Configuration Management Database.  When you delete the computer from ConfigMgr, there goes the data along with it.  I make sure to tell our management, this data is ONLY useful for computers currently active in our system, we do NOT keep historical data.

As always, if you run into any problems, please feel free to contact me, I'll update the Blog to correct anything found.  Sometimes its hard to get the proper screen captures after you've already set it up and been using it.

Also, I highly recommend checking out Jason's OSDInfo Script & Mike Terrill's Dell Posts, those might help shed some light on what we've done.

ConfigMgr Report - Software Information by Title by Collection

Files Included in ZIP File (Download HERE: garytown.com/Downloads/MultiSoftwareVersions.zip)
Documentation (This File)
A Multi Software versions Inventory Report.rdl – MAIN REPORT FILE
SoftwareCountsDetailVersion.rdl – SUPPORT REPORT FILE

Assumptions, you have MS Report Builder installed, you have access to your CM Report Server, and can Save & Modify reports on your CM Report Server, and some knowledge of how Datasets & DataSources work.

This is a Report to Search Specified Collection for Specified Software

 

Collections List Can Be Modified & Software List Can be modified... More Later. Choose a Collection & Software, then click View Report

 When when you click on a Version or Software Name, you’ll see the computers with software that matches the Software Name or Version Number.

 

 

 

Once you click on the Name or Version, you’ll get forward to another page with those machines

Information includes: PC Name, Software Name, Version, Last Logon Name, Email Address for User, Computer info, and the last time that computer had a CM Hardware Inventory Scan.

To install the reports, once you have saved both of them to your Configuration Manager SSRS, you’ll need to create a new data source in the report and fix the datasets to use the correct data source.

The Main Report uses “SoftwareCountsDetailVersion” (Linked on the “Software Name” & “Versions” Text Box)

 

 

 (Linked on both “Software Name” &  “Version_Number” Text Box)

 

To modify the Available software in the Drop Down List:  Modify the Query in the “SoftwareSelection” Dataset.
These will correspond to the @SoftSelect Parameter which creates the Drop Down list.

To Change the Collections, Modify CollectionDropDown Dataset

Replace the CollectionID numbers with the ones you want (SMS00001 = All Systems)

 

Please provide any feedback that will help improve this documentation or reports.

garywblok@gmail.com

 

 

 

Chrysanth WebStory Published by WebStory