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

[jagadeeshm]

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!

[gcusello]

Maybe it's too simple but try this:

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

Bye.
Giuseppe

[murataydogan]

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

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

[javiergn · Accepted solution]

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

[jagadeeshm]

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

[javiergn]

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 "<>=".<>.";"

[jagadeeshm]

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

[javiergn]

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.

[Cuyose]

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

[cmerriman]

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

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

[sundareshr]

Try this

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

[jagadeeshm]

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