How to use the second index to search missing fields

[NewGhost]

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.

[NewGhost]

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.

[NewGhost]

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.

[gcusello · Accepted solution]

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

[gcusello]

Hi @NewGhost,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