Compare two fields (IP addresss) from 2 different vendor firewalls

[rookie507SL]

Hi mates,

I'm figuring out how I can show a table with matching IP addresses from 2 different vendor firewalls.

So far I've tried with the "join" statement in order to do a 2nd search and then, an if statement in order to compare. Here is my search:

index=index-company sourcetype=firewall1  NOT srcIP=172.20.* | stats count by srcIP | sort 10 -count | rename srcIP as "srcfw1" | join [search index=index-company sourcetype=firewall2  NOT srcIP2=172.20.* | stats count by srcIP2 | sort 10 -count ] | eval DiffIP=if(srcfw1==srcIP2, srcIP2 ,srcfw1) | table srcfw1 DiffIP

Unfortunately, I do not get results 😞

Any help would be appreciated.

[kamlesh_vaghela]

Hi
Can you please try this?

index=index-company sourcetype=firewall1 NOT srcIP=172.20. 
| stats count by srcIP 
| sort 10 - count | eval N1=1 | accum N1
| rename srcIP as "srcfw1" 
| append 
    [ search index=index-company sourcetype=firewall2 NOT srcIP2=172.20. 
    | stats count by srcIP2 
    | sort 10 - count | eval N1=1 | accum N1] 
| stats values(srcIP) as srcIP values(srcIP2) as srcIP2 values(srcfw1) as srcfw1 by N1
| eval DiffIP=if(srcfw1==srcIP2, srcIP2 ,srcfw1) 
| table srcfw1 DiffIP

can you make sure your individual search will return values?

Thanks

[rookie507SL]

Hi kamlesh_vaghela,

Thanks for your reply, I really appreciate.

I'm able to get a table in the statistics tab with the 2 fields, srcfw1 and DiffIP but, IP addresses do not match. Actually, when I look for the Events tab, I only see 1 sourcetype.

If I do both searches independently, I can see that results march some IP addresses in both searches.

[kamlesh_vaghela]

Hi rookie507SL,

Yes, event tab will show you only events of the first search. It will not show you events of any subsearches.
So you can't compare event tab IP Addresses to our search output.

To execute individually & compare is the best approach to do so.

For more clarification in comparision of IP Address and there count try below search.

index=index-company sourcetype=firewall1 NOT srcIP=172.20. 
 | stats count by srcIP 
 | sort 10 - count | eval N1=1 | accum N1
 | rename srcIP as "srcfw1", count as count1 
 | append 
     [ search index=index-company sourcetype=firewall2 NOT srcIP2=172.20. 
     | stats count by srcIP2 
     | sort 10 - count | eval N1=1 | accum N1 | rename count as count2 ] 
 | stats values(srcIP) as srcIP values(srcIP2) as srcIP2 values(srcfw1) as srcfw1 values(count1) as count1 values(count2) as count2 by N1
 | eval DiffIP=if(srcfw1==srcIP2, srcIP2 ,srcfw1) 
 | table N1 DiffIP srcfw1 count1 srcIP2 count2

This will give you clear comparision of firewall data with count of events for perticular IP.

Thanks

[HiroshiSatoh]

Try this!

| set diff 
[search  index=index-company sourcetype=firewall1 NOT srcIP=172.20. | stats count by srcIP |rename  srcIP as DiffIP| sort 10 -count |table DiffIP] 
[search  index=index-company sourcetype=firewall2 NOT srcIP2=172.20. | stats count by srcIP2|rename  srcIP2 as DiffIP| sort 10 -count  |table DiffIP]

※Please be careful when increasing the number of sub search because there is a limit of 10000 defaults.

[rookie507SL]

Hi HiroshiSatoh,

Yes, thanks for your reply.

When I do the "set diff", I actually get some IP addresses for the DiffIP table in the statistics tab, but cannot confirm if this data comes from the 2 firewalls (2 sourcetypes) since I cannot see logs in the Event tab.

[HiroshiSatoh]

How about this?

index=index-company sourcetype=firewall1 NOT srcIP=172.20. 
 | stats count by srcIP 
 | sort 10 - count | eval wk_srcIP=srcIP  
 | append 
     [ search index=index-company sourcetype=firewall2 NOT srcIP2=172.20. 
     | stats count by srcIP2 
     | sort 10 - count |  | eval wk_srcIP=srcIP2] 
 | stats dc(sourcetype) as count,values(sourcetype) as sourcetype by wk_srcIP
 | where count<2
 | table sourcetype ,wk_srcIP