topic Re: Help with Stats count expression?? in Splunk Search

Help with Stats count expression??

kfinn — Tue, 08 Dec 2020 00:26:08 GMT

Hi Everyone,

I'm newer-ish to splunk. I'm doing a search similar to this in splunk : index=mfa sourcetype=lexus Subcategory="Delivery Method".

With the search results, I want to do stats count by action, but It brings back results similar to this(see below), with each action having a different phone number. How do I get stats only on the wording "User selected text Deilvery"? and not having 1 stat for every phone number. There are 100 actions with the different phone numbers. I just want a count by User selected text delivery.

"User selected text delivery to ***-***-****"

I hope this makes sense. I'll gladly provide more info if needed. i'm just pretty new to this, and looking for some help.

Kevin

Re: Help with Stats count expression??

bowesmana — Tue, 08 Dec 2020 00:34:38 GMT

Use this

index=mfa sourcetype=lexus Subcategory="Delivery Method" | rex field=action mode=sed "s/(User selected text delivery).*/\1/"

It won't change your other actions.

There are other ways to achieve the same end, but this is an easy option. See rex command doc

https://docs.splunk.com/Documentation/Splunk/8.1.0/SearchReference/Rex

it's a useful command for extracting new fields from existing fields, but also in this case to replace text.

Another option would be to use | eval+replace - see the docs for that.

Re: Help with Stats count expression??

kfinn — Tue, 08 Dec 2020 01:29:57 GMT

I have done this, but it just brings back all the events, including other actions in that Subcategory, not just text.

Am I missing something?

index=mfa sourcetype=lexus Subcategory="Delivery Method"
| rex field=action mode=sed "s/(User selected text delivery).*/\1/"

Once I get this working. I can do stats count by action? or something else to get the count?

Re: Help with Stats count expression??

bowesmana — Tue, 08 Dec 2020 02:29:09 GMT

So when you say you want to 'count by action', it sounds you are only interested in one specific action right and want only to show text delivery actions within the subcategory "Delivery Method"?

In that case, just restrict the search for

action="User selected text delivery*"

and then just | stats count.

Or maybe I still don't understand what you want. If not, perhaps you can be a bit clearer on what data you have and what specific results you need to see.

Re: Help with Stats count expression??

kfinn — Tue, 08 Dec 2020 03:03:35 GMT

Thanks, that helps.

Sorry for not being more clear. Ultimately, within the Subcategory=Delivery Method. There are these 3 actions, that I'm trying to get "stats" on. Counts on. To search and then put into panel dashboard.

Wondering what the search would look like to search and get counts on all 3 actions. When I do a stats count by action, it includes the phone number or email address. I want counts of each, not a total of all 3. I hope this makes more sense. I'll gladly explain more if needed.

Again appreciate your assistance. Still trying to get better with this stuff.

User selected email delivery

User selected text delivery

User selected voice delivery

Re: Help with Stats count expression??

bowesmana — Tue, 08 Dec 2020 09:24:31 GMT

@kfinn

So what you want is this I expect

index=mfa sourcetype=lexus Subcategory="Delivery Method" | rex field=action "User selected (?<mode>\w+) delivery" | stats count by mode

The rex statement will extract a new field (mode) using the regular expression, which will be one of text, email or voice and then the by clause in stats will do the appropriate grouping.

Re: Help with Stats count expression??

kfinn — Tue, 08 Dec 2020 14:57:38 GMT

Thanks.

This seems to have worked quite well. One last question. There is one more action, "User choose to answer security questions"

| rex field=action "User choose to answer (?<mode>\w+)"

This picks up security. What in the expression do I need to add so it will pick up security questions as the action and show like that in the group by mode results?

Thanks again for the help. I'm learning quite a bit about this stuff.

Kevin

Re: Help with Stats count expression??

bowesmana — Wed, 09 Dec 2020 02:15:24 GMT

So, if your 4th action is as described, but you still want the delivery mechanism, then either of these two will work - using a different technique to demonstrate the are of the possible 🙂

index=mfa sourcetype=lexus Subcategory="Delivery Method" | rex field=action "(User choose to answer security|User selected) (?<mode>\w+) (delivery|questions)" | stats count by mode

OR this using an eval technique

index=mfa sourcetype=lexus Subcategory="Delivery Method" | rex field=action "User selected (?<mode>\w+) delivery" | eval mode=if(!isnull(mode), mode, if(match(action, "User choose to answer security questions"), "security", "unknown")) | stats count by mode

usage comes down to preference/your data and whether this will work well if your data changes. The above will set mode for the most common case, then test if it's not set and evaluate the new security question condition and return unknown if it does not match that.