Custom Reports in Snow License Manager

Development • 19 April 2022

This post was updated 02/08/2023 to correct an error in relation to CID usage on SPE platforms.

Snow License Manager is great. It has an insane amount of Inventory data from desktops, servers and mobile devices to SaaS providers like Zoom. It has a lot of reports built in that have been created by the development team but you’ll probably find something you want isn’t there. This isn’t just a Snow thing, this is common to all products and unlike some other products, you can actually create custom reports in T-SQL and have them within your Web GUI and allow your system users to run them like any other report.

So, what is a custom report? By assumption most people refer to Saved User Reports as Custom Reports and technically they’re not wrong. A Saved User Report is a customized version of a default report in Snow License Manager which usually has extra or less columns and pre-set criteria like Organisation = Department A which can then be scheduled or viewed without setting your columns and criteria every time you run the report. A Custom Report on the other hand is created in T-SQL within your SnowLicenseManager database and then inserted into the tblReport and tblReportSecurity tables. That’s a very simple explanation of what they are but hopefully you get the gist.

# Prerequisites

Before you can create a custom report there are a few things you’ll need or would benefit having.

• Required: Snow License Manager environment
• Required: Administrator on Snow License Manager Web GUI
• Required: Write permission to your SnowLicenseManager database
• Required: SQL Server Management Studio (SSMS) and how to use it
• Required: Basic understanding of SQL scripting
• Recommended: Database Backup or make use of a PRE-PROD environment
• Recommended: Understanding of SnowLicenseManager tables, procedures and overall structure

I’ll be providing scripts that can be copy and pasted into SSMS but ideally you should never randomly copy and paste scripts from the internet that you’re not entirely sure what they do, especially if you’re doing this directly into a production environment. If you’ve never used T-SQL before, I recommend looking through one of these resources and getting to grips with the basics.

• Tutorialspoint - T-SQL
• SQLServer Tutorial.net
• w3schools - SQL Tutorial

Note that at the time of writing the above external websites are suitable for a basic understanding of T-SQL. I cannot guarantee that in two weeks or two years that the content will still be useful, appropriate or relevant. I would recommend further research if required and finding your own resources.

# Creating your Initial Script

Before creating your Custom Report, you first need to create a normal script that returns the data that you’re looking to get. For this example I’ll be creating a report that provides all of the uploaded documents per an inventoried device, this report would be useful if you had an internal requirement to upload purchase or warranty documents to all devices in the system.

1
2
3
4
5
6
7
8
9
SELECT 
    c.ComputerID
    ,c.HostName
    ,d.Title AS [Document Title]
    ,d.FileName
    ,d.ContentType AS [Document Type]
FROM [SnowLicenseManager].[dbo].[tblDocuments] d
INNER JOIN [SnowLicenseManager].[dbo].[tblComputer] c ON d.ParentID = c.ComputerID
WHERE DocumentType = 3 AND c.CID = 1

In the above example we are doing our initial SELECT from the tblDocuments table with the alias d. We’re then joining it to our tblComputer table with the alias c, based on the ParentID in tblDocuments being the same as the ComputerID in tblComputer. We’re then finishing the script off with a WHERE statement ensuring that we’re only returning Computer Documents with DocumentType = 3 and limiting the data to the Customer with CID = 1.

You can find the CID for each customer in the platform within the SnowLicenseManager.dbo.tblCID table, if you’re running an enterprise platform with only one customer in it then the CID will always be 1.

If you are creating a custom report on an SPE platform, ensure that you use CID = {0} instead of directly referencing a CID number as you may accidentally leak customer data otherwise.

On Lines 2 - 6, these are the columns that we are wanting to appear in our output. It’s currently configured to return the ComputerID, HostName, Title of the document, FileName of the document, and the ContentType of the uploaded document. I’ve added the alias to the start of each column name to ensure that if both tables have an identical column name that the correct column is being returned.

Assuming that you have documents assigned to some computers in Snow License Manager, when you run the below in SSMS you should see the below, although your data will be different naming wise.

# Creating your Custom Report

Here’s the fun part, creating and adding the report to Snow License Manager. Just a few things to point out

• This is a basic way of doing this, you can do it with stored procedures as well
• Just because this is the way I do it, doesn’t mean it’s the correct way - I’ve never had a problem though
• I haven’t documented every column, but if you do want to know anything about any I haven’t feel free to email me
• I didn’t originally create this ‘template’, Mark Potts did and I now refer to it as old faithful because it’s never done me wrong

