How to autofill serial number from inventory

In this article you will learn how to autofill serial number from inventory. This will require permissions to the upKeeper database and some Microsoft SQL knowledge.

To accomplish this you have to perform this steps:

1. Verify that you have a valid backup of upKeeper database.
2. Run Microsoft SQL script inside Microsoft SQL Server Management Studio connected to the upKeeper database.
3. Execute inventory task on selected computer/computers.
4. View computer details to verify that serial number is updated.

Steps in more detail

1. Verify that you have a valid backup of upKeeper database or make a backup before next step. Verifying backup or making a backup is not covered in this article. If not familiar with Microsoft SQL, please contact your database administrator.
2. Open Microsoft SQL Server Management Studio.
3. Connect to database server were the upKeeper database is installed with a user that have the permissions to update tables.
4. Open a new query from the File menu or the toolbar.
5. Select the upKeeper database in the dropdown for available databases.
6. Copy the following script to the query window and execute. If execution failed, look thru description and try to find out what went wrong and take action.

ON InventoryHardware

AFTER INSERT AS

BEGIN

DECLARE @HistoryId int, @PropertyId int, @StringValue nvarchar(512)

 SELECT @HistoryId=HistoryId, @PropertyId=PropertyId, @StringValue=StringValue  FROM inserted

 If @PropertyId <> 34

BEGIN

Return

END

UPDATE Computers

SET SerialNumber=@StringValue

WHERE

Id = (select top 1 c.id from InventoryHistory ih join Computers c ON ih.ComputerId = c.Id WHERE ih.Id = @HistoryId)

END

GO 

To test the autofill function

1. Open upKeeper administration web and execute an instant inventory on a computer currently connected to upKeeper.
2. Open computer details on the computer you just executed instant inventory on and verify under inventory has been executed. To verify check last inventory time.
3. Open computer details (general) and check the serial number field to that the new trigger has updated the field.