Splunk Search

How to use the second index to search missing fields

NewGhost
Engager

Please see this search - i'm trying to add missing field values from another index to this search.

 

index=1 earliest=-9d latest=now ExternalApiType=Event_DetectionSummaryEvent

| fillnull

| stats values(ComputerName) AS ComputerName values(DetectName) AS DetectName values(UserName) AS User values(event_platform) AS Platform values(FileVersion) AS SensorVersion P values(MachineDn) AS OU values(SiteName) AS SiteName count(_time) AS count BY _time EventUUID

| sort 0 - _time

| eval Time=strftime(_time, "%m/%d/%Y %H:%M:%S")

| appendcols

[ search earliest=-9d latest=now index=json "AuditKeyValues{}.Key"=new_state "AuditKeyValues{}.ValueString"=*

| spath

| spath AuditKeyValue{}
]

 

Index=1 has fields ComputerName, DetectName, UserName, _time, EventUUID

index=main has fields event_platform, FileVersion, MachineDn, SiteName

 

I want to pull the fields from index=main into the stats command of the index=1. I thought  it's as simple as adding the index=main at the beginning of the search with an OR:

(index=json ExternalApiType=Event_DetectionSummaryEvent) OR (index=main FileVersion=*).

But it's not working. I have to have the ExternalApiType value and it's only in the first index. I also tried join with the subsearch, but it didn't work. The original search is for 90 days, so I shouldn't use a subsearch anyways.

Thank you.

Labels (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @NewGhost,

to correlate events from different indexes you need a correlation key, is there a field (eventually with different name) to use for correlation.

e.g. in your searches: ComputerName from search1 and MachineDn from search2 have the same values?

Then, in the first search, if you use _time as key in stats command, it's better to use bin command before, otherwise you'll have too many results.

About join, I use it only when there isn't any other solution becasue it's a very slow command, but anyway, it still need a correlation key, otherwise it isn't possible to correlate events.

Ciao.

Giuseppe

View solution in original post

0 Karma

NewGhost
Engager

I realize my question is too confusing and I probably need to test my queries some more. I just tried to understand how to do the join correctly, and I think if I only want certain fields to show in the end I just put |table at the end.

0 Karma

NewGhost
Engager

They both have ComputerName field. I'll try using the bin too, thanks. My ultimate goal is to have the number of detections for ComputerNames over time, so I'll probably have to use the timechart, but I'm not even there yet. This was just me starting to combine all the information together.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @NewGhost,

to correlate events from different indexes you need a correlation key, is there a field (eventually with different name) to use for correlation.

e.g. in your searches: ComputerName from search1 and MachineDn from search2 have the same values?

Then, in the first search, if you use _time as key in stats command, it's better to use bin command before, otherwise you'll have too many results.

About join, I use it only when there isn't any other solution becasue it's a very slow command, but anyway, it still need a correlation key, otherwise it isn't possible to correlate events.

Ciao.

Giuseppe

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @NewGhost,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...