Splunk Search

Column not appending to first search

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 REACTIVEWARNING 13.09172019 NON SYNCHRONIZED
6346 3618 REACTIVE
WARNING 13.09172019 NON SYNCHRONIZED
1081 3628 REACTIVEWARNING 35.09182019 NON SYNCHRONIZED
1081 93 REACTIVE
WARNING 09.09182019 NON SYNCHRONIZED
6792 5174 REACTIVEWARNING 27.09182019 NON SYNCHRONIZED
2190 9602 REACTIVE
WARNING 102.09182019 NON SYNCHRONIZED

Second search(these MODECHANGE events can be several weeks old so I am searching for the last MODECHANGE 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
EXPLICITCONTROL 1040
STOPPED 1081
EXPLICIT
CONTROL 1170
EXPLICITCONTROL 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

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" ("REACTIVEEVENT" OR "REACTIVEWARNING")
| 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() MODECHANGE
| rename m:CONTENT.m:DISTRICT
NAME as xDist, m:CONTENT.m:BOSINSTANCEMODE as xmode
| lookup subDivLookup.csv SubDivName as xDist OUTPUT DivID 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

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" ("REACTIVEEVENT" OR "REACTIVEWARNING")
| rename Message{}.target.div as Div, Message{}.Address as Address, Message{}.Type as Type, Message{}.Id as ID,
| table Div Address Type ID
| eval eventtime=time
| join outer [search index=a sourcetype=Office earliest=-30d latest=$event_time$ MODECHANGE
| rename m:CONTENT.m:DISTRICT
NAME as xDist, m:CONTENT.m:BOSINSTANCEMODE as xmode
| lookup subDivLookup.csv SubDivName as xDist OUTPUT DivID as Div
| stats latest(xmode) as latestMode by Div
| table latestMode Div]
| table Address Div Type ID latestMode
| sort +Div

0 Karma

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" ("REACTIVEEVENT" OR "REACTIVEWARNING")
| 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() MODECHANGE
| rename m:CONTENT.m:DISTRICT
NAME as xDist, m:CONTENT.m:BOSINSTANCEMODE as xmode
| lookup subDivLookup.csv SubDivName as xDist OUTPUT DivID 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