topic Re: How to compare the data from CSV and get the desired result in Splunk Search

How to compare the data from CSV and get the desired result

nitinpa — Sun, 16 Aug 2020 23:50:47 GMT

I have a CSV (domains.csv) that contain the list of domains. I have uploaded into Splunk and get the result using [| inputlookup domains.csv]. Splunk is getting the data from email system for inbound/outbound emails.

I want to check against my domains list which are using email security protocols like TLS, SPF, DKIM and DMARC. How can I get that info?

index="pp_index" sourcetype="pp_messagelog" [| inputlookup domains.csv ]

Re: How to compare the data from CSV and get the desired result

gcusello — Mon, 17 Aug 2020 08:03:18 GMT

Hi @nitinpa,

to filter a search using a lookup, you have to be sure that the field names are the same both in search and in lookup (fields are case sensitive), if not you have to rename one of them.

In few words, if in the pp_index the url is called "url" and in lookup is called domain, you have to run something like this:

index="pp_index" sourcetype="pp_messagelog" [| inputlookup domains.csv | rename domain AS url | fields url ] | ...

if in addition, domain is a part of the url and not the full url, you have to extract the domain from the url using a regex.

If you share an example of logs and of domain .csv, I could be more precise.

Ciao.

Giuseppe

Re: How to compare the data from CSV and get the desired result

nitinpa — Mon, 17 Aug 2020 21:49:34 GMT

Hi Giuseppe,

Thank you for your help on this!

I got the list of domains from domains.csv. I want to search those domains against inbound emails which is stored in sourcetype = PP_messagelog. I want to match the sender domain field and check if they are using proper email security protocols or not by verifying TLS, SPF, DKIM DMARC etc.

There is a field called "connection.tls.inbound.version" so I can check from that CSV that this domain is using TLS v1.2 or 1.1 or 1

For example, If the list contains gmail.com. I will check gmail.com against search and using the above field, find out that whether gmail.com is using TLSv1.2 or not.

Hope this helps

Thanks,

Nitin

Re: How to compare the data from CSV and get the desired result

gcusello — Tue, 18 Aug 2020 07:35:33 GMT

Hi @nitinpa,

I try to summarize:

in your lookup (called e.g. "my_domain_lookup") you have two fields:
- domain (e.g.: gmail.com),
- connection.tls.inbound.version (e.g.: 1.2, 1.1, 1.0);
in the events you have email addresses (e.g. my_address@gmail.com) in a field called "email";
you want to enrich the reaults of your search with the information about TLS.

Is this correct?

Only two questions:

do you want to highlight the condition TLS=1.2 or TLS!=1.2?
what happens if the domain isn't in the lookup?

In the meantime, you could run something like this:

index=your_index sourcetype=PP_messagelog | rex field=email "[^@]*@(?<domain>.*)" | dedup domain | lookup my_domain_lookup domain OUTPUT connection.tls.inbound.version | table domain connection.tls.inbound.version

to have the list of all domains with related TLS (when present).

At the end you can add a condition to filter domains based on TLS (e.g. to find only TLS=1.2):

| search connection.tls.inbound.version="1.2"

Ciao.

Giuseppe

Re: How to compare the data from CSV and get the desired result

nitinpa — Tue, 18 Aug 2020 21:43:09 GMT

Hi Giuseppe,

Thank you for your help on this!

That's correct! I want to enrich the result of the search with the information about TLS.

I want to highlight the condition TLS=1.2

There are two parts of it

Using below command, I am getting the list of domains within specific time frame "index="pp_index" sourcetype="pp_messagelog" | rex field=envelope.from "(?<=@)(?<domain>([a-zA-z0-9.-]*))" | dedup domain | table domain" AND
I have the list of domains which is stored in domains.csv "| inputlookup Domains.csv"

I want to check the domains from the list (domains.csv) with the source (pp_messagelog) and check if it's using TLS 1.2 or not. If the domain is not from the list then simply ignore that domain.

Hope this helps!

Thanks,

Nitin

Re: How to compare the data from CSV and get the desired result

gcusello — Wed, 19 Aug 2020 07:08:32 GMT

Hi @nitinpa,

you speak of source (pp_messagelog), but from your question it seems to be the sourcetype: which information have you in the lookup: source or sourcetype?

in other words, do you want to relate the main search and the lookup using the sourcetype, the domain or both?

If you want to use the sourcetype to check the TLS and not the domain, you have only to modify just a little bit my previous search:

index=your_index sourcetype=PP_messagelog | rex field=email "[^@]*@(?<domain>.*)" | dedup domain | lookup my_domain_lookup sourcetype OUTPUT connection.tls.inbound.version | table domain sourcetype connection.tls.inbound.version | search connection.tls.inbound.version="1.2"

if you want to use the domain, use the old search.

Ciao.

Giuseppe

Re: How to compare the data from CSV and get the desired result

nitin_pangerkar — Wed, 19 Aug 2020 22:15:50 GMT

Hi Giuseppe,

Thank you for your help on this!

Thanks and Regards,

Nitin