Samuel

Sibling computers: the tedious job made easy

Discussion created by Samuel on Sep 21, 2018
Latest reply on Sep 27, 2018 by Samuel

Dear all,

 

You know how application's 1) secondary use rights 2) home use rights or 3) work at home use rights apply to some selected users of Microsoft products (and maybe other manufacturers offer similar benefits too). Conditions apply, for example, Software Assurance is necessary for the work at home option. This would roughly apply as follows:

If a user is the primary user of a desktop computer, the per-device license assigned to this user's desktop computer also covers for the same application to be used by this same user on a company owned (secondary use rights) or personal (home use rights/work at home rights) portable device like a laptop.

Beware, this does not cover of the application on an RDS server from the portable device!

 

Snow supports this clever way of saving some extra licenses under a couple of features that combine together:

  • Secondary use rights - at the application level
  • Sibling computer - at the computer level

 

As a result, if the secondary use rights check-box is ticked for Microsoft Office products AND if a computer is defined as the sibling computer of another one, Snow will rightfully extend the benefits of the license to both computers.

When you have just a few computers, this is manageable, but when your estate contains hundreds, thousands or tens of thousands of computers (or when you are too lazy to manage that manually), this can become difficult. Waiving those rights can be quite expensive. Why not have Snow managing that in the background for us?

 

Let's assume we can find the list of all primary users of all our desktops AND that we can find what portable devices are used by only one user (to simplify), we could ask Snow to link those computers for us. Then we can tick the secondary use rights box on the appropriate applications and see our license requirement drop for said product.

This is exactly the purpose of the script below. It creates a stored procedure that links the correct computers together and you can add this stored procedure as a step after the Data Update Job by running this command:

-- Replace 'X' in the VALUES by the next available custom procedure index available in your system
USE [SnowLicenseManager]
INSERT INTO SnowLicenseManager.dbo.tblSystemCustomProcedures (DatabaseName, ProcedureOwner, ProcedureName, ProcedureArguments, ExecutionOrder, PostJob)

VALUES ('SnowLicenseManager', 'dbo', 'AutoLinkSiblingComputers', NULL, X, 1)

Feel free to read it, check it, fix it, improve it, implement it. If any fixing or improvement is added, or if you too have interesting tips to share, I would be grateful to hear about it.

USE [SnowLicenseManager]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AutoLinkSiblingComputers]
AS
BEGIN
-- Creation of temporary tables
CREATE TABLE #CurrentlyLinkedComputersTemp (
 DesktopID int,
 LaptopID int
)
INSERT INTO #CurrentlyLinkedComputersTemp
 Select tc1.ComputerID, tc1.SRComputerID FROM tblComputer tc1
 where tc1.IsPortable=0 and tc1.SRComputerID is not null
--SELECT * FROM #CurrentlyLinkedComputersTemp
CREATE TABLE #DesktopPrimaryUsersTemp (
 UserID int,
 ComputerID int,
 LogonCount int
)
INSERT INTO #DesktopPrimaryUsersTemp
 SELECT cu1.UserID, MAX(cu1.ComputerID), cu1.LogonCount
 FROM tblComputerUsers cu1
 INNER JOIN (
  SELECT MAX(cu3.LogonCount) DesktopLogonCount, cu3.UserID as UserID
  FROM tblComputerUsers cu3
  INNER JOIN tblComputer tc ON tc.ComputerID = cu3.ComputerID
  WHERE cu3.MostFrequent =1 and tc.IsPortable=0
  GROUP BY cu3.UserID
  ) cu5 on cu1.UserID = cu5.UserID and cu1.LogonCount = cu5.DesktopLogonCount
 INNER JOIN tblComputer c1 on c1.ComputerID = cu1.ComputerID
 WHERE cu1.MostFrequent = 1 AND c1.IsPortable = 0 and c1.IsVirtual = 0
 GROUP BY cu1.UserID, cu1.LogonCount
--SELECT * FROM #DesktopPrimaryUsersTemp ORDER BY UserID ASC
CREATE TABLE #LaptopPrimaryUsersTemp (
 UserID int,
 ComputerID int,
 --LogonCount int
)
INSERT INTO #LaptopPrimaryUsersTemp
SELECT tcu1.UserID, MAX(tcu1.ComputerID)
FROM tblComputerUsers tcu1
INNER JOIN (
 SELECT DISTINCT tcu2.ComputerID, SUM(CASE WHEN tcu2.ComputerID>DATEADD(day,-90, GETDATE()) THEN 1 ELSE 0 END) as UserCount
 from tblComputerUsers tcu2
 GROUP BY tcu2.ComputerID
) tcu3
ON tcu3.ComputerID = tcu1.ComputerID
INNER JOIN tblComputer tc ON tc.ComputerID = tcu3.ComputerID
WHERE tcu3.UserCount = 1 and  tc.IsPortable = 1
GROUP BY tcu1.UserID
--SELECT * FROM #LaptopPrimaryUsersTemp ORDER BY UserID ASC
CREATE TABLE #LinkableComputersTemp (
 DesktopID int,
 LaptopID int
)
INSERT INTO #LinkableComputersTemp
 SELECT dp.ComputerID, lp.ComputerID FROM #DesktopPrimaryUsersTemp dp
 INNER JOIN #LaptopPrimaryUsersTemp lp
 ON dp.UserID = lp.UserID
--SELECT * FROM #LinkableComputersTemp ORDER BY DesktopID ASC
CREATE TABLE #CorrectlyLinkedComputersTemp (
 DesktopID int
)
INSERT INTO #CorrectlyLinkedComputersTemp
 SELECT lc.DesktopID FROM #LinkableComputersTemp lc
 INNER JOIN #CurrentlyLinkedComputersTemp cl
 ON lc.DesktopID = cl.DesktopID AND lc.LaptopID = cl.LaptopID
--SELECT * FROM #CorrectlyLinkedComputersTemp ORDER BY DesktopID ASC
CREATE TABLE #ComputersToUnlink (
 DesktopID int,
)
INSERT INTO #ComputersToUnlink
 SELECT cl.DesktopID FROM #CurrentlyLinkedComputersTemp cl
 LEFT JOIN #LinkableComputersTemp lc
 ON lc.DesktopID = cl.DesktopID
 WHERE lc.LaptopID <> cl.LaptopID OR lc.LaptopID IS NULL
--SELECT * FROM #ComputersToUnlink ORDER BY DesktopID ASC
CREATE TABLE #ComputersToLink (
 DesktopID int,
 LaptopID int
)
INSERT INTO #ComputersToLink
 SELECT lc.DesktopID AS DesktopID, lc.LaptopID as LaptopID FROM #CurrentlyLinkedComputersTemp cl
 RIGHT JOIN #LinkableComputersTemp lc
 ON lc.DesktopID = cl.DesktopID
 WHERE lc.LaptopID <> cl.LaptopID OR cl.LaptopID IS NULL
