Automatic Linking of Assets

[brodiemac2]

We are starting to inventory iPhones that are being imported from Intune. Each phone shows a valid AD user but when you click the link for the user, the device is not listed on the user page. Is there a way to create that asset relation automatically?

[njordur]

I also created a procedure to be run in the same schedule to be able to update the IMEI number from Intune to stick it to tblassetCustom. This was later used to find duplicates assets for example and those who were created manually before intune.

    create proc [dbo].[UpdateIntuneIMEI_On_Asset] as
    update tblassetcustom 
        set custom2 = intune.imei
    FROM [tblIntuneDevice] intune
    inner join tblassetcustom ass on ass.AssetID = intune.AssetId        
    where intune.imei <> '' and intune.imei not like '*%'
    and ass.Custom2 = ''

[njordur]

brodiemac-too wrote:
We are starting to inventory iPhones that are being imported from Intune. Each phone shows a valid AD user but when you click the link for the user, the device is not listed on the user page. Is there a way to create that asset relation automatically?

I'm running this procedure on a sql agent job peridocally to fill this asset relations from intune in. This is only the insert part. Not handling updates.

create proc [dbo].[InsertIntune_User_Relations] as
  declare @domain as varchar(20)
  set @domain = '<YourDomain>'
  ;with missing_intune_users_cte as (
  select         
        intune.UserDisplayName as Intune_Displayname
        ,intune.UserPrincipalName as Intune_UPN
        ,SUBSTRING(intune.UserPrincipalName,1,CHARINDEX('@',intune.UserPrincipalName)-1) as Intune_Samaccountname
        ,intune.UserId as Intune_Userid
        ,intune.AssetId as Intune_Assetid
        ,intune.EnrolledDateTime as Intune_EnrollDateTime
      ,assr.[Username]
      ,assr.[Userdomain]
      ,assr.[AssetID]
      ,assr.[Type] --Type = 12
      ,assr.[StartDate]  
  from [tblIntuneDevice] intune
  left outer join [tblAssetUserRelations] assr on assr.AssetID = intune.assetid
  where assr.Username is null and intune.UserPrincipalName <> ''
  )
  insert into [tblAssetUserRelations] (Username,Userdomain,AssetID,[Type],[StartDate],Comments)
  select Intune_Samaccountname,@domain,Intune_Assetid,12,Intune_EnrollDateTime,'Imported from Intune' from missing_intune_users_cte

[dhardyuk]

Hi there,

Before I make a dumb mistake 😉 please can you clarify:

@domain= '<YourDomain>

 We have the netbios domain name which is probably the same as the Userdomain in your code snippet and the email domain which in our infrastructure is the same as the domain portion of our UPNs.

So for us our UPNs match our email addresses.

I'm leaning towards this being the email domain after the @ in our UPNs and email addresses .........

Thanks

David

[brodiemac2]

I posed this question to support and they tell me it's a feature not yet built into LANSweeper.