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