How to use map to identify added or removed node names between current and previous events
#
# Search Report
# map is used for this
# join cannot do this
#
I have logs which lists problematic nodes every day. I would like to create two reports from the logs.
One is to check how many problematic nodes were reported and the total number of nodes were increased or decreased
from the previous day. Other report is to list which nodes were added or removed from the previous day.
#
# Re-phrasing what I want to do
#
What we would like to achieve
For example,
2013-01-20 07:30:00 problematic nodes: a b c d e f
2013-01-21 07:30:00 problematic nodes: b c d e f g
Target 1: To calculate change of total number of nodes
Table columns for Target 1 ( Timestamp, Total_Count_of_Nodes, Difference_from_Previous_Record )
Target 2: To list that "a" was removed and "g" was added.
Table columns for Target 2 ( Timestamp, List_Added_Nodes, List_Removed_Nodes )
#
# Sample logs
# file: sample_node_list.log
# Event is a daily summary of problematic nodes
# sourcetype="daily_monitor_nodes"
- sample_node_list.log
2013-01-28T07:30:01.849479-05:00 problem1:esx101-n24 esx108-n44 esx110-n6 esx110-n70
2013-01-29T07:30:01.849479-05:00 problem1:esx101-n24 esx101-n91 esx102-n3 esx102-n59
2013-01-30T07:30:01.849479-05:00 problem1:esx101-n24 esx101-n91 esx102-n3 esx102-n59 esx102-n82 esx102-n83
2013-01-31T07:30:01.849479-05:00 problem1:esx101-n24 esx101-n91 esx102-n3 esx102-n59 esx102-n82 esx102-n83 esx103-n2 esx103-n3 esx103-n36
2013-02-01T07:30:01.849479-05:00 problem1:esx101-n24 esx108-n44 esx110-n6 esx110-n70
2013-02-02T07:30:01.849479-05:00 problem1:esx101-n24 esx108-n4 esx108-n11 esx108-n17 esx108-n18 esx108-n44 esx110-n6 esx110-n70
2013-02-03T07:30:01.849479-05:00 problem1:esx101-n24 esx107-n71
2013-02-04T07:30:01.849479-05:00 problem1:esx101-n24 esx106-n62
2013-02-05T07:30:01.849479-05:00 problem1:esx101-n24 esx101-n91 esx102-n3 esx102-n59 esx106-n71 esx107-n59 esx107-n71
2013-02-06T07:30:01.849479-05:00 problem1:esx102-n59 esx106-n71 esx107-n59 esx107-n71
2013-02-07T07:30:01.849479-05:00 problem1:esx101-n24 esx106-n71 esx107-n59 esx107-n71 esx107-n81 esx108-n1 esx108-n3 esx108-n4 esx108-n11 esx108-n17 esx108-n18 esx108-n44 esx110-n6 esx110-n70
2013-02-08T07:30:01.849479-05:00 problem1:esx101-n24 esx106-n71 esx107-n59 esx107-n71 esx107-n81 esx108-n1 esx108-n3 esx108-n4 esx108-n11 esx108-n17 esx108-n18 esx108-n44 esx110-n6 esx110-n70
1. Index the sample file
# ./bin/splunk add oneshot ~/Log/sample_node_list.log -sourcetype daily_monitor_nodes -host vcenter1000 -auth admin:changeme
Oneshot '/home/masa/Log/sample_node_list.log' added
2. Search query for the target 1
# ./bin/splunk search '
sourcetype="daily_monitor_nodes"
| rex "problem1:(?<nodes>.*)"
| makemv delim=" " nodes
| timechart span=1d dc(nodes) AS Total_Count_Of_Nodes
| delta Total_Count_Of_Nodes AS Diff'
_time Total_Count_Of_Nodes Diff
--------------------------- -------------------- ----
2013-01-28 00:00:00.000 PST 4
2013-01-29 00:00:00.000 PST 4 0
2013-01-30 00:00:00.000 PST 6 2
2013-01-31 00:00:00.000 PST 9 3
2013-02-01 00:00:00.000 PST 4 -5
2013-02-02 00:00:00.000 PST 8 4
2013-02-03 00:00:00.000 PST 2 -6
2013-02-04 00:00:00.000 PST 2 0
2013-02-05 00:00:00.000 PST 7 5
2013-02-06 00:00:00.000 PST 4 -3
2013-02-07 00:00:00.000 PST 14 10
2013-02-08 00:00:00.000 PST 14 0
3. Search query for the target 2
########################################
# 3-a: Pre-table before the final table
########################################
#
# All Nodes Status (Added:+1, Removed:-1, NoChange:0 ) As delta
# - map to run delta for each node (Note: join command cannot achieve this)
# - After that, table command to select only _time and $nodes$ field
# - Without maxsearches option, only default 10 node values are used
# ==> WARNING message: The search result count (26) exceeds maximum (10), using max. To override it, set maxsearches appropriately.
#
# ./bin/splunk search '
sourcetype="daily_monitor_nodes"
| rex "problem1:(?<nodes>.*)"
| makemv delim=" " nodes
| stats count by nodes
| table nodes
| map [ search sourcetype="daily_monitor_nodes"
| rex "problem1:(?<nodes>.*)"
| makemv delim=" " nodes
| mvexpand nodes
| timechart count by nodes limit=0
| table _time $nodes$
| delta $nodes$ as $nodes$ ] maxsearches=50
| stats first(*) AS * by _time
| fillnull value=0'
# The following output used maxsearches=8
# ./bin/splunk search '
sourcetype="daily_monitor_nodes"
| rex "problem1:(?<nodes>.*)"
| makemv delim=" " nodes
| stats count by nodes
| table nodes
| map [ search sourcetype="daily_monitor_nodes"
| rex "problem1:(?<nodes>.*)"
| makemv delim=" " nodes
| mvexpand nodes
| timechart count by nodes limit=0
| table _time $nodes$
| delta $nodes$ as $nodes$ ] maxsearches=8
| stats first(*) AS * by _time
| fillnull value=0'
_time esx101-n24 esx101-n91 esx102-n3 esx102-n59 esx102-n82 esx102-n83 esx103-n2 esx103-n3
--------------------------- ---------- ---------- --------- ---------- ---------- ---------- --------- ---------
2013-01-28 00:00:00.000 PST 1 0 0 0 0 0 0 0
2013-01-29 00:00:00.000 PST 0 1 1 1 0 0 0 0
2013-01-30 00:00:00.000 PST 0 0 0 0 1 1 0 0
2013-01-31 00:00:00.000 PST 0 0 0 0 0 0 1 1
2013-02-01 00:00:00.000 PST 0 -1 -1 -1 -1 -1 -1 -1
2013-02-02 00:00:00.000 PST 0 0 0 0 0 0 0 0
2013-02-03 00:00:00.000 PST 0 0 0 0 0 0 0 0
2013-02-04 00:00:00.000 PST 0 0 0 0 0 0 0 0
2013-02-05 00:00:00.000 PST 0 1 1 1 0 0 0 0
2013-02-06 00:00:00.000 PST -1 -1 -1 0 0 0 0 0
2013-02-07 00:00:00.000 PST 1 0 0 -1 0 0 0 0
2013-02-08 00:00:00.000 PST 0 0 0 0 0 0 0 0
########################################
# 3-b: Final table for target 2
########################################
#
# Table of Added nodes and Removed nodes by _time, AddedNodes, RemovedNodes
#
# ./bin/splunk search '
sourcetype="daily_monitor_nodes"
| rex "problem1:(?<nodes>.*)"
| makemv delim=" " nodes
| stats count by nodes
| table nodes
| map [ search sourcetype="daily_monitor_nodes"
| rex "problem1:(?<nodes>.*)"
| makemv delim=" " nodes
| mvexpand nodes
| timechart count by nodes limit=0
| table _time $nodes$
| delta $nodes$ as Changed
| rename $nodes$ AS Existing
| eval Added=if(isnull(Changed), if(Existing == 1, $nodes$, null), if(Changed == 1, $nodes$, null))
| eval Removed=if(Changed == -1, $nodes$, null) ] maxsearches=50
| stats values(Added) AS Added values(Removed) AS Removed by _time
| fillnull value="NoChange"'
_time Added Removed
--------------------------- ---------- ----------
2013-01-28 00:00:00.000 PST esx101-n24 NoChange
esx108-n44
esx110-n6
esx110-n70
2013-01-29 00:00:00.000 PST esx101-n91 esx108-n44
esx102-n3 esx110-n6
esx102-n59 esx110-n70
2013-01-30 00:00:00.000 PST esx102-n82 NoChange
esx102-n83
2013-01-31 00:00:00.000 PST esx103-n2 NoChange
esx103-n3
esx103-n36
2013-02-01 00:00:00.000 PST esx108-n44 esx101-n91
esx110-n6 esx102-n3
esx110-n70 esx102-n59
esx102-n82
esx102-n83
esx103-n2
esx103-n3
esx103-n36
2013-02-02 00:00:00.000 PST esx108-n11 NoChange
esx108-n17
esx108-n18
esx108-n4
2013-02-03 00:00:00.000 PST esx107-n71 esx108-n11
esx108-n17
esx108-n18
esx108-n4
esx108-n44
esx110-n6
esx110-n70
2013-02-04 00:00:00.000 PST esx106-n62 esx107-n71
2013-02-05 00:00:00.000 PST esx101-n91 esx106-n62
esx102-n3
esx102-n59
esx106-n71
esx107-n59
esx107-n71
2013-02-06 00:00:00.000 PST NoChange esx101-n24
esx101-n91
esx102-n3
2013-02-07 00:00:00.000 PST esx101-n24 esx102-n59
esx107-n81
esx108-n1
esx108-n11
esx108-n17
esx108-n18
esx108-n3
esx108-n4
esx108-n44
esx110-n6
esx110-n70
2013-02-08 00:00:00.000 PST NoChange NoChange
