AnsweredAssumed Answered

How to show FullName in License Query?

Question asked by Tim Read on Aug 10, 2011
Latest reply on Aug 11, 2011 by 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

]

Outcomes