I’m going to split this out into four different sections, however I’ll include the finished script at the bottom. As mentioned in the Prerequisites section, I highly recommend doing this on a test environment before pushing anything into PROD as the Script I use for creating Custom Reports does contain DELETE statements and I’m trusting you not to accidentally delete default reports, I do mention below a way of not accidentally doing that though.

# Variables

For ease of use there are seven variables at the top of the script. Six of them need to be filled out for the report to go into the system correctly and function correctly.

1
2
3
4
5
6
7
8
9
USE [SnowLicenseManager]

DECLARE @ReportName            NVARCHAR(MAX) = 'Custom Report for Documents per Inventoried Asset'
DECLARE @ReportID              uniqueidentifier = (SELECT NEWID())
DECLARE @ReportDescription     NVARCHAR(MAX) = 'Shows all documents for each asset in the system'
DECLARE @ColumnList            NVARCHAR(MAX) = 'ComputerID,HostName,Document Title,FileName,Document Type'
DECLARE @ColVis                NVARCHAR(MAX) = '-1,1,1,1,1'
DECLARE @KeyFieldName          NVARCHAR(MAX) = 'ComputerID'
DECLARE @RowTargetLink         NVARCHAR(MAX) = 'Computer.aspx?='

Hopefully the above is pretty self explanatory but just in case. @ReportName is the name that appears in the Snow License Manager report list and throughout the system, such as All Computers. I include Custom Report for at the start of all of my Custom Reports to prevent me accidentally overwriting or deleting a default report from the system. @ReportID is automatically generated and is a random UUID/GUID, don’t change this.

@ReportDescription is the description of the report that appears under it’s name when viewing the report or when in the All Reports page. The @ColumnList is a list of the columns that are in your script created in Creating your Initial Script. @ColVis is whether or not you want the column to be visible, 1 for true and 0 for false. When a column is set to 0 it does not appear by default but can be selected via the Column Selector. If you want to have the column used for something else but not usable to the End User you can use a -1. The values match up with the columns in Column List, so in this case ComputerID is not usable or selectable but everything else is visible by default.

@KeyFieldName and @RowTarget are useful but depending on the type of reports you’re creating, you might not use all that often. The RowTargetLink refers to the page of the object you’re viewing, so for Computers it’s Computer.aspx?id=, for Users it’s User.aspx?id=, etc. The KeyFieldName refers to the actual ID of said object, which in this case is ComputerID. This means that when you click on a row in the report it will take you to that physical object in the system, they’re not actually required and can be left blank, but I thought I’d include it.

# Old Report Cleanup

If you’re like me you’ll probably find yourself updating Custom Reports every so often. To make this easier, I started to include DELETE statements at the top of my report script when executing so I wouldn’t end up with duplicate reports in the system that return different results, or just don’t appear at all because of identical names.

1
2
3
4
DELETE FROM [tblReportSecurity] WHERE ReportID = (SELECT ReportID FROM [tblReport] WHERE Name = @ReportName)
DELETE FROM [tblReportExportSchedule] WHERE ReportID = (SELECT ReportID FROM [tblReport] WHERE Name = @ReportName)
DELETE FROM [tblSystemUserReports] WHERE BaseReportID = (SELECT ReportID FROM [tblReport] WHERE Name = @ReportName)
DELETE FROM [tblReport] WHERE Name = @ReportName

There are four major tables involved when adding a report to Snow License Manager, tblReportSecurity, tblReportExportSchedule, tblReport. Each serves it’s own purpose but as there is no ‘procedure’ for deleting reports from the system, if you simply add the report in again or delete it from one table you’ll be left with stale data in the database that may or may not become a pain in the future.

The first two delete statements are removing the report information from tblReportSecurity which determines who can actually view the report (Administrator, Viewer, etc.). The second delete statement is reporting data from tblReportExportSchedule so that there is no left over schedule data assigned to users. Both of these do this based off of the ReportID which is generated when the original report was created, which we are able to do by searching tblReport for a report with the same name as the one we’re adding.

The third statement removes the report information from tblSystemUserReports, this will delete any ‘user saved’ reports which are based on the Custom Report. Once the Custom Report is removed from the system these reports will not work, thus we want to remove the left behind data.

The forth delete statement is deleting the actual report itself and everything assigned to it, so if you find that your new report has a bug in it and need to revert back, make sure you have a backup of the original report.

# The Script

This section is pretty simple, it’s simple a variable that has the script you created early in it.

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE @sql NVARCHAR(MAX) = 
'
SELECT 
    c.ComputerID
    ,c.HostName
    ,d.Title AS [Document Title]
    ,d.FileName
    ,d.ContentType AS [Document Type]
