Archive

Compare two sources with multiple value

nguyenhuyhoang0
New Member

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,

0 Karma

nguyenhuyhoang0
New Member

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)

alt text

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

nguyenhuyhoang0
New Member

Hi @ gcusello,

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

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!