jorge.pascua

How to: Using collected registry values to create SLM report

Blog Post created by jorge.pascua Employee on Jul 3, 2018

Hi community,

 

Continuing the "How to" series (started with the How to: Using Snow Inventory Agent to collect registry values post), it's time to show you how to consume the collected info.

 

First, let see the result from SLM UI

Ok, lets see the report definition:

 

DECLARE @NAME NVARCHAR(MAX) = 'Customer Custom Registry Info'

DECLARE @uid uniqueidentifier = (SELECT NEWID())

DECLARE @Description NVARCHAR(MAX) = 'Report on Custom Registry Info'

DECLARE @ColumnList NVARCHAR(MAX) = 'ComputerID,HostName,Domain,Hierarchy,LastScanDate,LastModified,Contact,InstallDate,RegistryKey,Name,LastModifiedBy,Version'

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

DECLARE @KeyFieldName NVARCHAR(MAX) = 'ComputerID'

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

DECLARE @SQL NVARCHAR(MAX) = '

SELECT

DISTINCT tc.ComputerID as [ComputerID],

tc.HostName as [HostName],

tc.Domain as [Domain],

tco.Hierarchy as [Hierarchy],

tc.LastScanDate as [LastScanDate],

LastModified as [LastModified],

Contact as [Contact],

InstallDate as [InstallDate],

piv.KeyName as [RegistryKey],

Name as [Name],

LastModifiedBy as [LastModifiedBy],

Version as [Version]

FROM (

SELECT

tcr.KeyName,

tcr.ComputerID,

tcr.CID,

tcr.ValueName,

tcr.ValueData

FROM

tblComputerRegistry tcr

WHERE

tcr.KeyName = ''HKEY_LOCAL_MACHINE\SOFTWARE\CustomerCustomInfo\MasterImage''

and tcr.CID = {0}

) src

pivot

(

MAX(ValueData)

for [VaHope lueName] in ([Contact],[InstallDate],[LastModified],[Name],[LastModifiedBy],[Version])

) piv

INNER JOIN tblComputer tc

ON tc.ComputerID = piv.ComputerId

LEFT JOIN tblComputerInfo tci

ON tci.ComputerID = tc.ComputerID

INNER JOIN tblsystemuserorgdefinition def

ON def.cid = tc.cid

AND def.userid = {1}

AND def.orgchecksum = tci.orgchecksum

LEFT JOIN tblOrganization tco

ON tco.OrgChecksum = tci.OrgChecksum

WHERE tc.cid = {0}

ORDER BY Hostname, RegistryKey desc'

 

DELETE from tblReport

Where Name like @NAME

 

INSERT INTO tblReport (ReportID, StockReport, IsCustomReport, ReportType, ViewName,

Name, Description, SQLQuery, ColumnList, ColumnVisibility, KeyFieldName, RowTargetLink)

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

 

According to the the structure of the registry info:

we need to get the registry values for the registry path HKEY_LOCAL_MACHINE\SOFTWARE\CustomerCustomInfo\MasterImage.

 

This is done with the filtering in conjunction with pivot statement:

WHERE

tcr.KeyName = ''HKEY_LOCAL_MACHINE\SOFTWARE\CustomerCustomInfo\MasterImage''

and tcr.CID = {0}

) src

pivot

(

MAX(ValueData)

for [ValueName] in ([Contact],[InstallDate],[LastModified],[Name],[LastModifiedBy],[Version])

) piv

 

NOTE: You can see a detailed how to create SQL Reports in SLM in this post Adding custom SQL reports in Snow License Manager from my colleague mark.potts.

 

Done! You just created a custom report to show collected registry values during the Snow Inventory scan!

 

Hope you'll find this usefull. See you on the next "How to" post!

 

Jorge.

Outcomes