Splunk Search

Column not appending to first search

l0gik
Explorer

I have read a lot of similar questions to mine but I still can't get the results to work as needed. I have two searches and I simply want to append a single column to the primary search based on a value from the second search. The first search will return many events for per each "district" the second search will display what "mode" the district is in.

First search:

index=a sourcetype=messages Message{}.messageNumber=*2083 "Message{}.target.description"="NON SYNCHRONIZED" ("REACTIVE_EVENT" OR "REACTIVE_WARNING")
| rename Message{}.target.div as Div, 
Message{}.Address as Address,
Message{}.Type as Type, 
Message{}.Id as ID, 
Message{}.target.description as Description
| table Div Address Type ID Description

This gives me values like this:
Div Address Type ID Description
6346 3618 REACTIVE_WARNING 13.09172019 NON SYNCHRONIZED
6346 3618 REACTIVE_WARNING 13.09172019 NON SYNCHRONIZED
1081 3628 REACTIVE_WARNING 35.09182019 NON SYNCHRONIZED
1081 93 REACTIVE_WARNING 09.09182019 NON SYNCHRONIZED
6792 5174 REACTIVE_WARNING 27.09182019 NON SYNCHRONIZED
2190 9602 REACTIVE_WARNING 102.09182019 NON SYNCHRONIZED

Second search(these MODE_CHANGE events can be several weeks old so I am searching for the last MODE_CHANGE event per DIV and then converting the name of it to a common DIV number using the lookup):

index=a sourcetype=Office earliest=-30d latest=now() MODE_CHANGE
| rename m:CONTENT.m:DISTRICT_NAME as xDist, 
m:CONTENT.m:BOS_INSTANCE_MODE as xmode
| lookup subDivLookup.csv Div_Name as xDist OUTPUT Div_ID as Div 
| stats latest(xmode) as latestMode by Div
| table latestMode Div

This gives me the following results:
latestMode Div
EXPLICIT_CONTROL 1040
STOPPED 1081
EXPLICIT_CONTROL 1170
EXPLICIT_CONTROL 1171
EXPLICIT_CONTROL 1182

So from here I just want to append the "latestMode" column to the first search based on the DIV ID matching. Here is the search I am using:

index=a sourcetype=messages Message{}.messageNumber=*2083 "Message{}.target.description"="NON SYNCHRONIZED" ("REACTIVE_EVENT" OR "REACTIVE_WARNING")
| rename Message{}.target.div as Div, 
Message{}.Address as Address,
Message{}.Type as Type, 
Message{}.Id as ID, 
| table Div Address Type ID 

| appendcols [search sourcetype=Office earliest=-30d latest=now() MODE_CHANGE
| rename m:CONTENT.m:DISTRICT_NAME as xDist, 
m:CONTENT.m:BOS_INSTANCE_MODE as xmode
| lookup subDivLookup.csv SubDiv_Name as xDist OUTPUT Div_ID as Div 
| stats latest(xmode) as latestMode by Div
| table latestMode Div]

| table Address Div Type ID latestMode
| sort +Div

I am looking for a resulting table that will look like the results of the first query plus the "latestMode" of each Div. From here I will add a WHERE clause to only display latestMode events of EXPLICIT_CONTROL. Currently my results looks exactly like the first search with a "latestMode" column that is always empty. I don't know if the DIV id isn't mapping correctly between the two searches or what.

Thanks for any help!!

Tags (3)
0 Karma
1 Solution

l0gik
Explorer

I found that my first issue was that I needed to include the index in the appendcols search. From there I received results but not a value in each column for the primary search. I then had to change the subsearch to an outer join and it finally worked. The search that I used, in case it helps others:

index=a sourcetype=messages Message{}.messageNumber=*2083 "Message{}.target.description"="NON SYNCHRONIZED" ("REACTIVE_EVENT" OR "REACTIVE_WARNING")
| rename Message{}.target.div as Div, Message{}.Address as Address, Message{}.Type as Type, Message{}.Id as ID,
| table Div Address Type ID
| join outer [search index=a sourcetype=Office earliest=-30d latest=now() MODE_CHANGE
| rename m:CONTENT.m:DISTRICT_NAME as xDist, m:CONTENT.m:BOS_INSTANCE_MODE as xmode
| lookup subDivLookup.csv SubDiv_Name as xDist OUTPUT Div_ID as Div
| stats latest(xmode) as latestMode by Div
| table latestMode Div]
| table Address Div Type ID latestMode
| sort +Div

View solution in original post

0 Karma

l0gik
Explorer

One additional question. How canI dynamically set the latest time search event in the outer join search?

This is what I would assume, but doesn't work. I also see that join has 'usetime' and 'earlier' options but not sure if either achieves what I am looking for.

index=a sourcetype=messages Message{}.messageNumber=2083 "Message{}.target.description"="NON SYNCHRONIZED" ("REACTIVE_EVENT" OR "REACTIVE_WARNING")
| rename Message{}.target.div as Div, Message{}.Address as Address, Message{}.Type as Type, Message{}.Id as ID,
| table Div Address Type ID
| eval event_time=_time
| join outer [search index=a sourcetype=Office earliest=-30d latest=$event_time$ MODE_CHANGE
| rename m:CONTENT.m:DISTRICT_NAME as xDist, m:CONTENT.m:BOS_INSTANCE_MODE as xmode
| lookup subDivLookup.csv SubDiv_Name as xDist OUTPUT Div_ID as Div
| stats latest(xmode) as latestMode by Div
| table latestMode Div]
| table Address Div Type ID latestMode
| sort +Div

0 Karma

l0gik
Explorer

I found that my first issue was that I needed to include the index in the appendcols search. From there I received results but not a value in each column for the primary search. I then had to change the subsearch to an outer join and it finally worked. The search that I used, in case it helps others:

index=a sourcetype=messages Message{}.messageNumber=*2083 "Message{}.target.description"="NON SYNCHRONIZED" ("REACTIVE_EVENT" OR "REACTIVE_WARNING")
| rename Message{}.target.div as Div, Message{}.Address as Address, Message{}.Type as Type, Message{}.Id as ID,
| table Div Address Type ID
| join outer [search index=a sourcetype=Office earliest=-30d latest=now() MODE_CHANGE
| rename m:CONTENT.m:DISTRICT_NAME as xDist, m:CONTENT.m:BOS_INSTANCE_MODE as xmode
| lookup subDivLookup.csv SubDiv_Name as xDist OUTPUT Div_ID as Div
| stats latest(xmode) as latestMode by Div
| table latestMode Div]
| table Address Div Type ID latestMode
| sort +Div

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