topic Re: Compare two sources with multiple value in Splunk Search

Compare two sources with multiple value

nguyenhuyhoang0 — Wed, 30 Sep 2020 02:23:46 GMT

Hi folks,

Hi have a case needing to compare 2 sources with CSV type

Source 1 has fields as below:
start_time_s1, end_time_s1, source_ip_s1, account

Source 2 has fileds as below:
start_time_s2, end_time_s2, source_ip_s2

I need to compare with the condition: If (start_time_s1 < start_time_s2) AND (end_time_s2 < end_time_s1) AND (source_ip_s2 = source_ip_s1) then table start_time_s2, end_time_s2, source_ip_s2, account

How way can I solve this query?

Regards,

Re: Compare two sources with multiple value

gcusello — Mon, 07 Oct 2019 06:45:51 GMT

Hi nguyenhuyhoang0901,
If you have these logs in two indexes, you could run something like this:

index=index1 OR index=index2
| rename source_ip_s1 AS source_ip source_ip_s2 AS source_ip
| stats values(start_time_s1) AS start_time_s1 values(end_time_s1) AS end_time_s1 values(account) AS account values(start_time_s2) AS start_time_s2 values(end_time_s2) AS end_time_s2 BY source_ip
| where (start_time_s1 < start_time_s2) AND (end_time_s2 < end_time_s1)
| table start_time_s2 end_time_s2 source_ip account

If instead you have two lookups it's just a little bit different:

| inputlookup lookup_s1
| lookup lookup_s2 source_ip_s2 AS source_ip_s1 OUTPUT start_time_s2 end_time_s2
| where (start_time_s1 < start_time_s2) AND (end_time_s2 < end_time_s1)
| table start_time_s2 end_time_s2 source_ip_s2 account

Bye.
Giuseppe

Re: Compare two sources with multiple value

nguyenhuyhoang0 — Wed, 30 Sep 2020 02:24:35 GMT

Hi gcusello,

Thanks for your information.
I describe more about my situation.
the source IP may be repeated so it can't work with values(*) as * by source_ips properly.
I give an example below: (assume that the time information was converted to timestamp)

the result needs to show a table and meet the criteria (begin <= start_time) AND ( end_time <= stop) AND (ip = source_ips)

Re: Compare two sources with multiple value

nguyenhuyhoang0 — Tue, 08 Oct 2019 09:58:24 GMT

Hi @ gcusello,

I add an example for more detail my case as my sharing