Using a Snow out of the box report in your custom report

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

In this document I am going to discuss how to use a Snow out of the box report to feed into a custom report that you are creating.

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

 

Let say we like the out of the box License compliance summary report, but our boss wants us to add the End of Life data from the application to the report (I have no idea why either), this data isn’t in the column selector.

Stage one of our process is to identify the stored procedure that Snow use to generate that report.

All the reports are stored in the SQL table tblReports, so we start by looking in there to find one that looks like the report we want to use

 

select [Name], [Description], [SQLQuery] from tblReport where [name] like '%compliance%'

 

In my results, I see an entry for Compliance overview summary, that sounds about right, and I can see the SQL query for that report is StockReportLicenseCostSummary

The next stage is to verify that is the correct report and to identify the data structure, to do this we execute the stored procedure in SQL

 

StockReportLicenseCostSummary 1,1

 

There are 2 parameters to this stored procedure CustomerID and UserID. In my environment we only have one customer so the CID is always 1, and there is an administrator user in the system that has a userid of 1, so by providing the parameters 1,1 I should get all the results returned, if you have multiple customers, or you need to execute the procedure as a different user then change the parameters as required.

What you should now have is the results of the stored procedure displayed in your SQL results window.

Now the fiddly bit. If we want to use this data in our custom report, we need somewhere to store it, I don’t believe it is possible to dynamically create a table from the results of a stored procedure, nor can we select which columns we want returned from the procedure, so we have no choice but to manually define the table to hold the results.  Think carefully about how you do this, if you will have a number of reports using the custom data, then it may be better to create a “real” table rather than a temporary table to store the results, that way you are not rerunning the Snow report every time you run your custom report.  As that data is mainly updated by the data update job overnight we could just make sure we update our “real” table as part of that process then we can reference our data in what every reports we like.  In my example that is what I am doing.

The easiest way I have found to create the table, is to copy to results to a spreadsheet, then take the column headers and paste them back into SQL, and turn it into a table create statement. And of course I had to pick a massive one for this example.

CREATE TABLE [dbo].[CustomReport_LicenseCostSummary] (

[ApplicationID] uniqueidentifier NULL,

[Application] nvarchar(MAX) NULL,

[ManufacturerName] nvarchar(MAX) NULL,

[ApplicationMetric] int NULL,

[TotalLicenses] int NULL,

[LicenseRequirement] int NULL,

[AvailableLicenses] int NULL,

[Coverage] int NULL,

[Compliance] int NULL,

[TotalLicenseCost] decimal(18, 2) NULL,

[InitialRequirement] int NULL,

[IncompleteLicenses] int NULL,

[ExpiredSubscriptionLicenses] int NULL,

[InvalidAssignments] int NULL,

[Unassigned] int NULL,

[SiteLicenses] int NULL,

[DowngradesGiven] int NULL,

[DowngradesReceived] int NULL,

[UpgradesGiven] int NULL,

[UpgradesReceived] int NULL,

[CrossPlatformGiven] int NULL,

[CrossPlatformReceived] int NULL,

[CrossEditionGiven] int NULL,

[CrossEditionReceived] int NULL,

[IncludedInBundle] int NULL,

[OutsideMetricPeriod] int NULL,

[SecondaryUseRightsDesktopLaptop] int NULL,

[SecondaryUseRightsDevice] int NULL,

[ExtendedCoverageForDcc] int NULL,

[ExtendedCoverageForHosts] int NULL,

[MultipleVersionEdition] int NULL,

[VirtualDesktopInfrastructure] int NULL,

[MinimumRequirement] int NULL,

[CoreFactor] int NULL,

[PhysicalHost] int NULL,

[MainCoverage] int NULL,

[OtherMetricCoverage] int NULL,

[SiteCoverage] int NULL,

[VmCoverage] int NULL,

[LicenseStacking] int NULL,

[LicensingOtherMetrics] int NULL,

[VirtualDesktopAccess] int NULL,

[TransfersGiven] int NULL,

[TransfersReceived] int NULL,

[Quantity] int NULL,

[ApplicationCost] decimal(18, 2) NULL,

[AppCostPerMetric] decimal(18, 2) NULL,

[ApplicationTypeList] nvarchar(MAX) NULL,

[ApplicationCategoryList] nvarchar(MAX) NULL,

[BundleApplications] nvarchar(MAX) NULL,

[IsBundle] bit NULL,

[RiskUnusedSoftware] decimal(18, 2) NULL,

[ApplicationFamily] nvarchar(MAX) NULL,

[UnusedInstallations] int NULL,

[NoOverlicensedAlerts] bit NULL,

[NoUnderlicensedAlerts] bit NULL,

[IsOS] bit NULL,

[ActiveUpgradeRights] int NULL,

[Users] int NULL,

[Installations] int NULL)

 

Once you have your table created, just check you can store the data from the Snow report in your table.

truncate table CustomReport_LicenseCostSummary

  

insert into [CustomReport_LicenseCostSummary]

exec StockReportLicenseCostSummary 1,1

 

So this query will empty the table first (Just in case you have put data in it already), then run the Snow report and store the output in your CustomReport_LicenseCostSummary table

Make sure this works OK with no errors, for example if you have got fields missing, or the incorrect data types etc. you will see errors with this process. If it works with no errors, just validate the data by doing a simple select statement.

select * from [dbo].[CustomReport_LicenseCostSummary]

 

Make sure the data looks correct, for example you haven’t got the fields in the wrong order.

The next step would be to automate the process for keeping that table up to date by adding it to the data update job, but that will be the topic for another document, in the meantime you could either add the code block above into the report which will refresh the data every time you run the report, or schedule it to run as a SQL job daily which would be much more efficient.

Now you have the Snow data available, you can proceed the same as writing your SQL from scratch.

select lcs.*, tba.EndOfLifeDate from [dbo].[CustomReport_LicenseCostSummary] lcs

left join tblApplication tba on lcs.applicationID = tba.ApplicationID

 

And of course this report could be added into SLM by using the instructions I posted here:

https://community.snowsoftware.com/docs/DOC-1283-adding-custom-sql-reports-in-snow-license-manager

1 person found this helpful

Attachments

    Outcomes