Splunk Search

Search and count by multiple specific substrings in a field

chimuru84
Explorer

Hello. 

I have logs which contains field "matching" which is a String type. This field contains this kind of information: [firstName, lastName, mobileNumber, town, ipAddress, dateOfBirth, emailAddress, countryCode, fullAddress, postCode, etc]. What I want to do is to compose a query that will return count of a specific search, such as [mobileNumber, countryCode] and display only the fields that contain the above words.

I tried this query:

index="source*" | where matching LIKE "%mobileNumber%" AND matchingLIKE "%countryCode%"  | stats count by matching | table count matching

But the answer returns all the possible variants that also contains [mobileNumber, countryCode].

chimuru84_0-1699972201243.png

What I want is a count only for all this results

chimuru84_1-1699972441357.png

 

Also I want to create a table with all specific searches I do. I know how to use append, but result is like a stairs, what other solution can be used?

Than you!

 

Labels (5)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

In this case, you can easily adapt @ITWhisperer's first search.  If you want to be more stringent against edge cases, you can use regex.

Let me suggest some alternatives.  The first is the simplest:

index="source*" mobilePhoneNumber countryCode
| stats count by matchField

(AND is implied between SPL search terms.)

If this still give you more output than desired, try

index="source*" mobilePhoneNumber countryCode
| where match(matchField, "\bmobilePhoneNumber\b") AND match(matchField, "\bcountryCode\b")
| stats count by matchField

If for some odd reason the two terms in index search eliminates too many, you can get rid of them. (But it will be a lot more expensive.)

Hope this helps.

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try converting matching to a multivalue field

index="source*" | where matching LIKE "%mobileNumber%" AND matching LIKE "%countryCode%"  
| eval matching=split(matching,",")
| stats count by matching | table count matching

chimuru84
Explorer

This query is giving this result 

chimuru84_0-1699976413821.png

but, I want to count by 2 or more words. Thank You

1.jpg

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Do you mean this?

index="source*" | where matching="mobileNumber,countryCode"  | stats count
0 Karma

chimuru84
Explorer

yes, I tried like this, bus 0 events are returned

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Please share a sample of the events you expect to be returned

0 Karma

chimuru84
Explorer

This is a part of result. What I want, to get in one line only mobileNumber and countryCode, on other line lastName, firstName, not all log where this words are meet.

chimuru84_0-1699977795730.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

OK so that's not the actual event. So, going by what you have shared, try this

| where matching="[mobileNumber, countryCode]"
0 Karma

chimuru84
Explorer

chimuru84_0-1699985125666.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Without seeing the events, it is difficult to know what to suggest. Hopefully, previous answers will at least give you some ideas.

0 Karma

chimuru84
Explorer

This is simple search, which give me this result. Result contains fields which contains "mobilePhoneNumber" OR "countryCode" OR "mobilePhoneNumber AND countryCode"

1.jpg

I want to return count (in one line) of all fields which contains both, mobilePhoneNumber and countryCode ("mobilePhoneNumber AND countryCode").

chimuru84_0-1700047130126.png

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Pro tips:

  • Post data (real or emulated) in text.
  • Post how you want the result to be in text.
  • Explain the logic between the two.

The last screenshot is impossible.  Your search is "| stats count by matching" but the results table has header "matchingFields" instead.  Additionally, your very first screenshot looks like part of a spreadsheet; there is no space after comma, and no square brackets ([ and ]) on two ends.  Is this "match" text field bounded by brackets?  Are spaces present in this text field?  Further more, if "match" text exists in raw events, why not filter directly in index search?  There are only two possible combinations of MobileNumber and CountryCode.  You can do something like

index="source*" match IN ("[MobileNumber, CountryCode]", "[ContryCode, MobileNumber]")
| stats count by match

Bottom line, you need to describe and illustrate data precisely (anonymize as needed but keep characteristics accurate), and illustrate desired results clearly, then state your logic clearly.

 

0 Karma

chimuru84
Explorer

This is simple search, which give me this result. Result contains fields which contains "mobilePhoneNumber" OR "countryCode" OR "mobilePhoneNumber AND countryCode"

1.jpg

I want to return count (in one line) of all fields which contains both, mobilePhoneNumber and countryCode ("mobilePhoneNumber AND countryCode").

chimuru84_0-1700047130126.png

0 Karma

yuanliu
SplunkTrust
SplunkTrust

In this case, you can easily adapt @ITWhisperer's first search.  If you want to be more stringent against edge cases, you can use regex.

Let me suggest some alternatives.  The first is the simplest:

index="source*" mobilePhoneNumber countryCode
| stats count by matchField

(AND is implied between SPL search terms.)

If this still give you more output than desired, try

index="source*" mobilePhoneNumber countryCode
| where match(matchField, "\bmobilePhoneNumber\b") AND match(matchField, "\bcountryCode\b")
| stats count by matchField

If for some odd reason the two terms in index search eliminates too many, you can get rid of them. (But it will be a lot more expensive.)

Hope this helps.

0 Karma

chimuru84
Explorer

Work fine, Thank you.

How can I ignore some value from result in same query, for example if I want to return only fields like second one in the screenshot. I mean, from first line in screenshot I want to exclude firstName, lastName and others. Thank you.

chimuru84_0-1700135368540.png

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Again, please illustrate/emulate data in text.

Does "exclude firstName, lastName" mean to remove them for display purposes?  For that, you can use rex command's sed mode.  Like

| rex field=matchingField mode=sed "s/\bfirstName\b// s/, *,/,/ s/\[, */[/ s/, *\]/]/"
| rex field=matchingField mode=sed "s/\blastName\b// s/, *,/,/ s/\[, */[/ s/, *\]/]/"

 

0 Karma

chimuru84
Explorer

I mean return all fields which does not contain firstName or lastName (do not remove them). Like in picture, second field does not contain firstName and lastName.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

That would be logical NOT

index="source*" mobilePhoneNumber countryCode NOT firstName NOT lastName
| stats count by matchField
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...