FROM [SnowLicenseManager].[dbo].[tblDocuments] d
INNER JOIN [SnowLicenseManager].[dbo].[tblComputer] c ON d.ParentID = c.ComputerID
WHERE DocumentType = 3 AND c.CID = 1
'

If you’re using a Service Provider Edition platform, you’ll want to replace c.CID = 1 with c.CID = {0}. This means when Customer A views the report they see their data, and when Customer B views the report they see their data - it’s a wildcard I guess?.

Another honorble mention is that if you have a report that contains single quotes, such as WHERE OperatingSystem LIKE '%Windows%' you want to wrap it in single quotes twice, like ''%Windows%'' - these ARE NOT DOUBLE QUOTES.

# Adding the Report

Finally, this section adds the report to the actual system and the relevant tables.

1
2
3
4
5
INSERT INTO [tblReport] (ReportID, StockReport, IsCustomReport, ReportType, ViewName, Name, Description, SQLQuery, ColumnList, ColumnVisibility, KeyFieldName, RowTargetLink, UsesCustomFields, CustomFieldCategoryID)
VALUES (@ReportID, 0, 1, 3 , 'QUERYBUILDER', @ReportName, @ReportDescription, @sql, @ColumnList, @ColVis, @KeyFieldName, @RowTargetLink, 0, 0)

INSERT INTO [tblReportSecurity]
SELECT ReportID, 0 FROM [tblReport] WHERE ReportID = @ReportID

In the first INSERT statement we’re inserting the report into the tblReport table and making it available to users in the system. The INSERT INTO should be pretty self explanatory for most of the values as they match up with the previously explained variables, however for the ones that aren’t:

The second INSERT statement is inserting the Report ID into the tblReportSecurity table. @ReportID is as defined previously, 0 is the ID of the System User Group that can view the report which in this case would be Administrator only.

# Whole thing

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
USE [SnowLicenseManager]

DECLARE @ReportName            NVARCHAR(MAX) = 'Custom Report for Documents per Inventoried Asset'
DECLARE @ReportID              uniqueidentifier = (SELECT NEWID())
DECLARE @ReportDescription     NVARCHAR(MAX) = 'Shows all documents for each asset in the system'
DECLARE @ColumnList            NVARCHAR(MAX) = 'ComputerID,HostName,Document Title,FileName,Document Type'
DECLARE @ColVis                NVARCHAR(MAX) = '-1,1,1,1,1'
DECLARE @KeyFieldName          NVARCHAR(MAX) = 'ComputerID'
DECLARE @RowTargetLink         NVARCHAR(MAX) = 'Computer.aspx?id='

DELETE FROM [tblReportSecurity] WHERE ReportID = (SELECT ReportID FROM [tblReport] WHERE Name = @ReportName)
DELETE FROM [tblReportExportSchedule] WHERE ReportID = (SELECT ReportID FROM [tblReport] WHERE Name = @ReportName)
DELETE FROM [tblSystemUserReports] WHERE BaseReportID = (SELECT ReportID FROM [tblReport] WHERE Name = @ReportName)
DELETE FROM [tblReport] WHERE Name = @ReportName

DECLARE @sql NVARCHAR(MAX) = 
'
SELECT 
    c.ComputerID
    ,c.HostName
    ,d.Title AS [Document Title]
    ,d.FileName
    ,d.ContentType AS [Document Type]
FROM [SnowLicenseManager].[dbo].[tblDocuments] d
INNER JOIN [SnowLicenseManager].[dbo].[tblComputer] c ON d.ParentID = c.ComputerID
WHERE DocumentType = 3 AND c.CID = 1
'

INSERT INTO [tblReport] (ReportID, StockReport, IsCustomReport, ReportType, ViewName, Name, Description, SQLQuery, ColumnList, ColumnVisibility, KeyFieldName, RowTargetLink, UsesCustomFields, CustomFieldCategoryID)
VALUES (@ReportID, 0, 1, 3 , 'QUERYBUILDER', @ReportName, @ReportDescription, @sql, @ColumnList, @ColVis, @KeyFieldName, @RowTargetLink, 0, 0)

INSERT INTO [tblReportSecurity]
SELECT ReportID, 0 FROM [tblReport] WHERE ReportID = @ReportID

If you paste the above (after reviewing it) into SQL Server Management Studio in yourt Development Environment and execute, then login to Snow License Manager, you should now be able to see and run the report within the portal and see something similar to the below

As mentioned at the start, this is a very simple report. It should give you a general idea of what you’re doing when creating custom reports in Snow License Manager, however if you have any issues feel free to reach out to me via email - otherwise have fun. 😀

//

Song Addiction at he moment: Modern Baseball - I Think You Were in My Profile Picture Once 2012 is calling me

Catch. 😊x