How to display 2 fields from different sources into a table?

[charlottelimcl]

Hi all,

I have the following query:

index=wineventlog source=wineventlog:security EventCode=4688
[search index=wineventlog source=wineventlog:security EventCode=4663 Object_Name="*hello.exe" Process_Name="*welcome.exe"
| fields Account_Name Process_Name 
| rename Process_Name as New_Process_Name]
| table _time ComputerName Account_Name New_Process_Name Initiating_Process_Name 

EventCode=4663 has a field called Object_Name, while EventCode=4688 does not. My end result is that I want to display a table to show the Object_Name column alongside with New_Process_Name and Initiating_Process_Name.

The above query identifies the Account_Name and New_Process_Name (of the subsearch) and is fed into the main search to identify the Initiating_Process_Name. I want to be able to include the Object_Name from EventCode=4663 into this table as well. How can i do it?

[PickleRick]

OK. That might be simple, but not easy.

But firstly, let's dig a bit into your search.

It contains a subsearch. A subsearch is executed first and rendered into a set of conditions which are inserted into the outer search. So there is no way to "relay" additional fields into the results. As simple as that. So you need another way (most probably some stats-based solution like the one shown by @gcusello ).

But.

Remember that subsearch has its limitations and at this moment you might actually not be getting correct results (even ignoring the lack of additional fields). The subsearch will get silently finalized after reaching execution timeout (by default it's 60 seconds) or results number (by default - 10k) and you will not be notified about this in any way. So you might actually be getting incomplete results without knowing it.

OK. Back to the original issue.

You have two data sets. One is produced by

index=wineventlog source=wineventlog:security EventCode=4688

Another one by

index=wineventlog source=wineventlog:security EventCode=4663 Object_Name="*hello.exe" Process_Name="*welcome.exe"

As a side note, let me point out that searching for terms like "*hello.exe" and "*welcome.exe" is very inefficient since Splunk cannot use its internal indexes of terms to find those ones so it has to parse all events matching other conditions. If you can avoid it, don't use wildcards at the beginning of the search term.

So while the general approach of searching for

(index=wineventlog source=wineventlog:security EventCode=4688) OR (index=wineventlog source=wineventlog:security EventCode=4663 Object_Name="*hello.exe" Process_Name="*welcome.exe")

Which can be  simplified to

index=wineventlog source=wineventlog:security (EventCode=4688 OR (EventCode=4663 Object_Name="*hello.exe" Process_Name="*welcome.exe"))

And then doing

| stats values(field1) values(field2) <...> by commonfield1 commonfield2 <...>

is sound and is the way to go in general and if it's slow, it's probably due to

a) Amount of data you have to process

b) The wildcarded search terms. If you can narrow it, it would be much much more efficient.

Just for a test, try to search for

index=wineventlog source=wineventlog:security EventCode=4663 Object_Name="*hello.exe" Process_Name="*welcome.exe"

