topic Re: Search and count by multiple specific substrings in a field in Splunk Search

Search and count by multiple specific substrings in a field

chimuru84 — Tue, 14 Nov 2023 15:09:10 GMT

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

What I want is a count only for all this results

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!

Re: Search and count by multiple specific substrings in a field

ITWhisperer — Tue, 14 Nov 2023 14:40:08 GMT

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

Re: Search and count by multiple specific substrings in a field

chimuru84 — Tue, 14 Nov 2023 15:42:58 GMT

This query is giving this result

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

Re: Search and count by multiple specific substrings in a field

ITWhisperer — Tue, 14 Nov 2023 15:51:33 GMT

Do you mean this?

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

Re: Search and count by multiple specific substrings in a field

chimuru84 — Tue, 14 Nov 2023 15:53:43 GMT

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

Re: Search and count by multiple specific substrings in a field

ITWhisperer — Tue, 14 Nov 2023 15:55:02 GMT

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

Re: Search and count by multiple specific substrings in a field

chimuru84 — Tue, 14 Nov 2023 16:04:42 GMT

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.

Re: Search and count by multiple specific substrings in a field

ITWhisperer — Tue, 14 Nov 2023 16:47:24 GMT

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

| where matching="[mobileNumber, countryCode]"

Re: Search and count by multiple specific substrings in a field

chimuru84 — Tue, 14 Nov 2023 18:05:33 GMT

Re: Search and count by multiple specific substrings in a field

ITWhisperer — Tue, 14 Nov 2023 18:10:33 GMT

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

Re: Search and count by multiple specific substrings in a field

chimuru84 — Wed, 15 Nov 2023 13:02:21 GMT

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

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

Re: Search and count by multiple specific substrings in a field

yuanliu — Wed, 15 Nov 2023 04:30:16 GMT

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.

Re: Search and count by multiple specific substrings in a field

chimuru84 — Wed, 15 Nov 2023 13:02:35 GMT

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

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

Re: Search and count by multiple specific substrings in a field

yuanliu — Thu, 16 Nov 2023 11:12:44 GMT

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.

Re: Search and count by multiple specific substrings in a field

chimuru84 — Thu, 16 Nov 2023 11:52:22 GMT

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.

Re: Search and count by multiple specific substrings in a field

yuanliu — Thu, 16 Nov 2023 18:08:54 GMT

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/, *\]/]/"

Re: Search and count by multiple specific substrings in a field

chimuru84 — Thu, 16 Nov 2023 18:40:44 GMT

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.

Re: Search and count by multiple specific substrings in a field

yuanliu — Fri, 17 Nov 2023 04:23:25 GMT

That would be logical NOT

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