Splunk Search

replace values within a chart command

bsstewart
Explorer

I have a search that looks at a index that has the user and a single group they belong to as shown below: I then use a chart command [index=myindexname sourcetype=sourcetype Group="pw-pro*" |chart count by User Group limit=0] to give me a two dimensional chart as shown below: by using the count parameter I either get a value of zero or one since all names are unique. zero represents that the user is not part of that group and a count of one means they are present in the group. so here's the question for readability I want to replace the zero with either a "N" or Space and the "1's with "Y" nothing I have tried works. I tried using a eval with a if command and or a case command and I even tried using a replace command none of them worked. How do I accomplish this?

DATA:
USER-1; PW-PRO-ROLE2
USER-1; PW-PRO-ROLE5
USER-2; PW-PRO-ROLE3
USER-3; PW-PRO-ROLE1
USER-4; PW-PRO-ROLE3
USER-4; PW-PRO-ROLE4
USER-5; PW-PRO-ROLE2
USER-6; PW-PRO-ROLE2
USER-6; PW-PRO-ROLE4

SEARCH/CHART Results
User PW-PRO-ROLE1 PW-PRO-ROLE2 PW-PRO-ROLE3 PW-PRO-ROLE4 PW-PRO-ROLE5
USER-1 0 1 0 0 1
USER-2 0 0 1 0 0
USER-3 1 0 0 0 0
USER-4 0 0 1 1 0
USER-5 0 1 0 0 0
USER-6 0 1 0 1 0

Tags (2)
0 Karma
1 Solution

niketnilay
Legend

@bsstewart, please try the following

index=myindexname sourcetype=sourcetype Group="pw-pro*" 
| stats count by  User Group
| eval Flag=case(count=1,"Yes")
| chart values(Flag) by User Group limit=0
| fillnull value="No"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

somesoni2
Revered Legend

Try these

index=myindexname sourcetype=sourcetype Group="pw-pro*" 
|chart count by User Group limit=0
| replace "0" with "N" "1" with "Y" in PW*

OR (if above doesn't work, use the foreach as-is, <<FIELD>> has to be use literally the way specified below)

index=myindexname sourcetype=sourcetype Group="pw-pro*" 
|chart count by User Group limit=0
| foreach PW* [| eval "<<FIELD>>"=if('<<FIELD>>'=0,"N","Y")]
0 Karma

niketnilay
Legend

@bsstewart, please try the following

index=myindexname sourcetype=sourcetype Group="pw-pro*" 
| stats count by  User Group
| eval Flag=case(count=1,"Yes")
| chart values(Flag) by User Group limit=0
| fillnull value="No"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

bsstewart
Explorer

I tried your answer the result is the same still has 0's and 1's ?

0 Karma

niketnilay
Legend

@bsstewart, sorry I had missed values(Flag) instead of replacing count. Can you please try again?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

bsstewart
Explorer

Thank you it worked! I appreciate the quick answer:)

0 Karma

niketnilay
Legend

@bsstewart Do accept the answer if your issue is resolved!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma