Lync Client Discovery

lyncClients.sql

declare @user varchar(20)
set @user = ''

declare @ClientVer varchar(100)
set @ClientVer = ''

declare @server varchar(10)


Select (cast (RE.ClientApp as varchar (100))) as ClientVersion, R.UserAtHost as UserName, Reg.Fqdn 
From [LYNC1\RTCLOCAL].rtcdyn.dbo.RegistrarEndpoint RE
Inner Join [LYNC1\RTCLOCAL].rtc.dbo.Resource R 
      on R.ResourceId = RE.OwnerId 
Inner Join [LYNC1\RTCLOCAL].rtcdyn.dbo.Registrar Reg 
      on Reg.RegistrarId = RE.PrimaryRegistrarClusterId 
where       R.UserAtHost like '%' + @user + '%'
and (cast (RE.ClientApp as varchar (100))) like '%' + @ClientVer + '%'

union all

Select (cast (RE.ClientApp as varchar (100))) as ClientVersion, R.UserAtHost as UserName, Reg.Fqdn
From [LYNC2\RTCLOCAL].rtcdyn.dbo.RegistrarEndpoint RE
Inner Join [LYNC2\RTCLOCAL].rtc.dbo.Resource R 
      on R.ResourceId = RE.OwnerId 
Inner Join [LYNC2\RTCLOCAL].rtcdyn.dbo.Registrar Reg 
      on Reg.RegistrarId = RE.PrimaryRegistrarClusterId 
where       R.UserAtHost like '%' + @user + '%'
and (cast (RE.ClientApp as varchar (100))) like '%' + @ClientVer + '%'

union all

Select (cast (RE.ClientApp as varchar (100))) as ClientVersion, R.UserAtHost as UserName, Reg.Fqdn
From [LYNC3\RTCLOCAL].rtcdyn.dbo.RegistrarEndpoint RE
Inner Join [LYNC3\RTCLOCAL].rtc.dbo.Resource R 
      on R.ResourceId = RE.OwnerId 
Inner Join [LYNC3\RTCLOCAL].rtcdyn.dbo.Registrar Reg 
      on Reg.RegistrarId = RE.PrimaryRegistrarClusterId 
where       R.UserAtHost like '%' + @user + '%'
and (cast (RE.ClientApp as varchar (100))) like '%' + @ClientVer + '%'