Need help with lookups issue

[neeltiwari]

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..

[neeltiwari]

@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

[richgalloway]

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?

[richgalloway]

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

[neeltiwari]

@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...

Thanks,

Neelesh Tiwari

[richgalloway]

The search you gave looks like it should work.

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

How does it fail to produce the desired results?

Here is another way to do the same thing.

sourcetype="Sourcetype_A" s1_field4="Completed" 
| search s1_field1=$from_token$ 
| append [ search sourcetype="Sourcetype_B" | rename s2_field1 as s1_field2 ] 
| stats values(*) as * by s1_field2 
| table s1_field2, s1_field1,s2_field2,s2_field3

[neelesh_tiwari]

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.

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

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

[richgalloway]

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>>

[neeltiwari]

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

Regards,

Neelesh