getting stats from a query where match is found.

[surekhasplunk]

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

[elliotproebstel · Accepted solution]

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" ]

[surekhasplunk]

Hi @elliotproebstel

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

[elliotproebstel]

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.

[elliotproebstel]

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...

[micahkemp]

Don't forget to accept the answer if you consider it correct and helpful.

[elliotproebstel]

Glad to help!