Adding custom SQL reports in Snow License Manager

Document created by MarkPotts Advocate on Nov 2, 2017Last modified by MarkPotts Advocate on Nov 3, 2017
Version 2Show Document
  • View in full screen mode

We have created many custom reports, some of these we wanted to make available to the end users who are used to using the SLM console, we didn’t want them to have to go off and run the reports using another tool, so we load them into the SLM console from the database, and here is how we do it.

 

This guide has been tested on Snow License Manager Version 8.1, it may not work on other versions.

 

As always if you are making changes at the database level THERE ARE RISKS INVOLVED, always back up your database, and preferably make the changes to a test system before putting it into production, and as always if your system breaks as a result of you trying to follow these instructions I take no responsibility, it is up to you to be confident about making any changes detailed in this guide.  If you are unsure, don’t do it and ask Snow for assistance

 

Prepare your query:

 

Get the code working in SQL first, make sure it returns the columns you are expecting, always try to include a key column that is relevant to the data, for example if it’s a computer report, include the clientid, if its applications include the applicationid

 

Here is a very simple example

 

select DISTINCT tco.[ClientID], tco.[HostName] as 'Computer Name', tco.[Manufacturer] as 'Computer Make', tco.[Model] as 'Computer Model', tco.IsPortable as 'Is Laptop', tco.[ClientInstallDate] as 'First Seen', tco.OperatingSystem

from tblComputer tco

where tco.OperatingSystem like '%Windows 10%'

order by tco.[HostName]

 

All the reports available in the SLM portal are stored in the database table tblReports, so to make our report visible we need to add it to that table. Here is some funky SQL to do that.

 

Please note this SQL only works for “SQL Statements” if you are executing a stored procedure rather than a SQL statement, you need to run slightly different SQL which I will document another day

 

 

 

DECLARE @NAME                 NVARCHAR(MAX) = 'Windows 10 Report'

DECLARE @uid                   uniqueidentifier = (SELECT NEWID())

DECLARE @Description           NVARCHAR(MAX) = 'This report shows the computers running Windows 10'

DECLARE @ColumnList           NVARCHAR(MAX) = ' ClientID,Computer Name,Computer Make,Computer Model,Is Laptop,First Seen,OperatingSystem'

DECLARE @ColVis               NVARCHAR(MAX) = '1,1,1,1,1,1,1'

DECLARE @KeyFieldName         NVARCHAR(MAX) = 'ClientID'

DECLARE @RowTargetLink         NVARCHAR(MAX) = 'Computer.aspx?id='

 

DELETE FROM tblReport WHERE Name = @NAME

 

DECLARE @SQL                   NVARCHAR(MAX) = 'select DISTINCT tco.[ClientID], tco.[HostName] as ''Computer Name'', tco.[Manufacturer] as ''Computer Make'', tco.[Model] as ''Computer Model'', tco.IsPortable as ''Is Laptop'', tco.[ClientInstallDate] as ''First Seen'', tco.OperatingSystem

from tblComputer tco

where tco.OperatingSystem like ''%Windows 10%''

order by tco.[HostName]'

 

INSERT INTO tblReport (ReportID, StockReport, IsCustomReport, ReportType, ViewName, Name, Description, SQLQuery, ColumnList, ColumnVisibility, KeyFieldName, RowTargetLink, UsesCustomFields, CustomFieldCategoryID)

VALUES (@uid, 0, 1, 3 , 'QUERYBUILDER', @NAME, @Description, @SQL, @ColumnList, @ColVis, @KeyFieldName, @RowTargetLink, 1, 1)

 

INSERT INTO tblreportsecurity

SELECT ReportID, 0 FROM tblReport WHERE ReportID = @uid

 

OK there are MANY things to explain about the SQL before you go ahead and run it.

 

As you can see there are lots of parameters and options here so I will break the query down and explain them to you.

 

DECLARE @NAME                 NVARCHAR(MAX) = 'Windows 10 Report'

 

This is the title of the report, replace the text between the ‘ ‘ with the title you wish the report to have. In my example the report will be called Windows 10 Report

 

DECLARE @uid                   uniqueidentifier = (SELECT NEWID())

 

This will generate a unique ID for the report, you do not need to change this line

 

DECLARE @Description           NVARCHAR(MAX) = 'This report shows the computers running Windows 10'

 

This is the description of the report, replace the text between the ‘ ‘ with the description you wish the report to have. In my example the report description will be This report shows the computers running Windows 10

 

DECLARE @ColumnList           NVARCHAR(MAX) = ' ClientID,Computer Name,Computer Make,Computer Model,Is Laptop,First Seen,OperatingSystem'

 

This is the list of columns that will be returned by your SQL and will be the titles of the columns displayed in the console. A few things to note about this field.  If your column name has space in it, do not be tempted to surround them with square brackets as that will break the report.  Also if you leave a space before the column name, it will effectively hide that column. So for example in my example I have left a space before the ClientID column, so this column will be included in the report, but will not be displayed by default, using the column selector I could drag this field into the report.  Replace the text between the ‘ ‘ with the list of columns you wish the report to have.  In my example I have 7 columns of which the first one will be hidden.

 

DECLARE @ColVis               NVARCHAR(MAX) = '1,1,1,1,1,1,1'

 

This is supposed to affect the visibility of each column, but I have never got it to work, so I hide columns by using the space in the name trick I highlights above, so for this field just put in a 1 for each column included in your report, so in my example I have 7 columns, so 7 1’s.

 

DECLARE @KeyFieldName         NVARCHAR(MAX) = 'ClientID'

 

This line identifies which column in your SQL contains the key value for the item you are reporting on, so in my example it’s a computer record and the column in called ClientID

 

DECLARE @RowTargetLink         NVARCHAR(MAX) = 'Computer.aspx?id='

 

This line identifies what action should be taken when I click on a record in the report, as it is a computer report, and I have a computer ID, I allows the user to be able to drill into the computer record by double clicking on the computer. So in my example I am referencing the Computer.aspx?id= command.

 

The options for this field are:

 

Application:                        Application.aspx?id=

Computer:                          Computer.aspx?id=

ComputerHistory:            ComputerHistoryDetails.aspx?id=

Contract:                             Contract.aspx?id=

CustomObject:                 CustomObject.aspx?id=

DataCentre:                       Dcc.aspx?id=

Device:                                                 Device.aspx?id=

License:                                License.aspx?id=

Mobile:                                Mobile.aspx?id=

OracleDatabase:               OracleDatabase.aspx?id=

OracleOrder:                      OracleOrder.aspx?id=

User:                                     User.aspx?id=

 

DELETE FROM tblReport WHERE Name = @NAME

 

This line will delete any reports already in the table with the same name as the report you are loading, this allows you to make changes to the code and upload the report again without creating duplicates. It does not need changing.

 

DECLARE @SQL                  NVARCHAR(MAX) = 'select DISTINCT tco.[ClientID], tco.[HostName] as ''Computer Name'', tco.[Manufacturer] as ''Computer Make'', tco.[Model] as ''Computer Model'', tco.IsPortable as ''Is Laptop'', tco.[ClientInstallDate] as ''First Seen'', tco.OperatingSystem

from tblComputer tco

where tco.OperatingSystem like ''%Windows 10%''

order by tco.[HostName]'

 

Now for the actual SQL itself, the complete statement needs to be enclosed in the ‘ ‘ so replace the text between the two ‘ ‘ with the SQL you wish to run. Note if your code includes the ‘ mark you will need to escape it by adding an additional ‘ so in my code above I am defining some column names for example ‘’Computer Name’’ and you can see it has two quotation marks at each end so the SQL  code knows this is not the end of the statement.

 

INSERT INTO tblReport (ReportID, StockReport, IsCustomReport, ReportType, ViewName, Name, Description, SQLQuery, ColumnList, ColumnVisibility, KeyFieldName, RowTargetLink, UsesCustomFields, CustomFieldCategoryID)

VALUES (@uid, 0, 1, 3 , 'QUERYBUILDER', @NAME, @Description, @SQL, @ColumnList, @ColVis, @KeyFieldName, @RowTargetLink, 1, 1)

 

This code block just inserts all the data you have populated into the tblReport table, and does not need changing.

 

INSERT INTO tblreportsecurity

SELECT ReportID, 0 FROM tblReport WHERE ReportID = @uid

 

And finally, the last bit of code creates an entry in the tblReportSecurity table so that the report will be visible to all users.

 

So once you have edited this SQL to fit your needs, run it against the SnowLicenseManager database, and verify it completes with no errors.

 

Then logon to your SLM console, and go to reports.

 

You should see a section labelled Custom Reports, and if all worked correctly you should see your report, here is my example.

 

Report1

 

If you run the report, you should see the columns you asked to be visible.

 

Report2

 

And by clicking on the 3 dots and selecting Column Selector, you should be able to add any columns you asked to be hidden

 

Report3

 

 

Sorry for the very long winded document, but I hope it explains the process thoroughly enough for it to be helpful and useful for other people to follow. If anything is unclear please do ask.

 

I obviously have a day job to do, but I find writing reports interesting, so if you have specific ideas for reports, post them here and if they are reports I could use in my job, then I may take the time to develop them and share them.  I hope that Snow will also pay attention and add them as standard reports going forward.

8 people found this helpful

Attachments

    Outcomes