topic Re: How to use the foreach command to list a particular field that contains an email address? in Getting Data In

How to use the foreach command to list a particular field that contains an email address?

jagadeeshm — Wed, 19 Oct 2016 21:08:42 GMT

I have events in JSON format as follows -

Event 1: 
{ QP_A:abc@gmail.com, QP_B:123, COUNTRY:USA}
Event 2: 
{ QP_C:XYZ@gmail.com, QP_B:123, COUNTRY:USA}
Event 3: 
{ QP_f:100, QP_Bb:123, COUNTRY:USA}
Event 4: 
{ COUNTRY:USA, STATE:CT}
Event 5: 
{ QP_A[0][A]:abc@gmail.com, COUNTRY:USA, STATE:CT}

Observe that QP_* fields don't appear in all events and even if they did they may not have a field that has an email address.

I am trying to search and list all QP_* fields that have email addresses in them. How can I do it?

I tried using foreach command, but no luck in the syntax -

index=abc  QP_*  
| foreach QP_* [eval fieldnames = if(match(<>, ".com"), "<>", "NoMatch")] | table _raw fieldnames

I see the output (may be wrong but) I see error that says something like -

[splunkindxers-001] Failed to parse templatized search for field 'QP_A[0][A]'

The output I am looking for in case of my above events is -

QP_A
QP_C
QP_A[0][A]

Thanks!

Re: How to use the foreach command to list a particular field that contains an email address?

sundareshr — Wed, 19 Oct 2016 21:27:01 GMT

Try this

base search | rex "{\s(?<emfield>[^:]+):.*@"  | table emfield

Re: How to use the foreach command to list a particular field that contains an email address?

jagadeeshm — Wed, 19 Oct 2016 21:30:52 GMT

It did not pick anything. I can see one column emfield with no values in it.

Re: How to use the foreach command to list a particular field that contains an email address?

cmerriman — Wed, 19 Oct 2016 21:44:36 GMT

Did you use the '<>' in the foreach command?

index=abc  QP_*  
 | foreach QP_* [eval fieldnames = if(match('<<FIELD>>', ".com"), "<>", "NoMatch")] | table _raw fieldnames

Re: How to use the foreach command to list a particular field that contains an email address?

javiergn — Wed, 19 Oct 2016 21:52:31 GMT

What about this?

index=abc  QP_*  
| eval fieldnames = ""
| foreach QP_* [eval temp = if(match('<<FIELD>>', "^([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})$"), "<<FIELD>>;", "") | eval fieldnames = fieldnames . temp]
| eval fieldnames = split(fieldnames, ";")
| table _raw fieldnames

When I replicated your problem in my lab it seemed to work fine. See below:

| stats count | fields - count
| eval raw = split("{ \"QP_A\":\"abc@gmail.com\", \"QP_B\":\"abc@gmail.com\", \"COUNTRY\":\"abc@gmail.com\"};{ \"QP_F\":\"abcgmail.com\", \"QP_G\":\"abcgmail.com\", \"COUNTRY\":\"abcgmail.com\"};{ \"QP_C\":\"XYZ@gmail.com\", \"QP_B\":\"123\", \"COUNTRY\":\"USA\"}", ";")
| mvexpand raw
| rename raw as _raw
| spath
| eval fieldnames = ""
| foreach QP_* [eval temp = if(match('<<FIELD>>', "^([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})$"), "<<FIELD>>;", "") | eval fieldnames = fieldnames . temp]
| eval fieldnames = split(fieldnames, ";")

Output: see picture

Thanks,
J

Re: How to use the foreach command to list a particular field that contains an email address?

gcusello — Thu, 20 Oct 2016 08:50:09 GMT

Maybe it's too simple but try this:

your_search | rex "(?<myQP>QP_\w).\w+@[^,]*" | table myQP

Bye.
Giuseppe

Re: How to use the foreach command to list a particular field that contains an email address?

jagadeeshm — Fri, 21 Oct 2016 12:41:03 GMT

Is there a way to get fields and their respective values separately ?

Re: How to use the foreach command to list a particular field that contains an email address?

javiergn — Fri, 21 Oct 2016 13:24:23 GMT

You mean something like this?

your previous search
| foreach QP_* [eval temp = if(match('<<FIELD>>', "^([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})$"), "<<FIELD>>=".<<FIELD>>.";", "") | eval fieldnames = fieldnames . temp]
| eval fieldnames = split(fieldnames, ";")
| table _raw fieldnames

Output:

QP_A=abc@gmail.com
QP_B=abc@gmail.com

All I've changed is "<>;" with "<>=".<>.";"

Re: How to use the foreach command to list a particular field that contains an email address?

jagadeeshm — Fri, 21 Oct 2016 14:31:58 GMT

Woh, that worked. I am still trying to understand how the match returned the email address!

Re: How to use the foreach command to list a particular field that contains an email address?

javiergn — Fri, 21 Oct 2016 16:58:24 GMT

It basically translates to:

IF 
    THE_VALUE_OF_YOUR_FIELD_CAPTURED_WITH_FOREACH ('<<FIELD>>')
MATCHES
    EMAIL REGEX (^([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})$)
THEN
    "FIELD NAME = CONCATENATED WITH FIELD VALUE AND SEMICOLON" ("<<FIELD>>=".<<FIELD>>.";")
ELSE
    EMPTY STRING

Hope that's clear enough.

Re: How to use the foreach command to list a particular field that contains an email address?

Cuyose — Wed, 10 Oct 2018 19:59:37 GMT

I am unable to get the values for my fields using this example. While I am able to successfully return only the fields I want, when editing to return the values, I just get
Failed to parse templatized search for field ***

Re: How to use the foreach command to list a particular field that contains an email address?

murataydogan — Fri, 15 Jan 2021 06:26:29 GMT

actualy that has give me some idea, extract data without some words. it's worked. thanks.

| rex field=domain "(?P<s>[^blabla.com].+)"