How to use where clause usage with log files.?

[SanthoshSreshta]

Hi

Can anyone help me in getting the below requirement
I have SRC_IP,DST_IP in my log files. I am writing the query as

source="E:\\SPLUNK\\FIREWALL\\*" status = "Allow" | stats values(dst_ip) where src_ip="192.168.1.115"

the corresponding dst_ip should be displayed for the where condition kept on src_ip. please help me out in getting this.

Thanks,
Santhosh

[bwooden]

There are a few things to consider here.

Generically, the where command leverages the same functions as eval. It happens that in your example, you could use a search command or the where command. When this is the case, the search will be more performant if that condition is moved to the base search, like this:

 source="E:\\SPLUNK\\FIREWALL\\*" status = "Allow"  src_ip="192.168.1.115" | stats values(dst_ip) 

I think the difficulty you're having though is that when the transforming stats command is invoked, you're lose the src_ip field -- that is to say when you do a stats showing only the values of dst_ip, the result set will only have the dst_ip field available to any commands further down the pipeline. So to keep your same search, you would need to do a bit more work to keep src_ip in the results coming from stats (you could then use the table or fields command to remove that field from the result set).

In this specific case, the above search will be faster.

[woodcock · Accepted solution]

You need to switch it around like this:

 source="E:\\SPLUNK\\FIREWALL\\*" status = "Allow"  | where src_ip="192.168.1.115"| stats values(dst_ip) 

Or better yet, skip it like this:

     source="E:\\SPLUNK\\FIREWALL\\*" status = "Allow" src_ip="192.168.1.115"| stats values(dst_ip) 

Or maybe this:

     source="E:\\SPLUNK\\FIREWALL\\*" status = "Allow" | stats values(dst_ip) by src_ip | where src_ip="192.168.1.115"

[solarboyz1]

If you are only looking for the dst_ip from a single src_ip, limit your results to that src_ip and then stats:

source="E:\SPLUNK\FIREWALL\*" status = "Allow" src_ip="192.168.1.115" | stats values(dst_ip)