alone (maybe pass it to | stats count so that you don't have to drag all those events around; just check how long it takes to dig through the index).

If it takes long, it means your original search (the one with the subsearch) was simply getting finalized early.

[kiran_panchavat]

@charlottelimcl 

Check this :- I have used makeresults command for dummy. 

| makeresults
| eval _raw="
_time,ComputerName,Account_Name,EventCode,Object_Name,Process_Name
2023-10-27 10:00:00,PC1,user1,4688,,/path/to/parent.exe
2023-10-27 10:00:01,PC1,user1,4663,/path/to/hello.exe,/path/to/welcome.exe
2023-10-27 10:01:00,PC2,user2,4688,,/path/to/another.exe
2023-10-27 10:01:02,PC2,user2,4663,/path/to/goodbye.exe,/path/to/start.exe
2023-10-27 10:02:00,PC3,user3,4688,,/path/to/third.exe
2023-10-27 10:02:03,PC3,user3,4663,/path/to/final.exe,/path/to/launch.exe
"
| multikv forceheader=1
| eval _time=strptime(_time,"%Y-%m-%d %H:%M:%S")
| stats 
    earliest(_time) AS _time
    values(ComputerName) AS ComputerName
    values(eval(if(EventCode=4663, Process_Name, ""))) AS New_Process_Name
    values(eval(if(EventCode=4688, Process_Name, ""))) AS Initiating_Process_Name
    values(eval(if(EventCode=4663, Object_Name, ""))) AS Object_Name
BY Account_Name
| table _time ComputerName Account_Name New_Process_Name Initiating_Process_Name Object_Name

In this example:

• makeresults generates dummy events.
• eval creates the raw data with the necessary fields.
• multikv parses the raw data into individual fields.
• stats aggregates the data as per your requirements.

[charlottelimcl]

Many thanks for your reply. Maybe I could add some clarity to the exact results I want:

First inner search:

index=wineventlog source=wineventlog:security EventCode=4663 Object_Name="*hello.exe" Process_Name="*welcome.exe" 
| table _time ComputerName Object_Name Process_Name

_time	ComputerName	Object_Name	Process_Name
2025-03-19 12:00:00	ABCDE	\ABC\hello.exe	welcome.exe

Next, when I search EventCode=4688, this is a sample search and outcome:

index=wineventlog source=wineventlog:security EventCode=4688 Process_Name="*welcome.exe" 
| table _time ComputerName Process_Name Initiating_Process_Name​

_time	ComputerName	Process_Name	Initiating_Process_Name
2025-03-19 12:00:00	ABCDE	welcome.exe	cmd.exe

WHAT I WANT: 

I want to feed this into the next search in EventCode=4688 to identify the Process Name and subsequently linking to the Initiating_Process_Name that appear as a result of the above search, i.e.

Final outcome I want:

_time	ComputerName	Object_Name	Process_Name	Initiating_Process_Name
2025-03-19 12:00:00	ABCDE	\ABC\hello.exe	welcome.exe	cmd.exe

The issue is, EventCode=4688 only has Process_Name and Initiating_Process_Name and NO Object_Name, while EventCode=4663 only has Object_Name and Process_Name and NO Initiaitng_Process_Name.

The common linkingfactor would be the Process_Name to correlate this two events together.

How can i do this? 

[gcusello]

Hi @charlottelimcl ,

you have to correlate events using stats:

index=wineventlog source=wineventlog:security EventCode IN (4663,4688) Process_Name="*welcome.exe" 
| stats 
     earliest(_time) AS _time 
     values(Object_Name) AS Object_Name
     BY ComputerName Process_Name
| table _time ComputerName Process_Name Initiating_Process_Name​

Ciao

Giuseppe

[charlottelimcl]

i tried entering this with a slight tweak to the query:

index=wineventlog source=wineventlog:security EventCode IN (4663,4688) Process_Name="*welcome.exe" 
| stats 
     earliest(_time) AS _time 
     values(Object_Name) AS Object_Name
     BY ComputerName Process_Name
| table _time ComputerName Object_Name Process_Name Initiating_Process_Name​

, however this is my result:

_time	ComputerName	Object_Name	Process_Name	Initiating_Process_Name
2025-03-19 16:00	ABCDE	object.exe	welcome.exe	(blank)

I am still not able to get all 3 columns (object name, process name, initiating process name) into the same table.

[PickleRick]

If you do 

| stats 
     earliest(_time) AS _time 
     values(Object_Name) AS Object_Name
     BY ComputerName Process_Name

You only have _time, Object_Name, ComputerName and Process_Name fields as output. Adding non-existing field in table command doesn't magically populate its contents.

You need to add Initiating_Proces_Name either as aggregation with values() or as the BY field.

The table command, BTW, is not needed after this stats.

[gcusello]

Hi @charlottelimcl ,

subsearch is used only to filter results from the main search using the results of the subsearch, you instead need a join, but, please, aviud to use the join command because it's very slow and resource consuming.

You could use a solution like the following:

index=wineventlog source=wineventlog:security (EventCode=4688 OR (EventCode=4663 Object_Name="*hello.exe" Process_Name="*welcome.exe"))
| stats 
     earliest(_time) AS _time
     values(ComputerName) AS ComputerName
     values(eval(if(EventCode=4663,Process_Name,"") AS New_Process_Name   
     values(eval(if(EventCode=4688,Process_Name,"") AS Initiating_Process_Name
     BY Account_name

 You should adapt this approach to your requirements.

Ciao.

Giuseppe

[charlottelimcl]

Hi @gcusello , thanks for your advise. I tried running the search below but it takes quite a long time to show results. Furthermore the query does not display Object_Name as needed

[gcusello]

Hi @charlottelimcl ,

about Object_Name, please use this:

index=wineventlog source=wineventlog:security (EventCode=4688 OR (EventCode=4663 Object_Name="*hello.exe" Process_Name="*welcome.exe"))
| stats 
     earliest(_time) AS _time
     values(ComputerName) AS ComputerName
     values(Object_Name) AS Object_Name
     values(eval(if(EventCode=4663,Process_Name,"") AS New_Process_Name   
     values(eval(if(EventCode=4688,Process_Name,"") AS Initiating_Process_Name
     BY Account_name

About the time occurring for the execution, this is the more performant way to create a search, if you try with join you'll have a more longer time for the execution.

To optimize the search, you should try some acceleration method https://docs.splunk.com/Documentation/Splunk/9.4.1/Knowledge/Aboutsummaryindexing or https://docs.splunk.com/Documentation/Splunk/9.4.1/Knowledge/Acceleratetables  or use a Data Model https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Aboutdatamodels

Ciao.

Giuseppe