sub-search and then join with another search

[CryoHydra]

Hi,

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

1. sub search :

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

2. 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

3. 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

[niketn]

@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?

[CryoHydra]

@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)

1. 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.

[logloganathan]

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]

[CryoHydra]

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

[somesoni2]

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.

[CryoHydra]

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 ?

[somesoni2]

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?

[CryoHydra]

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.