topic Re: Search for the events with the same IP with two different field names from the two different index in Splunk Search

Search for the events with the same IP with two different field names from the two different index

staparia — Tue, 29 Sep 2020 22:53:10 GMT

(index = intrusion dest_ip) OR (index = proxy r_ip)
dest_ip should always be equal to r_ip

Re: Search for the events with the same IP with two different field names from the two different index

richgalloway — Tue, 29 Sep 2020 22:53:15 GMT

You could use join, but that's inefficient.

index=intrusion dest_ip=* | join dest_ip [search index=proxy r_ip=* | rename r_ip as dest_ip]

A better way is to use stats. Replace 'some_field' with a field name from your events. Add more 'some_field' arguments as needed for all the events you wish to see.

(index=intrusion dest_ip=*) OR (index=proxy r_ip=*) | eval ip=coalesce(dest_ip, r_ip) 
| stats values(some_field) as some_field by ip

Re: Search for the events with the same IP with two different field names from the two different index

staparia — Tue, 29 Sep 2020 22:53:31 GMT

Hello, the stas query is giving me entire set of results. Whereas i wanted a query where if an IP 10.10.10.10 is involved --> it should return results in such a manner this particular IP (10.10.10.10) is present in both search queries ; that is

Query 1 --> (index=intrusion dest_ip=)
Query 2 --> (index=proxy r_ip=)

where r_ip and dest_ip = 10.10.10.10

Re: Search for the events with the same IP with two different field names from the two different index

staparia — Tue, 29 Sep 2020 22:53:18 GMT

Hello,
i wanted all the events in such a manner where if dest_ip = 10.10.10.10 and r_ip = 10.10.10.10...

Both the values are same and matching

Re: Search for the events with the same IP with two different field names from the two different index

staparia — Tue, 29 Sep 2020 22:53:24 GMT

I would seek results wherein r_ip=dest_ip . Example r_ip=10.10.10.10 and dest_ip=10.10.10.10 . So i would want only those results when these two fields have same values

Re: Search for the events with the same IP with two different field names from the two different index

richgalloway — Mon, 21 Jan 2019 16:25:28 GMT

Please share your query. Perhaps there is an error preventing the expected results.

The by clause of the stats command groups events that have the same value in the 'ip' (in this case) field.

If you're not happy with the results of the stats command, try my join example.

Re: Search for the events with the same IP with two different field names from the two different index

staparia — Tue, 29 Sep 2020 22:53:57 GMT

(index=intrusion attack_signature=MS-Executable-File destination_port=80 direction=Outbound result_status=Inconclusive) OR (index=proxy x_exception_id!=IT-HotSpot-Denied AND cs_host!="testrating.webfilter.bluecoat.com" cs_host!="help.tower.shanhu99.com" cs_categories="none" url=*.php) | eval ip=coalesce(dest_ip, r_ip) | stats count by ip

It is giving me results of the values of the IP which is present in in both the indexes.. but not looking at the condition where i want dest_ip=r_ip

Re: Search for the events with the same IP with two different field names from the two different index

richgalloway — Wed, 23 Jan 2019 13:45:25 GMT

Have you tried using join?

Re: Search for the events with the same IP with two different field names from the two different index

staparia — Tue, 29 Sep 2020 23:15:14 GMT

i did. didnt show the results as expected.

suppose index=proxy AND s_ip="some value"
index=ips AND d_ip="some value"

now i would seek raw logs with all the fields (containing both the indexes) matching the values of s_ip and d_ip

suppose if i enter 10.10.10.10 (be it s_ip or d_ip), it gives me results of all the logs present in the index -->proxy and index--> ips

Re: Search for the events with the same IP with two different field names from the two different index

woodcock — Tue, 12 Feb 2019 05:47:43 GMT

Like this:

(index = intrusion dest_ip) AND [search index = proxy r_ip | table r_ip | rename dest_ip AS r_ip]

OR:

(index = intrusion dest_ip) OR (index = proxy r_ip)
| eval dest_ip = coalesce(dest_ip, r_ip)
| stats dc(sourcetype) AS sourcetypeCount values(sourcetype) AS sourcetypes BY dest_ip
| where sourcetypeCount==2