Search and count by multiple specific substrings in a field

[chimuru84]

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!

[ITWhisperer]

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]

This query is giving this result 

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

 

[ITWhisperer]

Do you mean this?

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

[chimuru84]

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

[ITWhisperer]

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

[chimuru84]

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.

[ITWhisperer]

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

| where matching="[mobileNumber, countryCode]"

[chimuru84]

[ITWhisperer]

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

[chimuru84]

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

[yuanliu]

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.

[chimuru84]

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

[yuanliu · Accepted solution]

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.

[chimuru84]

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.

[yuanliu]

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

[chimuru84]

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.

[yuanliu]

That would be logical NOT

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