3 Replies Latest reply on Aug 11, 2011 2:44 AM by Tim Read

    How to show FullName in License Query?

    Tim Read

      Based on reports found on this forum I am hoping to show the FullName of all users using EPDM licenses but I cannot figure it out. The ConisioMasterDb only has a table showing the username so it needs to be 'joined' with the Users table from the Vault databases. I can do this within one database but adding the complexity of a second database has put this out of my reach.

       

      Any help will be greatly appreciated. The query is below.

       

      §Sql
      [

      SELECT DISTINCT liu.username AS 'Username', liu.hostname AS 'Computer', gliu.DatabaseName,
      CASE
      WHEN gliu.ProgramType=0 THEN 'CAD Editor'
      WHEN gliu.ProgramType=1 THEN 'Contributor'
      WHEN gliu.ProgramType=2 THEN 'Viewer'
      END AS LicenseType
      FROM
      ConisioMasterDb.dbo.LoggedInUsers liu, ConisioMasterDb.dbo.LoggedInUsers gliu, master..sysprocesses sp
      WHERE
      liu.spid = gliu.spid
      AND liu.spid = sp.spid
      AND upper(sp.program_name) LIKE upper('%EdmServ%')

      UNION ALL

      SELECT liu.username AS 'Username', liu.hostname AS 'Computer', gliu.DatabaseName, 'Web' AS LicenseType
      FROM
      ConisioMasterDb.dbo.LoggedInUsers liu, ConisioMasterDb.dbo.LoggedInUsers gliu, master..sysprocesses sp
      WHERE
      liu.spid = gliu.spid
      AND liu.spid = sp.spid
      AND upper(sp.program_name) LIKE upper('%ConisioWebServer%')
      AND gliu.ProgramType=3
      ORDER BY liu.username

      ]