Writing a Splunk search to filter email subjects?

[sulaimancds]

index=mail 
| lookup email_domain_whitelist domain AS RecipientDomain output domain as domain_match 
| where isnull(domain_match) 
| lookup all_email_provider_domains domain AS RecipientDomain output domain as domain_match2 
| where isnotnull(domain_match2) 
| stats values(recipient) as recipient values(subject) as subject earliest(_time) AS "Earliest" latest(_time) AS "Latest" by RecipientDomain sender 
| where mvcount(recipient)=1
| eval subject_count=mvcount(subject)
| sort - subject_count 
| convert ctime("Latest") 
| convert ctime("Earliest")

i have list of suspicious keywords to in a list in lookup editor called suspicoussubject_keywords.

can you include the query to lookup for this keyword in subject and then display results?

in another use case , i have a list not to show the following subject  filtersubjects  in lookup.

This will not display the results where there are the following words like CV, Resume in the subjects

can you help me with the query ?

[gcusello]

Hi @sulaimancds,

if you have a list of suspicious keywords in a lookup you could add to the main search this condition (assuming that the field in the lookup is called "keyword"):

index=mail [ | inputlookup suspicoussubject_keywords | rename keyword AS query | fields query ]
| ...

in this way you performa a full text search on your raw data using the keywords from the lookup.

Ciao.

Giuseppe

[sulaimancds]

 i have a list not to show the following subject  filtersubjects  in lookup.

This will not display the results where there are the following words like CV, Resume in the subjects

can you help me with the query ?

[gcusello]

Hi @sulaimancds,

if you want to exclede events containing keywords from the lookup, you have only to add a NOT condition tto the main search:

index=mail NOT [ | inputlookup suspicoussubject_keywords | rename keyword AS query | fields query ]
| lookup email_domain_whitelist domain AS RecipientDomain output domain as domain_match 
| where isnull(domain_match) 
| lookup all_email_provider_domains domain AS RecipientDomain output domain as domain_match2 
| where isnotnull(domain_match2) 
| stats values(recipient) as recipient values(subject) as subject earliest(_time) AS "Earliest" latest(_time) AS "Latest" by RecipientDomain sender 
| where mvcount(recipient)=1
| eval subject_count=mvcount(subject)
| sort - subject_count 
| convert ctime("Latest") 
| convert ctime("Earliest")

Ciao.

Giuseppe

[sulaimancds]

thank you, can you put those into my query as shown above.

[gcusello]

Hi @sulaimancds,

ok, try this:

index=mail [ | inputlookup suspicoussubject_keywords | rename keyword AS query | fields query ]
| lookup email_domain_whitelist domain AS RecipientDomain output domain as domain_match 
| where isnull(domain_match) 
| lookup all_email_provider_domains domain AS RecipientDomain output domain as domain_match2 
| where isnotnull(domain_match2) 
| stats values(recipient) as recipient values(subject) as subject earliest(_time) AS "Earliest" latest(_time) AS "Latest" by RecipientDomain sender 
| where mvcount(recipient)=1
| eval subject_count=mvcount(subject)
| sort - subject_count 
| convert ctime("Latest") 
| convert ctime("Earliest")

Ciao.

Giuseppe

[sulaimancds]

hi,

list is saved already.  

this error is being showed.

• [subsearch]: The lookup table 'email_subjects' requires a .csv or KV store lookup definition.
• [subsearch]: The lookup table 'email_subjects' is invalid.

help.

[gcusello]

Hi @sulaimancds,

these errors are in the part of the search that you shared, not in the part I updated.

Anyway, check the email_subjects lookup because there's an error.

Ciao.

Giuseppe

[sulaimancds]

suspicoussubject_keywords.csv

keyword

cv
interview
offboarding
resume