topic Re: Condition based Joins in Splunk Search

How to execute these condition based Joins?

mnj1809 — Mon, 17 Oct 2022 14:49:09 GMT

Hello,

I need your help to find a way to achieve the following use case:

in main search:
I've to categories: Windows and NIX. both the categories have ip and hostname fields.

category ip hostname
windows x.x.x.x a
nix y.y.y.y b

Now my requirement is to join the above result set with another result set based on the following business rules:

- for windows, I want to join based on the hostname only.
- for nix, I want to join based on both ip and hostname.

Thanks in advance for the help.

Re: Condition based Joins

gcusello — Mon, 17 Oct 2022 12:21:44 GMT

Hi @mnj1809,

I suppose that you want to join the results in one search, but what are the results after joining?

anuway. if in another index you have ip, hostname and other information (e.g. field1 and field2, you could run something like this:

Ciao.

Giuseppe

Re: Condition based Joins

ITWhisperer — Mon, 17 Oct 2022 12:26:46 GMT

Firstly, you should try to avoid using join in splunk - splunk is not an SQL database.

Secondly, it depends what your other data source contains and where it is coming from, e.g. another index or lookup file.

Please can you expand on your usecase?

Re: Condition based Joins

mnj1809 — Fri, 21 Oct 2022 12:33:28 GMT

Thanks a lot for your prompt response. Yes we should avoid joins to improve the performance. but sometimes we've to use joins. 🙂
The main search has the different data source and the sub search has the different one.

my requirement is to compare two inventories let's say CMDB and Qualys inventories based on IP and hostnames in the CMDB inventory.

So my requirement is to join the cmdb hostnames with qualys hostnames only for "windows category" (I don't want to join the cmdb ip with qualys ip).

But for nix category I want to join the cmdb hostnames and ip with both qualys hostnames and ip.
So as this way I find the assets which are in cmdb and not in qualys.

Hope this will help you to understand my requirement.
@ITWhisperer @gcusello

Re: Condition based Joins

mnj1809 — Mon, 17 Oct 2022 13:31:54 GMT

Hi Giuseppe,

Thanks for your quick response but the solution that you provide will not work in my case.

@gcusello

Re: Condition based Joins

gcusello — Mon, 17 Oct 2022 14:10:08 GMT

Hi @mnj1809,

You could also use the way I described to choose the correlation key between the archives, but, as @ITWhisperer said, you should try to avoid using join in splunk - splunk is not an SQL database because it's very slow and requires many resources.

So I hint to adapt my approach to your need, instead to use join.

Ciao.

Giuseppe

Re: Condition based Joins

ITWhisperer — Mon, 17 Oct 2022 15:12:13 GMT

From the way you are trying to solve the problem, would it be fair to say

for windows, the ip address in cmdb may not match ip address in qualys for the same host name
for nix, there may be multiple ip addresses for the same host name in both cmdb and qualys
qualys doesn't record what the category is

Re: Condition based Joins

ITWhisperer — Mon, 17 Oct 2022 15:42:42 GMT

Assuming that points above are true, you might need to change this around if you want to keep the cmdb ip address rather than the qualys ip address

``` Copy cmdb field(s) and category to qualys events for nix (host and ip matches) ``` | eventstats values(cmdb_field) as cmdb_field values(category) as category by hostname ip ``` Uncategorised qualys events must be windows ``` | fillnull value="windows" category ``` Create matching key dependent on category ``` | eval hostip=if(category="windows",hostname."|".category,hostname."|".ip) ``` Copy cmdb field(s) to qualys events ``` | eventstats values(cmdb_field) as cmdb_field by hostip ``` Only need to keep qualys events ``` | where index="qualys"

Re: Condition based Joins

mnj1809 — Fri, 21 Oct 2022 11:01:25 GMT

First of all thanks to @ITWhisperer for the prompt responses but unfortunately the solution that you shared will not work in my use case. I find the solution from my own. What I did is just to take the ip in cmdb as null for windows because we can've multiple ips for windows workstations for the same host so that's why I don't want to use cmdb ip address to join with qualys ip address for windows, only want to use cmdb hostname to join with qualys hostname. But for nix category I want to use both cmdb ip address and hostname to join with qualys ip and hostname.

```cmdb ip and hostnames```
| eval ip=if(category="windows",null(),ip)
| join max=0 type=LEFT host_name
[```Qualys hostnames```]
| join max=0 type=left ip
[```Qualys ip```]

@ITWhisperer