Mass update of assets via script,api or SCCM assignment?

[Kal451]

so we're in the process of getting Lansweeper and the helpdesk going. One thing we'd like is for Assets to tie to users wherever possible. However the idea of doing this by hand is not appealing.

Is there a way to do it to pull from a CSV or perhaps match up with how config manager has its user assignments lined up?

[Kal451]

Interesting idea, however its not solid really as for example what if I (or another IT admin) the last logged in user? far from an unexpected circumstance.

But it does give me a thought. I've been looking at setting up login auditing in some form, if I put something together that take a count of the users and make the most common user the "default" then run what you have here periodically to update that.... still runs into the problem of "if X user logs in more for y reason" then the relationship wouldnt match up with "this kit was issued UserA and they are responsible for it" kinda thing....


hummmm

[prighi61]

Hi Kal451,

if your need is to create a solid relationship between the asset and its last user you can benefit from this SQL script:

Set IDENTITY_INSERT [lansweeperdb].[dbo].[tblAssetUserRelations] ON
GO

DECLARE @offset integer;
set @offset = (Select Max(RelationID) + 1 from [lansweeperdb].[dbo].[tblAssetUserRelations]);

insert into [lansweeperdb].[dbo].[tblAssetUserRelations] (
[RelationID],[Username] ,[Userdomain] ,[AssetID] ,[Type] ,[Comments] ,[Lastchanged] ,[StartDate] ,[EndDate] ,[Ou] ,[AdObjectId] ,[IsLocalUser])
select ROW_NUMBER() OVER(PARTITION BY NULL ORDER BY AssetID) + @offset as RelationID,
a.Username,
a.Userdomain,
a.[AssetID],
12 as Type, -- UsedBy see tsysAssetRelationTypes table
null as Comments,
GETDATE() as Lastchanged,
null as StartDate,
null as EndDate,
null as Ou,
null as AdObjectId,
case a.FQDN
when null then 1
else 0
end as IsLocalUser
FROM [lansweeperdb].[dbo].[tblAssets] a
Where Username > ''

Set IDENTITY_INSERT [lansweeperdb].[dbo].[tblAssetUserRelations] OFF
GO