In this article you will learn how to add serial number from inventory. This will require permissions to the upKeeper database and some Microsoft SQL knowledge.
This script can be executed manually or added to some scheduled action executing SQL script. Only computers missing serial number in upKeeper Manager will be updated if serial number information exists in inventory information.
Script can be modified to only affect single organization or single computer by uncomment and edit specific rows. This row can also be modified to affect a number of organizations or computers
/*
This script to adds serialnumber to computers from inventory to where missing.
*/
USE upkeeper;
DECLARE @PropertyId integer;
SET @PropertyId = 34; -- 34 = SerialNumber
UPDATE Computers
SET SerialNumber = computerInvent.SerialNumber
FROM
(SELECT
c.Id
,invent.SerialNumber
FROM Computers c
JOIN (SELECT MAX(ihist.id) HistoryId, c.Id, MAX(ihw.StringValue) SerialNumber FROM InventoryHistory ihist
JOIN Computers c
ON ihist.ComputerId = c.Id
JOIN InventoryHardware ihw
ON ihist.Id = ihw.HistoryId
WHERE
ihist.InventoryType = 1
AND c.Deleted = 0
AND ihw.PropertyId = @PropertyId
GROUP BY c.Id) invent
ON c.Id = invent.Id
WHERE
c.Deleted = 0
AND
c.SerialNumber IS NULL
-- and OrganizationId = '7B27BE7B-817C-414A-8A03-9811A8305715' -- only specific organization
-- and c.Id = '899D24B4-5DC9-4A95-BFAC-649F017CD25D' -- only specific computer
) computerInvent
WHERE Computers.Id = computerInvent.Id
Remember to make a backup of your database before implementing and verify result.
Comments
0 comments
Please sign in to leave a comment.