All Apps and Add-ons

Displaying the 'count' data in a column

vasuparvatham
New Member
  <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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The 'OtherRoles' field is in your table command, but not anywhere else. table cannot display a field that has not been defined or extracted. Perhaps you need to add to your SQL query?

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...