topic Re: splunk email query subject filter in Alerting

Writing a Splunk search to filter email subjects?

sulaimancds — Wed, 01 Mar 2023 14:59:49 GMT

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 ?

Re: splunk email query subject filter

gcusello — Wed, 01 Mar 2023 08:03:34 GMT

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

Re: splunk email query subject filter

sulaimancds — Wed, 01 Mar 2023 08:06:58 GMT

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

Re: splunk email query subject filter

gcusello — Wed, 01 Mar 2023 08:08:58 GMT

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

Re: splunk email query subject filter

sulaimancds — Wed, 01 Mar 2023 08:24:19 GMT

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.

Re: splunk email query subject filter

sulaimancds — Wed, 01 Mar 2023 08:25:39 GMT

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 ?

Re: splunk email query subject filter

gcusello — Wed, 01 Mar 2023 08:33:12 GMT

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

Re: splunk email query subject filter

sulaimancds — Wed, 01 Mar 2023 08:50:08 GMT

suspicoussubject_keywords.csv

keyword

cv
interview
offboarding
resume

Re: splunk email query subject filter

gcusello — Wed, 01 Mar 2023 10:31:08 GMT

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