List of workloads with users & profile

Viewpoint
Fan

List of workloads with users & profile

Hi ,

How can I retrieve the list of all users and their profiles alongwith their workloads? I can get the list of users and profile simply from DBC.users and list of rulenames from the TDWM.RuleDefs. but could not figure out to join which column with whom to get list of workloads which are associated with certain users & profiles. I am working in TD 14.10 and we r using viewpoint 15.0

2 REPLIES
Enthusiast

Re: List of workloads with users & profile

Nomi,

Wdname is found on pdcrdata.dbqlogtbl_hst so you can associate it with users  there for users that have submitted queries.

Also they may be able to run in multiple workloads but have only qualified for some so you would check TASM ruleset to see what workloads the user qualifies for based on classification criteria i.e: userid/account/profile/querband. This would apply for users who have not submitted any queries also.

RGlass

Fan

Re: List of workloads with users & profile

Thanks RGlass. I have made the solution and following is the query to extract result set. Just posting it in case someone also needs it in future. 

sel c.username,c.profilename,a.rulename

from tdwm.ruleDefs as a

inner join tdwm.rulequalifycriteria as b

on a.ruleid = b.ruleid

and a.configid = b.configid

and a.removedate = 0

inner join  dbc.users as c

on b.charvalue = c.profilename

where b.criterionType = 'PROFIL'

group by 1,2,3

nomi.