topic sub-search and then join with another search in Splunk Search

sub-search and then join with another search

CryoHydra — Thu, 24 May 2018 12:08:50 GMT

Hi,

had facing issues in using join command , i have two search (sub-search, search)which needs to be joined together.

sub search :

"C:\Windows\System32\windows.exe" sourcetype=windows
[search sourcetype=scanner
| dedup FQDN
| rename FQDN as ComputerName]
|table Process ComputerName
search

sourcetype=scanner
| dedup FQDN
|rename FQDN as ComputerName
|convert ctime(_time) as time
|stats earliest(time) as et latest(time) as lt by ComputerName IP status
Join both sub-search and search using ComputerName key
```
"C:\Windows\System32\windows.exe" sourcetype=windows
[search sourcetype=scanner 
| dedup FQDN 
| rename FQDN as ComputerName
| table ComputerName] 
```
|table Process ComputerName
|join type=outer ComputerName
[search sourcetype=scanner
| dedup FQDN
|rename FQDN as ComputerName
|convert ctime(_time) as time
|stats earliest(time) as et latest(time) as lt by ComputerName IP status]
ComputerName is extracted from the sourcetype=scanner which is used in both sub-search search but tabling of fields not happening as expected ? where i am making mistake

Re: sub-search and then join with another search

somesoni2 — Thu, 24 May 2018 16:34:18 GMT

Whats the expected output? Joins are expensive in terms of resources, so if you could tell your expected output (columns in the output, number of rows after join, any sample mock output etc), we can suggest you some efficient workarounds.

Re: sub-search and then join with another search

CryoHydra — Fri, 25 May 2018 02:19:10 GMT

Thank you.

from sourcetype=scanner taken vulnerable system-name which do input lookup against sourcetype=windows to get running process.

then table ComputerName Process

However i do want to extract other fields from sourcetype=scanner like IP time status which needs to be joined to the above table ?

Is there any other smart way to achieve this ?

Re: sub-search and then join with another search

somesoni2 — Fri, 25 May 2018 15:17:43 GMT

Assuming there will be multiple entries for a ComputerName from sourcetype=windows (say one for each process), and multiple IPs/status/time for ComputerName in sourcetype=scanner, should your final table show many to many join? E.g. if there are two processes running on a ComputerName (2 rows returned from sourcetype=windows) and it changed status/IP twice (2 rows returned from sourcetype=scanner) how many rows should your final table contain? 2-one for each Process/IP-Status OR 4?

Re: sub-search and then join with another search

CryoHydra — Sat, 26 May 2018 04:00:14 GMT

Yeah your inference is right but join not at all happening resulting table appear more like append table.

I even did dedup ComputerName for the sourcetype=windows.

Re: sub-search and then join with another search

logloganathan — Sat, 26 May 2018 05:40:34 GMT

Please try this query if this work then we work on your target step by step
C:\Windows\System32\windows.exe" sourcetype=windows
[search sourcetype=scanner
| dedup FQDN
| rename FQDN as ComputerName
| table ComputerName]
|table Process ComputerName
|join type=outer ComputerName
[search sourcetype=scanner
"ComputerName"
|convert ctime(_time) as time
|stats earliest(time) as et latest(time) as lt by ComputerName]

Re: sub-search and then join with another search

CryoHydra — Sat, 26 May 2018 06:50:13 GMT

it won't work since the key "ComputerName" is not present as either value or field on sourcetype=scanner ? how does it work.

Re: sub-search and then join with another search

niketn — Sat, 26 May 2018 07:12:35 GMT

@CryoHydra I think you have misunderstood the purpose of subsearch with table command [search ..... | table <returnField>]. In the sourcetype windows, your subsearch will add filter for ComputerName field with all the values matching in sourcetype scanner. There is a way you can search for just the values (as search pattern) or search for a different fieldname with the values. However, the approach will be dependent on what are existing fieldnames and values and what is the common field/value in both the sourcetypes windows and scanner that you want to join the result on?

Similar to how foreign keys work to correlate two tables, you need something common in both the sourcetypes. Can you provide us with this detail?

Re: sub-search and then join with another search

CryoHydra — Sun, 27 May 2018 02:07:51 GMT

@niketnilay Thank you.

Common values in both sourcetype is hostname however they have different field name (for windows it is ComputerName, for scanner FQDN so i renamed it to ComputerName)

sub search extract Process field from the sourcetype=windows for the filtered ComputerName from sourcetype=scanner however i need to join the missing field from the sourcetype=scanner to extracted process and ComputerName field.