Getting Data In

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

jagadeeshm
Contributor

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!

0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

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

alt text

Thanks,
J

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Maybe it's too simple but try this:

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

Bye.
Giuseppe

murataydogan
Engager

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

 

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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

alt text

Thanks,
J

jagadeeshm
Contributor

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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

0 Karma

jagadeeshm
Contributor

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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
Builder

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

0 Karma

cmerriman
Super Champion

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

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

sundareshr
Legend

Try this

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

jagadeeshm
Contributor

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...