topic Re: Need help with lookups issue in Splunk Search

Need help with lookups issue

neeltiwari — Wed, 05 Jan 2022 15:05:16 GMT

Hello Team, I need help with a splunk query where I am trying to get the AWS instance ID via lookup table but I am able to get the instance name with respect to IP , please find the query below and help me with the suggestion.

index=c3d_security host=ip-10-10* rule=corp_deny_all_to_untrust NOT dest_port=4431 | table src_ip dest_ip transport dest_port application | lookup Blocked_Non-httptraffic.csv src_ip as src_ip outputnew dest_ip

Note: I have made the csv file with lookup editor " Non-httptraffic.csv src" with two fields src_ip and dest_ip , if I am searching with above query so I am unable to get the instance name like host name with regards to IP Please help..

Re: Need help with lookups issue

richgalloway — Wed, 05 Jan 2022 15:08:09 GMT

If the lookup table only has src_ip and dest_ip then how do you expect to get the instance name?

Re: Need help with lookups issue

neeltiwari — Wed, 05 Jan 2022 17:27:30 GMT

@richgallowayThanks for the response, yes I wont get the instance name but we have the instance name but how can I extract the two datasets to table the Instance name.

Regards,

Neelesh Tiwari

Re: Need help with lookups issue

richgalloway — Wed, 05 Jan 2022 20:45:24 GMT

You say you have the instance name, but there is field in the given query that appears to contain that information. Where is the instance name? What the "two datasets" to which you refer?

Re: Need help with lookups issue

neeltiwari — Sun, 09 Jan 2022 09:51:38 GMT

@richgallowayYes, I get confused so basically I gave two searches so if I combine it so I will be able to get the data so can you help that how can I combine the two query.

aws-description-resource( (aws_account_id="*") , (region="*") , "ec2_instances")
| search (private_ip_address="172.20.187.54")

index=c3d_security host=ip-172-23* rule=corp_deny_all_to_untrust NOT dest_port=4431 | table src_ip dest_ip transport dest_port application

With the help of my first query, I can get the private IP address which is machine the host name so if I merge both the query so can I get the expected result?

Can this one below help me out with it.

sourcetype="Sourcetype_A" s1_field4="Completed" | search s1_field1=$from_token$ | join s1_field2 [ search sourcetype="Sourcetype_B" | rename s2_field1 as s1_field2 ] | table s1_field2, s1_field1,s2_field2,s2_field3

https://community.splunk.com/t5/Splunk-Search/How-to-combine-two-searches-with-common-value-into-one-table/m-p/497485

Thanks,

Neelesh Tiwari

Re: Need help with lookups issue

richgalloway — Wed, 12 Jan 2022 14:49:03 GMT

The search you gave looks like it should work.

How does it fail to produce the desired results?

Here is another way to do the same thing.

Re: Need help with lookups issue

neeltiwari — Mon, 07 Feb 2022 20:15:05 GMT

@richgallowayThank you and I could not try this out due to some issues and will try it today and keep you updated.

Regards,

Neelesh

Re: Need help with lookups issue

neelesh_tiwari — Tue, 08 Feb 2022 10:51:39 GMT

Hello @richgalloway I have followed the query and this is how I should write it.

First query:

(index="main" OR index="c3d_infra") sourcetype="aws:description" aws_account_id="*" region="*" source="*:ec2_instances" | search private_ip_address="172.19.122.6"

Second query:

" index=c3d_security host=ip-172-23* rule=corp_deny_all_to_untrust NOT dest_port=3031 | table src_ip dest_ip transport dest_port application"

If I merge it , please check below.

or do I have to write like this.

" index=c3d_security host=ip-172-23* rule=corp_deny_all_to_untrust NOT dest_port=3031 | table src_ip dest_ip transport dest_port application" | sourcetype="aws:description" s1_field4="Completed"
| search s1_field1=$from_token$
| append [ search sourcetype="pan:traffic" | rename s2_field1 as s1_field2 ]
| stats values(*) as * by s1_field2
| table s1_field2, s1_field1,s2_field2,s2_field3

Re: Need help with lookups issue

richgalloway — Tue, 08 Feb 2022 14:21:21 GMT

Why does the merged query look nothing like the two original queries? I expected something more like this:

(index="main" OR index="c3d_infra") sourcetype="aws:description" aws_account_id="*" region="*" source="*:ec2_instances" | search private_ip_address="172.19.122.6" | append [ search index=c3d_security host=ip-172-23* rule=corp_deny_all_to_untrust NOT dest_port=3031 | table src_ip dest_ip transport dest_port application ] | stats values(*) as * by <<some field(s) common to both searches>>