Correlation of two searches

[Habanero]

Good day Community,

I would like to know what is the best approach to filters events based on previous query. My precisely here is my scenario:

I am attempting to correlate our firewall logs against Windows Event Log 5156 / 5154 which is the local firewall that allowed a connection.

I am not sure if a multi-search is the best approach, or using append vs join vs subsearch. Did anyone ever crafted a SPL similar to the one describe above, or can provide some insight into the best method to achieve the results wanted.

Thank you,

[gcusello]

Hi @Habanero,

in general, if you can, avoid join command because it's a very slow command, in addition has the limit (as all the subsearches) of 50,000 results.

So you have to know two thing:

• the secondary search could have more or less than 50,000 results?
• do you need some informations from the secondary search or you need only to filter the primary search?

knowing these two things you can choos the best solution for your need:

if you need to filter the results of the first search using the second one and you have less than 50,000 results in the subsearch you can run something like this:

e.g. searching host in index1 that are also present in index1:

index=index1 [ search index=index2 | dedup host | fields host ]
| ...

the only rule is that the field names must be the same in main and secondary search.

If instead the results in secondary search are more than 50,000, you cannot use subsearch and you have to correlate them, something like this:

index=index1 OR index=index2
| stats dc(index) AS count BY index
| where count=2

if you need to take fields from index1 and from index2, you have to run something like this:

index=index1 OR index=index2
| stats values(field1.1) As field1.1 values(field1.2) As field1.2 values(field2.1) As field12.1 values(field2.2) As field12.2 BY index

i hope to be clear.

Ciao.

Giuseppe

[Habanero]

Thank you @gcusello for the prompt reply.

It cleared up some questions in regards to the limitation.

One of the issues are the fact that both index does not have the same name field.

For instance this is the SPL for my first search.

index=estreamer src_ip!="10.0.0.0/8" src_ip!="192.168.0.0/24" src_ip!=172.16.0.0/12 (app_proto="TeamViewer" app_proto="FTP" OR app_proto="FTP Data" OR app_proto="Telnet" OR app_proto="RDP" OR app_proto="SSH" OR dest_port="3389" OR dest_port="23" OR dest_port="22") fw_rule_action="Allow"

The event of interest I am seeking are IP addresses that were allowed by the firewall and opened connection on our Windows servers (EventCode 5154/5156)

Our firewall uses the field name `src_ip` and Windows Event are `Source_Address`

If I understand I would have to rename the field to be able to search against our Windows Index

| rename src_ip as Source_Address

[gcusello]

Hi @Habanero,

yes, the main action you have to do is to rename one of the fields to have the same fieldname in both the partial searches.

You have to do this in both the cases: if you have to perform a subsearch or if you have to correlate searches with stats command.

So, you have to run something like this: 

index=estreamer NOT (src_ip="10.0.0.0/8" OR src_ip="192.168.0.0/24" OR src_ip=172.16.0.0/12) (app_proto="TeamViewer" OR app_proto="FTP" OR app_proto="FTP Data" OR app_proto="Telnet" OR app_proto="RDP" OR app_proto="SSH" OR dest_port="3389" OR dest_port="23" OR dest_port="22") fw_rule_action="Allow" [ search index=wineventlog | rename Source_Address AS src_ip | fields src_ip ]
| ...

Only two little final notes: you forgot one OR in the parenthesis conditions and it's better to have a NOT = condition than a != condition.

Ciao.

Giuseppe

[ericjorgensenjr]

Something like this can accomplish what you're trying to do. You can eliminate the need to rename in the search if you normalize your data (e.g. create a field alias that aliases Source_Address to src_ip).

(index=estreamer src_ip!="10.0.0.0/8" src_ip!="192.168.0.0/24" src_ip!=172.16.0.0/12 (app_proto="TeamViewer" app_proto="FTP" OR app_proto="FTP Data" OR app_proto="Telnet" OR app_proto="RDP" OR app_proto="SSH" OR dest_port="3389" OR dest_port="23" OR dest_port="22") fw_rule_action="Allow") OR (index=windows source=wineventlog EventCode IN ("5154","5156") Source_Address!="10.0.0.0/8" Source_Address!="192.168.0.0/24" Source_Address!="172.16.0.0/12") | rename Source_Address as src_ip | stats values(somefield) as somefield, values(someotherfield) as someotherfield by src_ip