Left JOIN using two searches

[davidcraven02]

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 

[harsmarvania57 · Accepted solution]

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 ]

[davidcraven02]

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?

[harsmarvania57]

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

[davidcraven02]

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?

[harsmarvania57]

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

[davidcraven02]

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

[harsmarvania57]

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

[davidcraven02]

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

I really appreciate your help!