AnsweredAssumed Answered

SQL Multiple Users on Device

Question asked by q155344 on May 2, 2019
Latest reply on May 2, 2019 by Laim McKenzie

I am trying to run some SQL reports that will give me the full name of the user and the host name of the device for all devices in our environment. The problem that I am running into is that there are multiple users showing for each device and each user could have multiple devices. I am fairly skilled in SQL but because of there being duplicates in multiple columns  grouping by any of these would lead to an ambiguous error. Is there a Primary user table in SIM or SLM database? Has anyone else tried to achieve this? Any input on this would be much appreciated. 

The query I started on is posted below. I would like to remove the duplicates and just get one computer and one primary user. 

select c.HostName, l.FullName, x.Name, l.UserId from SnowLicenseManager.dbo.tblUserInfo l with (nolock)
inner join SnowLicenseManager.dbo.tblComputerUsers k on l.UserID = k.UserID
inner join SnowLicenseManager.dbo.tblComputer c on k.ComputerID = c.ComputerID
inner join SnowLicenseManager.dbo.tblComputerApplications a on c.ComputerID =a.ComputerID
inner join SnowLicenseManager.dbo.tblApplication x on a.ApplicationID = x.ApplicationID
where FullName is not null and x.Name = 'Adobe Acrobat DC Professional'