Splunk Search

How to count different occurrences of a field having values with a common pattern?

gaurav0810
New Member

Hi,

I have an interface where user can search based on various criteria like email phone number , order number etc. I am logging the string user is searching now i have to find the count for different type of searches. I am using below query to search.

index=abc_core sourcetype=ABC_svc_log "SERVICE_NAME=MY_SERVICE" "OPERATION=SEARCH" SEARCH_CRITERIA USER_ID|stats count(SEARCH_CRITERIA) as Total_search, count(eval(SEARCH_CRITERIA  like "%@%.%")) as email_Search, count(eval(isInt(SEARCH_CRITERIA))) as Phone_Number_Search, count(eval(SEARCH_CRITERIA like "W%")) as Weborder_Search, count(eval(SEARCH_CRITERIA  like "SC%")) as SavedCart_Search by USER_ID | eval Name_Search= Total_search-(Weborder_Search+ SavedCart_Search+ email_Search+ Phone_Number_Search)

While I am searching for any count, I am using like above with eval, but this is not correct and one event can belong to multiple types of searches with this. If order starts with W and a name also starts with W, then they both will count the occurrences. I have a specific pattern for order (W12344566) and cart (SC1234567) and I need to know how can I achieve this.

Any help is highly appreciated.

Thanks,
Gaurav

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

Try this -

| stats count(SEARCH_CRITERIA) as Total_search, 
  count(eval(SEARCH_CRITERIA  like "%@%.%")) as email_Search, 
  count(eval(isInt(SEARCH_CRITERIA))) as Phone_Number_Search, 
  count(eval(match(SEARCH_CRITERIA, "W\d+"))) as Weborder_Search, 
  count(eval(SEARCH_CRITERIA  like "SC%")) as SavedCart_Search,
  count(eval(match(SEARCH_CRITERIA, "^[^0-9@]*$"))) as Name_Search 
     by USER_ID

It's not ALL of your answer, but it should get you started and should give you all the patterns you need. I'll explain so you can adapt it a bit as required (or, comment back with a bit more information about exactly what you need and we can help more specifically).

I added/changed two things. On Weborder_Search, you'll see I use count(eval(match(SEARCH_CRITERIA, "W\d+"))). That lets me use a regex, in this case W\d+. That regex means a literal W character followed by one or more + digits \d. If you need to capture either capital or lowercase W, you could use [wW]\d+.

You'll see the other one that's totally different is a new one, a "Name_Search", count(eval(match(SEARCH_CRITERIA, "^[^0-9@]*$"))) as Name_Search. It is much like the above but has a very different regex. Ignore the first ^ and last $ for now, the stuff that's in between is ...

[^0-9@]* don't match numbers (0-9) or the at sign @.

Around that is wrapped
^...$, which means to match at the start and end of the string. Yes, the carrot/top-hat has two meanings depending on where it is.

All together, that means it'll match william but won't match w737362 nor william@here.com because it only matches where the entire string - from beginning to end - doesn't have a number or at sign. This will get you a "name" search count, which you mention but don't list.

I think those two may help you do all you need but if you have further problems, paste in some sample data that's not behaving and what you are trying and we can probably help!

View solution in original post

Richfez
SplunkTrust
SplunkTrust

Try this -

| stats count(SEARCH_CRITERIA) as Total_search, 
  count(eval(SEARCH_CRITERIA  like "%@%.%")) as email_Search, 
  count(eval(isInt(SEARCH_CRITERIA))) as Phone_Number_Search, 
  count(eval(match(SEARCH_CRITERIA, "W\d+"))) as Weborder_Search, 
  count(eval(SEARCH_CRITERIA  like "SC%")) as SavedCart_Search,
  count(eval(match(SEARCH_CRITERIA, "^[^0-9@]*$"))) as Name_Search 
     by USER_ID

It's not ALL of your answer, but it should get you started and should give you all the patterns you need. I'll explain so you can adapt it a bit as required (or, comment back with a bit more information about exactly what you need and we can help more specifically).

I added/changed two things. On Weborder_Search, you'll see I use count(eval(match(SEARCH_CRITERIA, "W\d+"))). That lets me use a regex, in this case W\d+. That regex means a literal W character followed by one or more + digits \d. If you need to capture either capital or lowercase W, you could use [wW]\d+.

You'll see the other one that's totally different is a new one, a "Name_Search", count(eval(match(SEARCH_CRITERIA, "^[^0-9@]*$"))) as Name_Search. It is much like the above but has a very different regex. Ignore the first ^ and last $ for now, the stuff that's in between is ...

[^0-9@]* don't match numbers (0-9) or the at sign @.

Around that is wrapped
^...$, which means to match at the start and end of the string. Yes, the carrot/top-hat has two meanings depending on where it is.

All together, that means it'll match william but won't match w737362 nor william@here.com because it only matches where the entire string - from beginning to end - doesn't have a number or at sign. This will get you a "name" search count, which you mention but don't list.

I think those two may help you do all you need but if you have further problems, paste in some sample data that's not behaving and what you are trying and we can probably help!

gaurav0810
New Member

Thanks rich7177!

This is what i was looking for. Thanks for your quick help.

🙂

Gaurav

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