Hi,
| rest /services/authentication/users splunk_server=local | search [| rest /services/authentication/current-context | rename username as title | fields title]|fields title |map [search index=summary |search "requester_name"=Tom | stats count by "Delivery Area"]
I am using above query which is giving me result count 3 which is correct as i have 3 rows for the delivery area to which the requester name Tom belong to.
But my requirement is now i want to get the total count 10 which is the total count of rows the particular "Delivery Area" has.
The "Delivery Area" name say suppose Cloud_platform has a total of 10 rows in the summary index.
So when i login as user Tom since i belong to Cloud_platform can i just take the delivery area name corresponding to my name but get count of all the persons in the delivery area.
thanks
If I'm understanding your requirements correctly, you want to change this part of your search
| map
[ search index=summary
| search "requester_name"=$title$
| stats count by "Delivery Area"]
to use some logic that finds the value of "Delivery Area"
for the user with requester_name=$title$
and then counts all events with that "Delivery Area"
. If so, this should do the trick:
| map
[ search index=summary
| eval relevant_area=if("requester_name"="$title$", 'Delivery Area', NULL)
| eventstats max(relevant_area) AS relevant_area
| stats count(eval(relevant_area='Delivery Area')) AS count BY "Delivery Area" ]
If I'm understanding your requirements correctly, you want to change this part of your search
| map
[ search index=summary
| search "requester_name"=$title$
| stats count by "Delivery Area"]
to use some logic that finds the value of "Delivery Area"
for the user with requester_name=$title$
and then counts all events with that "Delivery Area"
. If so, this should do the trick:
| map
[ search index=summary
| eval relevant_area=if("requester_name"="$title$", 'Delivery Area', NULL)
| eventstats max(relevant_area) AS relevant_area
| stats count(eval(relevant_area='Delivery Area')) AS count BY "Delivery Area" ]
Thanks got it working ...but i have a problem with pattern matching can you please help me with that.
| eval relevant_area=if(match(lower('requester_name'),"%$title$%"), 'Delivery Area', NULL)
| eventstats max(relevant_area) AS relevant_area | stats count(eval(relevant_area='Delivery Area')) AS count BY "Delivery Area"
because the requester_name looks like this Tom Williams but my variable title has got value like this tom
Sure thing. I think it will work if you replace match
with like
:
| eval relevant_area=if(like(lower(requester_name),"%$title$%"), 'Delivery Area', NULL)
Note: It makes no difference to include single quotes around a field name that does not contain spaces, so I removed them from around requester_name
.
FYI, the match
function uses regex to compare the first and second options.
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/ConditionalFunctions#match.28...
The like
function performs pattern matching and uses the %
character as a wildcard.
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/ConditionalFunctions#like.28T...
Don't forget to accept the answer if you consider it correct and helpful.
Glad to help!