--SELECT * FROM #ComputersToLink ORDER BY DesktopID ASC
-- Create variables
DECLARE
@CID int,
@ComputerID int,
@OrgChecksum int,
@StatusCode int,
@DisableAutoEditing bit,
@QuarantineOverride bit,
@QuarantineDate nvarchar (10),
@PurchaseDate nvarchar (10),
@PurchaseValue numeric (18,2),
@PurchaseCurrency nvarchar (10),
@InvoiceReference nvarchar (50),
@Notes nvarchar (1024),
@SecurityCode nvarchar (100),
@Vendor nvarchar (100),
@CustomFields xml,
@HostComputerID int,
@SRComputerID int,
@SocketCountManualOverrideEnabled bit,
@SocketCount tinyint,
@UpdatedBy nvarchar (100),
@ForceCustomFieldCrunch bit,
@ChipModulesPerProcessor tinyint,
@cmd varchar(max)
-- Unlink computers to be unlinked
DECLARE Commands CURSOR FOR SELECT 'ComputerInfoAddUpdate ' +
 '@CID =  N''' + convert(varchar,c.CID) + ''', ' +
 '@ComputerID =  N''' + convert(varchar,c.ComputerID) + ''', ' +
 '@OrgChecksum =  N''' + convert(varchar,ci.OrgChecksum) + ''', ' +
 '@StatusCode =  N''' + convert(varchar,ci.StatusCode) + ''', ' +
 '@DisableAutoEditing =  N''' + convert(varchar,ci.DisableAutoEditing) + ''', ' +
 '@QuarantineOverride =  N''' + convert(varchar,c.QuarantineOverride) + ''', ' +
 '@QuarantineDate = ' +
  CASE WHEN c.QuarantineDate IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,c.QuarantineDate, 121) + '''' END
  + ', ' +
 '@PurchaseDate = ' +
  CASE WHEN PurchaseDate IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,PurchaseDate, 121) + '''' END
  + ', ' +
 '@PurchaseValue = ' +
  CASE WHEN ci.PurchaseValue IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,ci.PurchaseValue) + '''' END
  + ', ' +
 '@PurchaseCurrency = ' +
  CASE WHEN PurchaseCurrency IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,PurchaseCurrency) + '''' END
  + ', ' +
 '@InvoiceReference = ' +
  CASE WHEN InvoiceReference IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,InvoiceReference) + '''' END
  + ', ' +
 '@Notes = ' +
  CASE WHEN ci.Notes IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,ci.Notes) + '''' END
  + ', ' +
 '@SecurityCode = ' +
  CASE WHEN SecurityCode IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,SecurityCode) + '''' END
  + ', ' +
 '@Vendor = ' +
  CASE WHEN Vendor IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,Vendor) + '''' END
  + ', ' +
 '@CustomFields = ' +
  CASE WHEN (SELECT [CustomFieldID] as id, [Value] as value, 1 AS [updated] FROM [SnowLicenseManager].[dbo].[tblCustomFieldValue] WHERE ElementID = @ComputerID FOR XML RAW ('field'), ROOT('customfields')) IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,(SELECT [CustomFieldID] as id, [Value] as value, 1 AS [updated] FROM [SnowLicenseManager].[dbo].[tblCustomFieldValue] WHERE ElementID = @ComputerID FOR XML RAW ('field'), ROOT('customfields'))) + '''' END
  + ', ' +
 '@HostComputerID = ' +
  CASE WHEN c.HostComputerID IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,c.HostComputerID) + '''' END
  + ', ' +
 '@SRComputerID =  NULL, ' +
 '@SocketCountManualOverrideEnabled = ' +
  CASE WHEN SocketCountManualOverrideEnabled IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,SocketCountManualOverrideEnabled) + '''' END
  + ', ' +
 '@SocketCount = ' +
  CASE WHEN SocketCount IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,SocketCount) + '''' END
  + ', ' +
 '@UpdatedBy =  N''Data Update Job Custom Step: auto-(un)assign sibling Computer' + ''', ' +
 '@ForceCustomFieldCrunch =  0 , ' +
 '@ChipModulesPerProcessor = ' +
  CASE WHEN ChipModulesPerProcessor IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,ChipModulesPerProcessor) + '''' END
 FROM tblComputer c
 INNER JOIN tblComputerInfo ci on ci.ComputerID = c.ComputerID
 INNER JOIN tblComputerHardware ch on ch.ComputerID = c.ComputerID
 INNER JOIN #ComputersToUnlink cu on cu.DesktopID = c.ComputerID

--exec ComputerInfoAddUpdate
OPEN Commands
FETCH NEXT FROM Commands INTO @cmd
WHILE @@FETCH_STATUS=0
BEGIN
 PRINT @cmd
 EXEC(@cmd)
 --PRINT @ReturValue
 FETCH NEXT FROM Commands INTO @cmd
END
CLOSE Commands
DEALLOCATE Commands
-- Link computers to be linked
DECLARE Commands CURSOR FOR SELECT 'ComputerInfoAddUpdate ' +
 '@CID =  N''' + convert(varchar,c.CID) + ''', ' +
 '@ComputerID =  N''' + convert(varchar,c.ComputerID) + ''', ' +
 '@OrgChecksum =  N''' + convert(varchar,ci.OrgChecksum) + ''', ' +
 '@StatusCode =  N''' + convert(varchar,ci.StatusCode) + ''', ' +
 '@DisableAutoEditing =  N''' + convert(varchar,ci.DisableAutoEditing) + ''', ' +
 '@QuarantineOverride =  N''' + convert(varchar,c.QuarantineOverride) + ''', ' +
 '@QuarantineDate = ' +
  CASE WHEN c.QuarantineDate IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,c.QuarantineDate, 121) + '''' END
  + ', ' +
 '@PurchaseDate = ' +
  CASE WHEN PurchaseDate IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,PurchaseDate, 121) + '''' END
  + ', ' +
 '@PurchaseValue = ' +
  CASE WHEN ci.PurchaseValue IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,ci.PurchaseValue) + '''' END
  + ', ' +
 '@PurchaseCurrency = ' +
  CASE WHEN PurchaseCurrency IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,PurchaseCurrency) + '''' END
  + ', ' +
 '@InvoiceReference = ' +
  CASE WHEN InvoiceReference IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,InvoiceReference) + '''' END
  + ', ' +
 '@Notes = ' +
  CASE WHEN ci.Notes IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,ci.Notes) + '''' END
  + ', ' +
 '@SecurityCode = ' +
  CASE WHEN SecurityCode IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,SecurityCode) + '''' END
  + ', ' +
 '@Vendor = ' +
  CASE WHEN Vendor IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,Vendor) + '''' END
  + ', ' +
 '@CustomFields = ' +
  CASE WHEN (SELECT [CustomFieldID] as id, [Value] as value, 1 AS [updated] FROM [SnowLicenseManager].[dbo].[tblCustomFieldValue] WHERE ElementID = @ComputerID FOR XML RAW ('field'), ROOT('customfields')) IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,(SELECT [CustomFieldID] as id, [Value] as value, 1 AS [updated] FROM [SnowLicenseManager].[dbo].[tblCustomFieldValue] WHERE ElementID = @ComputerID FOR XML RAW ('field'), ROOT('customfields'))) + '''' END
  + ', ' +
 '@HostComputerID = ' +
  CASE WHEN c.HostComputerID IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,c.HostComputerID) + '''' END
  + ', ' +
 '@SRComputerID = N''' + convert(varchar,cl.LaptopID) + ''', ' +
 '@SocketCountManualOverrideEnabled = ' +
  CASE WHEN SocketCountManualOverrideEnabled IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,SocketCountManualOverrideEnabled) + '''' END
  + ', ' +
 '@SocketCount = ' +
  CASE WHEN SocketCount IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,SocketCount) + '''' END
  + ', ' +
 '@UpdatedBy =  N''Data Update Job Custom Step: auto-(un)assign sibling Computer' + ''', ' +
 '@ForceCustomFieldCrunch =  0 , ' +
 '@ChipModulesPerProcessor = ' +
  CASE WHEN ChipModulesPerProcessor IS NULL THEN 'NULL'
  ELSE 'N''' + CONVERT(varchar,ChipModulesPerProcessor) + '''' END
 FROM tblComputer c
 INNER JOIN tblComputerInfo ci on ci.ComputerID = c.ComputerID
 INNER JOIN tblComputerHardware ch on ch.ComputerID = c.ComputerID
 INNER JOIN #ComputersToLink cl on cl.DesktopID = c.ComputerID

--exec ComputerInfoAddUpdate
OPEN Commands
FETCH NEXT FROM Commands INTO @cmd
WHILE @@FETCH_STATUS=0
BEGIN
 PRINT @cmd
 EXEC(@cmd)
 --PRINT @ReturValue
 FETCH NEXT FROM Commands INTO @cmd
END
CLOSE Commands
DEALLOCATE Commands

-- Cleanup temporary tables
Drop Table #CurrentlyLinkedComputersTemp
Drop Table #DesktopPrimaryUsersTemp
Drop Table #LaptopPrimaryUsersTemp
Drop Table #LinkableComputersTemp
Drop Table #CorrectlyLinkedComputersTemp
Drop Table #ComputersToUnlink
Drop Table #ComputersToLink
END
GO

Outcomes