topic Re: Left JOIN using two searches in Splunk Search

Left JOIN using two searches

davidcraven02 — Wed, 20 Dec 2017 09:51:33 GMT

I have these two searches below and I want to join the fieldname Path from the first query to the second query using the machine as the unique identifier.

Search 1-

 index=ad source=otl_addnsscan name=*nas* type=CNAME NOT ( name=*.opt.com OR name=*app*) 
        | rex field=data "^(?<machine>[^.]+).*$" 
        | eval  machine = lower(machine) 
        | search NOT  machine=*app* 
        |eval name=lower(machine)
        | table  machine, Path
    | search NOT 
        [ search index=summary report=jira_serverrequests Component/s=*Momom* 
        | eval machine=lower('Server Name') 
        | table machine]
    |dedup  machine
    | sort  machine asc

Search 2 -

index=windows host=*nas* source=WMI:Shares 
| rename host as name 
|eval name=lower(machine)
| dedup name, Path, Caption | table name, Path, Caption

Re: Left JOIN using two searches

harsmarvania57 — Wed, 20 Dec 2017 09:57:51 GMT

Hi,

Please try this below query.

index=ad source=otl_addnsscan name=*nas* type=CNAME NOT ( name=*.options-it.com OR name=*app*) 
         | rex field=data "^(?<machine>[^.]+).*$" 
         | eval  machine = lower(machine) 
         | search NOT  machine=*app* 
         |eval name=lower(machine)
         | table  machine, Path
     | search NOT 
         [ search index=summary report=jira_serverrequests Component/s=*Momom* 
         | eval machine=lower('Server Name') 
         | table machine]
     |dedup  machine
     | sort  machine asc
| join type=left machine [ search index=windows host=*nas* source=WMI:Shares 
 |eval machine=lower(host)
 | dedup machine, Path, Caption | table machine, Path, Caption ]

Re: Left JOIN using two searches

davidcraven02 — Wed, 20 Dec 2017 10:14:10 GMT

Thank you this returns close to what I'm looking for.

However most machines have multiple different paths for each but this is only returning one path per machine. Any thoughts?

Re: Left JOIN using two searches

harsmarvania57 — Wed, 20 Dec 2017 10:20:24 GMT

Will you please try this? I am assuming that you have multiple paths in 2nd query.

    index=ad source=otl_addnsscan name=*nas* type=CNAME NOT ( name=*.options-it.com OR name=*app*) 
              | rex field=data "^(?<machine>[^.]+).*$" 
              | eval  machine = lower(machine) 
              | search NOT  machine=*app* 
              |eval name=lower(machine)
              | table  machine, Path
          | search NOT 
              [ search index=summary report=jira_serverrequests Component/s=*Momom* 
              | eval machine=lower('Server Name') 
              | table machine]
          |dedup  machine
          | sort  machine asc
     | join type=left machine [ search index=windows host=*nas* source=WMI:Shares 
      |eval machine=lower(host)
      | dedup machine, Path, Caption 
      | stats values(Path) AS Path, values(Caption) AS Caption by machine
      | table machine, Path, Caption ]
     | mvexpand Path

Re: Left JOIN using two searches

davidcraven02 — Wed, 20 Dec 2017 10:23:32 GMT

Thank you very much this pulls back all paths in the one row!

Sorry to be a pest but is there any way to have each new path as a new row in the table rather than contained in one line?

Re: Left JOIN using two searches

harsmarvania57 — Wed, 20 Dec 2017 10:31:27 GMT

To expand each Path in new row, I have given | mvexpand Path in last query. Didn't that worked ?

Re: Left JOIN using two searches

davidcraven02 — Wed, 20 Dec 2017 10:35:33 GMT

No unfortunately it didn't work, all paths are listed under Path and the same for Caption

Re: Left JOIN using two searches

harsmarvania57 — Wed, 20 Dec 2017 10:51:04 GMT

Can you please try below query but this will give you multiple result for single host. For xample you have 2 path and 2 caption for single host then it will generate 4 row in table (1st Path with both Caption so 2 events and 2nd Path with both Caption so another 2 events).

index=ad source=otl_addnsscan name=*nas* type=CNAME NOT ( name=*.options-it.com OR name=*app*) 
               | rex field=data "^(?<machine>[^.]+).*$" 
               | eval  machine = lower(machine) 
               | search NOT  machine=*app* 
               |eval name=lower(machine)
               | table  machine, Path
           | search NOT 
               [ search index=summary report=jira_serverrequests Component/s=*Momom* 
               | eval machine=lower('Server Name') 
               | table machine]
           |dedup  machine
           | sort  machine asc
| join type=left machine [ search index=windows host=*nas* source=WMI:Shares 
       |eval machine=lower(host)
       | dedup machine, Path, Caption 
       | stats values(Path) AS Path, values(Caption) AS Caption by machine
       | table machine, Path, Caption ]
| makemv delim=" " Path
| makemv delim=" " Caption
| mvexpand Path
| mvexpand Caption

Re: Left JOIN using two searches

davidcraven02 — Wed, 20 Dec 2017 12:47:00 GMT

Many thanks, I was able to use an append instead of a left join.

I really appreciate your help!