KACumiskey asked a question.
I need to know how to link the calculated user to an asset name in an SQL query.
I cannot find the correct tables to use to link everything together.
I cannot find the correct tables to use to link everything together.
Given an asset serial number or asset name, we need to know the calculated user attached to that serial number via SQL.
@KACumiskey
You can try with something along the lines of this (FNMP is the name of the Compliance DB and the AssetName and SerialNumber in the WHERE clause obviously need to be substituted with relevant data):
SELECT [ShortDescription] AS [AssetName]
,[SerialNumber]
,[FNMP].[dbo].[ComplianceComputer].[ComputerName]
,[FNMP].[dbo].[ComplianceUser].[UserName]
,[FNMP].[dbo].[ComplianceUser].[SAMAccountName]
FROM [FNMP].[dbo].[Asset]
LEFT JOIN [FNMP].[dbo].[ComplianceComputer] ON [FNMP].[dbo].[Asset].[AssetID] = [FNMP].[dbo].[ComplianceComputer].[AssetID]
LEFT JOIN [FNMP].[dbo].[ComplianceUser] ON [FNMP].[dbo].[ComplianceComputer].[CalculatedUserID] = [FNMP].[dbo].[ComplianceUser].[ComplianceUserID]
WHERE [FNMP].[dbo].[Asset].[ShortDescription] IN ('Computer00890','Computer005227') OR [FNMP].[dbo].[Asset].[SerialNumber] IN ('Dell765878','Lenovo684884')
Thanks,
John Sorensen
The calculated user ([CalculatedUserID]) is actually linked to the Inventory ([ComplianceComputer]), not to the Asset ([Asset]).
In case your Asset is linked to an Inventory, you find the calculated user by JOINing the following tables starting from the Asset:
[Asset] - [ComplianceComputer]
JOIN on ([Asset].[AssetID] = [ComplianceComputer].[AssetID])
[ComplianceComputer] - [ComplianceUser]
JOIN on ([ComplianceComputer].[CalculatedUserID] = [ComplianceUser].[ComplianceUserID])