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:[email protected], QP_B:123, COUNTRY:USA}
Event 2: 
{ QP_C:[email protected], 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]:[email protected], 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\":\"[email protected]\", \"QP_B\":\"[email protected]\", \"COUNTRY\":\"[email protected]\"};{ \"QP_F\":\"abcgmail.com\", \"QP_G\":\"abcgmail.com\", \"COUNTRY\":\"abcgmail.com\"};{ \"QP_C\":\"[email protected]\", \"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:

[email protected]
[email protected] 

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.