Report for visual studio installs per user with computers

[njordur]

Created this report to figure out visual studio usage in my company per user (excluding disabled users). You can edit the where clause to change the software searches.

Edit by moderator: This report only works if the database is running on SQL server Express or higher edition.

Select Top 1000000 tblADusers.Displayname,
  tblADusers.Userdomain,
  tblAssets.Username,
  Max(tblSoftwareUni.softwareName) As Software,
  Max(tblSoftware.softwareVersion) As Version,
  Count(tblSoftwareUni.softwareName) As InstalledCount,
  Stuff((Select ';' + A.AssetName As [text()]
  From tblSoftware A1 Inner Join tblAssets A On A1.AssetID = A.AssetID
    Inner Join tblSoftwareUni B On A1.softID = B.SoftID Inner Join
    tblAssetCustom C On A.AssetID = C.AssetID Left Join tblADusers D
      On D.Username = A.Username And D.Userdomain = A.Userdomain
  Where (B.softwareName Like N'%microsoft visual studio%pro%' Or
      B.softwareName Like N'%microsoft visual studio%premium%' Or
      B.softwareName Like N'%microsoft visual studio%ultimate%' Or
      B.softwareName Like N'%microsoft visual studio%enterprise%') And
    A.Username = tblAssets.Username And C.State = 1 For Xml Path('')),
  1, 1, '') As Assets,
  SubString(SubString(tblADusers.OU, CharIndex(',', tblADusers.OU) - 250, 250),
  4, 250) As ShortUserOU
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
Where tblSoftwareUni.SoftwarePublisher Like N'%microsoft%' And
  tblAssetCustom.State = 1 And (tblSoftwareUni.softwareName Like
    N'%microsoft visual studio%pro%' Or tblSoftwareUni.softwareName Like
    N'%microsoft visual studio%premium%' Or tblSoftwareUni.softwareName Like
    N'%microsoft visual studio%ultimate%' Or tblSoftwareUni.softwareName Like
    N'%microsoft visual studio%enterprise%') And
  (SubString(SubString(tblADusers.OU, CharIndex(',', tblADusers.OU) - 250, 250),
    4, 250) Not Like '%disabled%' Or SubString(SubString(tblADusers.OU,
    CharIndex(',', tblADusers.OU) - 250, 250), 4, 250) Is Null)
Group By tblADusers.Displayname,
  tblADusers.Userdomain,
  tblAssets.Username,
  SubString(SubString(tblADusers.OU, CharIndex(',', tblADusers.OU) - 250, 250),
  4, 250)
Order By tblADusers.Displayname