Sibling computers: the tedious job made easy
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', , 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_S 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 --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 --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 --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 THEN '' ELSE 'N''' + CONVERT(varchar,c.QuarantineDate, 121) + '''' END + ', ' + '@PurchaseDate = ' + CASE WHEN PurchaseDate IS THEN '' ELSE 'N''' + CONVERT(varchar,PurchaseDate, 121) + '''' END + ', ' + '@PurchaseValue = ' + CASE WHEN ci.PurchaseValue IS THEN '' ELSE 'N''' + CONVERT(varchar,ci.PurchaseValue) + '''' END + ', ' + '@PurchaseCurrency = ' + CASE WHEN PurchaseCurrency IS THEN '' ELSE 'N''' + CONVERT(varchar,PurchaseCurrency) + '''' END + ', ' + '@InvoiceReference = ' + CASE WHEN InvoiceReference IS THEN '' ELSE 'N''' + CONVERT(varchar,InvoiceReference) + '''' END + ', ' + '@Notes = ' + CASE WHEN ci.Notes IS THEN '' ELSE 'N''' + CONVERT(varchar,ci.Notes) + '''' END + ', ' + '@SecurityCode = ' + CASE WHEN SecurityCode IS THEN '' ELSE 'N''' + CONVERT(varchar,SecurityCode) + '''' END + ', ' + '@Vendor = ' + CASE WHEN Vendor IS THEN '' 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 THEN '' 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 THEN '' ELSE 'N''' + CONVERT(varchar,c.HostComputerID) + '''' END + ', ' + '@SRComputerID = , ' + '@SocketCountManualOverrideEnabled = ' + CASE WHEN SocketCountManualOverrideEnabled IS THEN '' ELSE
Expand Post
