Feed Detail

September 21, 2018 at 7:00 AM

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

SamuelBaughan
7 years ago
Here is some updated code. Read the code carefully, understand it and test it before implementing , I'm not responsible for what happens if you decide to use it! The changes are: The code has been simplified to only update directly the SRComputerID (according to Oliver's suggestion) rather than calling the stock procedure The Primary Computer can now also be a Portable device (thanks to Oliver again ) Still to do: Take the CID into account Check the Disable Auto-editing setting before updating -- Creation of temporary tables CREATE TABLE #CurrentlyLinkedComputersTemp ( PrimaryComputerID int, SecondaryComputerID 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 #DesktopLaptopPrimaryUsersTemp ( UserID int, ComputerID int, LogonCount int ) INSERT INTO #DesktopLaptopPrimaryUsersTemp 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.IsVirtual = 0 --AND c1.IsPortable = 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 ( PrimaryComputerID int, SecondaryComputerID int ) INSERT INTO #LinkableComputersTemp SELECT dlp.ComputerID, lp.ComputerID FROM #DesktopLaptopPrimaryUsersTemp dlp INNER JOIN #LaptopPrimaryUsersTemp lp ON dlp.UserID = lp.UserID AND dlp.ComputerID <> lp.ComputerID --SELECT * FROM #LinkableComputersTemp ORDER BY DesktopID ASC CREATE TABLE #CorrectlyLinkedComputersTemp ( DesktopID int ) INSERT INTO #CorrectlyLinkedComputersTemp SELECT lc.PrimaryComputerID FROM #LinkableComputersTemp lc INNER JOIN #CurrentlyLinkedComputersTemp cl ON lc.PrimaryComputerID = cl.PrimaryComputerID AND lc.SecondaryComputerID = cl.SecondaryComputerID --SELECT * FROM #CorrectlyLinkedComputersTemp ORDER BY DesktopID ASC CREATE TABLE #ComputersToUnlink ( ComputerID int, ) INSERT INTO #ComputersToUnlink SELECT cl.PrimaryComputerID FROM #CurrentlyLinkedComputersTemp cl LEFT JOIN #LinkableComputersTemp lc ON lc.PrimaryComputerID = cl.PrimaryComputerID WHERE lc.SecondaryComputerID <> cl.SecondaryComputerID OR lc.SecondaryComputerID IS UNION SELECT cl.SecondaryComputerID FROM #CurrentlyLinkedComputersTemp cl LEFT JOIN #LinkableComputersTemp lc ON lc.PrimaryComputerID = cl.PrimaryComputerID WHERE lc.SecondaryComputerID <> cl.SecondaryComputerID OR lc.SecondaryComputerID IS --SELECT * FROM #ComputersToUnlink ORDER BY ComputerID ASC CREATE TABLE #ComputersToLink ( PrimaryComputerID int, SecondaryComputerID int ) INSERT INTO #ComputersToLink SELECT lc.PrimaryComputerID AS PrimaryComputerID, lc.SecondaryComputerID as SecondaryComputerID FROM #CurrentlyLinkedComputersTemp cl RIGHT JOIN #LinkableComputersTemp lc ON lc.PrimaryComputerID = cl.PrimaryComputerID WHERE lc.SecondaryComputerID <> cl.SecondaryComputerID OR cl.SecondaryComputerID IS UNION SELECT lc.SecondaryComputerID AS PrimaryComputerID, lc.PrimaryComputerID as SecondaryComputerID FROM #CurrentlyLinkedComputersTemp cl RIGHT JOIN #LinkableComputersTemp lc ON lc.PrimaryComputerID = cl.PrimaryComputerID WHERE lc.SecondaryComputerID <> cl.SecondaryComputerID OR cl.SecondaryComputerID IS --SELECT * FROM #ComputersToLink ORDER BY PrimaryComputerID ASC -- Create variables DECLARE @cmd varchar(max) -- Unlink computers to be unlinked DECLARE Commands CURSOR FOR SELECT 'UPDATE tblComputer SET SRComputerID = WHERE ComputerID = N''' + convert(varchar, ul.ComputerID) + '''' FROM #ComputersToUnlink ul -- Update SLM table OPEN Commands FETCH NEXT FROM Commands INTO @cmd WHILE @@FETCH_STATUS=0 BEGIN --PRINT @cmd EXEC(@cmd) FETCH NEXT FROM Commands INTO @cmd END CLOSE Commands DEALLOCATE Commands -- Link computers to be linked DECLARE Commands CURSOR FOR SELECT 'UPDATE tblComputer SET SRComputerID = N''' + convert(varchar, + l.SecondaryComputerID) + ' WHERE ComputerID = N''' + convert(varchar, l.PrimaryComputerID) + '''' FROM #ComputersToLink l -- Update SLM table OPEN Commands FETCH NEXT FROM Commands INTO @cmd WHILE @@FETCH_STATUS=0 BEGIN --PRINT @cmd EXEC(@cmd) FETCH NEXT FROM Commands INTO @cmd END CLOSE Commands DEALLOCATE Commands -- Cleanup temporary tables Drop Table #CurrentlyLinkedComputersTemp Drop Table #DesktopLaptopPrimaryUsersTemp Drop Table #LaptopPrimaryUsersTemp Drop Table #LinkableComputersTemp Drop Table #CorrectlyLinkedComputersTemp Drop Table #ComputersToUnlink Drop Table #ComputersToLink
Expand Post
Oliver Berger (Flexera Software)
8 years ago
Hi ?, nice work - I wished someone had published something similar including the procedure "ComputerSuggestedSiblingsList", which is part of SLM... Of course, this procedure itself is closed source, so completely inflexible, and the documentation is "hard to find", even for Snow Employees... Comming to your script: In theory, you could sibble two portable computers. You just need to have the main user the same. You are not filtering with the CID, what makes the script only usable for Customers on Enterprise Edition. You could just update the SRComputerID in the tblcomputerinfo - any change through an update to the procedure you use, will break your script. Cheers, Oliver
Expand Post
- SamuelBaughan
  8 years ago
  Hi ‌ Indeed I had never heard about this Stored Procedure which looks very interesting! I was also wondering if the results of the function are somehow accessible from SLM. I played a bit with it since, as you mention, there is little documentation about it. I found a few issues with using this stock stored procedure though: As you say, there is no documentation so I cannot know what Snow bases its suggestions on so I can confirm it or deny it Related to my first bullet point, I see that some sibling computers proposed by the Snow-provided Stored Procedure are used by several users ... technically, this is only possible if none but the primary user uses the applications that benefit from secondary use rights (or if all the other users of that same device are also covered, being primary users of a primary device ... but that would become very complicated to code and long to run). I'm not sure if Snow checks that. For this reason I limited my script to select only portable devices that have only one active user. Some computers proposed by the Stored Procedure as a base for a sibling portable computer are actually Virtual Machines from a virtual desktop infrastructure. This would normally not be possible since those would initially be covered by the accessing device, not by a real assigned license. Now, the down-sides of the Stored Procedure I came up with: I should probably have included a filter on the CID. We use only one so I didn't even think about it I leave that to a SQL fan to implement and share Also, you are completely right, I didn't realize that a portable device is a valid base for another portable sibling. This would complicate the script a tiny bit but shouldn't be unachievable. I might add that in the near future. As you also rightly mention, any change in the structure of Snow would potentially break my stored procedure. I thought of simply updating the SRComputerID in the tblComputerInfo table but I was not aware if this would be enough. Since Snow already provide a Stored Procedure to update computers I thought it would be good to use it instead but if you say that there are no consequences in shortcutting this step, it would make things much simpler! If Snow eventually provides more documentation on the stock function, it might be wise to use it in the DUJ rather than mine Thanks again for your invaluable input!
  Expand Post
  - SamuelBaughan
    8 years ago
    Additionally, the script I wrote does not take the DisableAutoEditing flag into account. I will add that too when I have some time
    Expand Post