topic Re: How to use the second index to search missing fields in Splunk Search

How to use the second index to search missing fields

NewGhost — Fri, 17 Jun 2022 18:19:59 GMT

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.

Re: How to use the second index to search missing fields

gcusello — Sat, 18 Jun 2022 06:00:40 GMT

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

Re: How to use the second index to search missing fields

NewGhost — Mon, 20 Jun 2022 12:32:00 GMT

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.

Re: How to use the second index to search missing fields

NewGhost — Mon, 20 Jun 2022 14:44:39 GMT

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.

Re: How to use the second index to search missing fields

gcusello — Mon, 20 Jun 2022 14:55:12 GMT

Hi @NewGhost,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