<table>
<title>eCW User Roles</title>
<search>
<query>|dbxquery connection=eCW_Mobiledoc_Production shortnames=t maxrows=5000000 query="Use mobiledoc_repl
select
u.region_id,
u.uid,
u.uname,
u.ufname,
u.ulname,
CASE
WHEN u.UserType='1' THEN 'Provider'
WHEN u.UserType='2' THEN 'Staff'
END as [UserType],
sg.GroupName as [Role Name],
c.[Count of Roles]
from users u --Users Table
join grouppermissions gp on u.uid=gp.userid and gp.permission=1 and u.Region_ID = gp.Region_ID --User to Role Crosswalk
join securitygroup sg on gp.groupid=sg.groupid and sg.Region_ID = gp.Region_ID --Role Table
join (select userid,count(groupid) as [Count of Roles] from grouppermissions where permission=1 group by userid) c on gp.UserId=c.UserId --Count Roles to User
where
u.delFlag=0 and --Not Deleted User
u.status=0 and --Active User
c.[Count of Roles] > 1 --More than 1 Role
order by Region_ID, u.uname
" | table region_id, uname, ulname, ufname, UserType, "Role Name", "Count of Roles", OtherRoles | search "Role Name" = "$roles$" AND region_id = "$region$"</query>
<earliest>-4h@m</earliest>
<latest>now</latest>
</search>
<option name="count">50</option>
<option name="drilldown">none</option>
<option name="refresh.display">progressbar</option>
</table>
This is my Splunk query. I have newly added a column called "OtherRoles" which is supposed to print the roles assigned to user. Count of Roles is displaying the number of roles assigned to an user.
Please help me thanks very much.
