topic Re: How to extract multiple values for multiple fields within a single event? in Splunk Search

How to extract multiple values for multiple fields within a single event?

cbwillh — Fri, 07 Aug 2020 15:00:53 GMT

I have syslogs from our load balancer which has 4 servers on it.

When one of the servers states changes from UP to DOWN or DOWN to UP it is reported in the syslogs as a string value in an event but sometimes a single event from the same time will contain server state changes for multiple servers. OR a single server but BOTH state change to DOWN and state change to UP.

my issue is that no matter what search I use it never accurately picks up every state change for every server from any event that has multiple messages in it.

Below is a sample of one of my events that has more than one state change:

NOTE I want to extract ALL instances of the following message to a single field

A Loadbalancer Server Status is changed to DOWN

AND/OR

A Loadbalancer Server Status is changed to UP

LOG EXAMPLE:

Aug 6 03:01:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:770 data:{"systemEvents":[{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30302", "message":"A Loadbalancer Server Status is changed to DOWN", "timestamp":1596708060, "metaData":{ "listener" : "Carson_MDCM_Servers", "server" : "Server69" } },{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30301", "message":"A Loadbalancer Server Status is changed to UP", "timestamp":1596708081, "metaData":{ "listener" : "Carson_MDCM_Servers", "server" : "Server69" } },{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30301", "message":"A Loadbalancer Server Status is changed to UP", "timestamp":1596708082, "metaData":{ "listener" : "WT_MDCM_Servers", "server" : "Server81" } }]}

Re: How to extract multiple values for multiple fields within a single event?

spitchika — Fri, 07 Aug 2020 15:43:16 GMT

Please try this Rex

| rex field=_raw max_match=0 "message\":\"(?<TotalMessage>[^\"]+)"
| stats values(TotalMessage)

Re: How to extract multiple values for multiple fields within a single event?

cbwillh — Fri, 07 Aug 2020 16:24:05 GMT

thanks for that spitchika

that works to present the two messages but it only shows them once.

my fault maybe I should have given more details of what I am trying to accomplish.

I have created 3 field extractions with the following field names:

(data I am trying to extract is noted to the right of the field name below)

message (to extract the "message": values: "A Loadbalancer Server Status is changed to DOWN" OR "A Loadbalancer Server Status is changed to UP" entries )

server (to extract the "server" : values: "Server69")

site (to extract the "listener" : values: " Carson_MDCM_Servers" OR "WT_MDCM_Servers")

I want a search to display the results in a table showing the time of the event and the values from the server, site and message fields extracted above.

I am currently using the following search and it does work BUT it is not grabbing every instance of the values listed above. if an event has FOUR instances of the same event with a different site, server and message my search only returns the first instance or sometimes the first two instances but ignores the other two.

so my search is no accurate as it is not parsing all of the occurrence's from a single event when there are three or more of them in a single event from the same time as in my original example.

My Search is currently

sourcetype=syslog_nsxedge host="NSX-Edge03-0" server!="NULL"
|sort - _time
|fieldformat _time = strftime(_time, "%b %d, %Y - %H:%M")
|table _time,server,site,message

the search above returns the following

Aug 07, 2020 - 05:28 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to DOWN
Aug 07, 2020 - 03:01 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to DOWN
Aug 07, 2020 - 02:46 Server62 WT_MDCM_Servers A Loadbalancer Server Status is changed to DOWN
Aug 07, 2020 - 01:51 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to UP
Aug 07, 2020 - 01:50 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to DOWN

it looks correct but when compared against the RAW syslog information it is clear that there are missing events not listed in the table

Re: How to extract multiple values for multiple fields within a single event?

spitchika — Fri, 07 Aug 2020 17:09:04 GMT

Before |Table statement, use "| mvexpand message" if you already captured messages using | rex max_match=0

Re: How to extract multiple values for multiple fields within a single event?

cbwillh — Fri, 07 Aug 2020 17:30:37 GMT

Hello Spitchika

thanks so much for your help.

I tried your suggestion but I get the same results I posted before.

so here is what I have now

search I am using now which is applying your suggestions, please advise if I formatted it correctly.

the resulting table from the search above

The ACTUAL Logs indicate the events for this search time period should are missing the following events

Aug 7 02:46:12 should show the following additional event

Server62 WT_MDCM_Servers A Loadbalancer Server Status is changed to UP

Aug 7 03:01:12 should show the following additional events

Server81 WT_MDCM_Servers A Loadbalancer Server Status is changed to DOWN

Server81 WT_MDCM_Servers A Loadbalancer Server Status is changed to UP

Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to UP

Aug 7 05:28:12 should show the following additional event

Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to UP

actual logs are below

Aug 7 01:50:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:272 data:{"systemEvents":[{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30302", "message":"A Loadbalancer Server Status is changed to DOWN", "timestamp":1596790245, "metaData":{ "listener" : "Carson_MDCM_Servers", "server" : "Server69" } }]}

Aug 7 01:51:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:270 data:{"systemEvents":[{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30301", "message":"A Loadbalancer Server Status is changed to UP", "timestamp":1596790256, "metaData":{ "listener" : "Carson_MDCM_Servers", "server" : "Server69" } }]}

Aug 7 02:46:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:515 data:{"systemEvents":[{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30302", "message":"A Loadbalancer Server Status is changed to DOWN", "timestamp":1596793558, "metaData":{ "listener" : "WT_MDCM_Servers", "server" : "Server62" } },{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30301", "message":"A Loadbalancer Server Status is changed to UP", "timestamp":1596793578, "metaData":{ "listener" : "WT_MDCM_Servers", "server" : "Server62" } }]}

Aug 7 03:01:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:1019 data:{"systemEvents":[{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30302", "message":"A Loadbalancer Server Status is changed to DOWN", "timestamp":1596794458, "metaData":{ "listener" : "Carson_MDCM_Servers", "server" : "Server69" } },{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30302", "message":"A Loadbalancer Server Status is changed to DOWN", "timestamp":1596794458, "metaData":{ "listener" : "WT_MDCM_Servers", "server" : "Server81" } },{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30301", "message":"A Loadbalancer Server Status is changed to UP", "timestamp":1596794468, "metaData":{ "listener" : "WT_MDCM_Servers", "server" : "Server81" } },{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30301", "message":"A Loadbalancer Server Status is changed to UP", "timestamp":1596794478, "metaData":{ "listener" : "Carson_MDCM_Servers", "server" : "Server69" } }]}

Aug 7 05:28:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:523 data:{"systemEvents":[{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30302", "message":"A Loadbalancer Server Status is changed to DOWN", "timestamp":1596803281, "metaData":{ "listener" : "Carson_MDCM_Servers", "server" : "Server69" } },{ "moduleName":"vShield Edge LoadBalancer", "severity":"Informational", "eventCode":"30301", "message":"A Loadbalancer Server Status is changed to UP", "timestamp":1596803291, "metaData":{ "listener" : "Carson_MDCM_Servers", "server" : "Server69" } }]}

Re: How to extract multiple values for multiple fields within a single event?

spitchika — Fri, 07 Aug 2020 17:54:07 GMT

Please try this... Your extracted variable is "TotalMessage". So I changed it in your query

Re: How to extract multiple values for multiple fields within a single event?

cbwillh — Fri, 07 Aug 2020 18:31:25 GMT

Hello Spitchika

you are AWESOME! it is really close but I still have some pieces not showing accurate data.

to clarify your latest suggested search DOES fix and display ALL of the message values correctly.

So I believe you definitely fixed the issue for that field but I seem to have inaccurate data in the other two that needs sorting out.

unfortunately the server and the site table column data is not matching the correct server or site in the event to the NOW CORRECT message in the new TotalMessage column

NEW SEARCH

NEW RESULT (highlighted green = data correct-matches log, red = data does not match log)

Aug 07, 2020 - 05:28 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to DOWN
Aug 07, 2020 - 05:28 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to UP
Aug 07, 2020 - 03:01 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to DOWN

BOTH IN RED BELOW (Server69 should be Server81 & Carson MDCM Servers should be WT_MDCM Servers)
Aug 07, 2020 - 03:01 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to DOWN
Aug 07, 2020 - 03:01 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to UP
Aug 07, 2020 - 03:01 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to UP
Aug 07, 2020 - 02:46 Server62 WT_MDCM_Servers A Loadbalancer Server Status is changed to DOWN
Aug 07, 2020 - 02:46 Server62 WT_MDCM_Servers A Loadbalancer Server Status is changed to UP
Aug 07, 2020 - 01:51 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to UP
Aug 07, 2020 - 01:50 Server69 Carson_MDCM_Servers A Loadbalancer Server Status is changed to DOWN

From the results it seems like the only discrepancy if from the data being parsed from the Aug 7 03:01:12 event which is the only one that contains event values for TWO different Servers (Server69 and Server81)

all of the other events being extracted during the searches timeframes only contain events for a single servername

So it looks like it is not pulling the correct server from the two middle events in the following log

Re: How to extract multiple values for multiple fields within a single event?

to4kawa — Fri, 07 Aug 2020 18:46:00 GMT

sample:

index=_internal |head 1| fields _raw _time | eval _raw="Aug 7 01:50:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:272 data:{\"systemEvents\":[{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30302\", \"message\":\"A Loadbalancer Server Status is changed to DOWN\", \"timestamp\":1596790245, \"metaData\":{ \"listener\" : \"Carson_MDCM_Servers\", \"server\" : \"Server69\" } }]}" | appendpipe [ | eval _raw="Aug 7 01:51:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:270 data:{\"systemEvents\":[{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30301\", \"message\":\"A Loadbalancer Server Status is changed to UP\", \"timestamp\":1596790256, \"metaData\":{ \"listener\" : \"Carson_MDCM_Servers\", \"server\" : \"Server69\" } }]}" | appendpipe [ | eval _raw="Aug 7 02:46:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:515 data:{\"systemEvents\":[{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30302\", \"message\":\"A Loadbalancer Server Status is changed to DOWN\", \"timestamp\":1596793558, \"metaData\":{ \"listener\" : \"WT_MDCM_Servers\", \"server\" : \"Server62\" } },{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30301\", \"message\":\"A Loadbalancer Server Status is changed to UP\", \"timestamp\":1596793578, \"metaData\":{ \"listener\" : \"WT_MDCM_Servers\", \"server\" : \"Server62\" } }]}" | appendpipe [ eval _raw="Aug 7 03:01:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:1019 data:{\"systemEvents\":[{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30302\", \"message\":\"A Loadbalancer Server Status is changed to DOWN\", \"timestamp\":1596794458, \"metaData\":{ \"listener\" : \"Carson_MDCM_Servers\", \"server\" : \"Server69\" } },{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30302\", \"message\":\"A Loadbalancer Server Status is changed to DOWN\", \"timestamp\":1596794458, \"metaData\":{ \"listener\" : \"WT_MDCM_Servers\", \"server\" : \"Server81\" } },{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30301\", \"message\":\"A Loadbalancer Server Status is changed to UP\", \"timestamp\":1596794468, \"metaData\":{ \"listener\" : \"WT_MDCM_Servers\", \"server\" : \"Server81\" } },{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30301\", \"message\":\"A Loadbalancer Server Status is changed to UP\", \"timestamp\":1596794478, \"metaData\":{ \"listener\" : \"Carson_MDCM_Servers\", \"server\" : \"Server69\" } }]}" | appendpipe [ eval _raw="Aug 7 05:28:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:523 data:{\"systemEvents\":[{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30302\", \"message\":\"A Loadbalancer Server Status is changed to DOWN\", \"timestamp\":1596803281, \"metaData\":{ \"listener\" : \"Carson_MDCM_Servers\", \"server\" : \"Server69\" } },{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30301\", \"message\":\"A Loadbalancer Server Status is changed to UP\", \"timestamp\":1596803291, \"metaData\":{ \"listener\" : \"Carson_MDCM_Servers\", \"server\" : \"Server69\" } }]}"]]]] | rex "(?<time>.*)\sNSX-Edge" | eval _time=strptime(time,"%B %d %T") | rex "(?<json>{.*})" | spath input=json systemEvents{} output=systemEvents | stats values(_time) as _time by systemEvents | spath input=systemEvents | fields - systemEvents

recommend:

sourcetype=syslog_nsxedge host="NSX-Edge03-0" server!="NULL" | rex "(?<json>{.*})" | spath input=json systemEvents{} output=systemEvents | stats values(_time) as _time by systemEvents | spath input=systemEvents | fields - systemEvents | eval _time=strptime(timestamp,"%s") | sort _time

Re: How to extract multiple values for multiple fields within a single event?

spitchika — Fri, 07 Aug 2020 18:59:33 GMT

I took one event and tried like below.

| makeresults
| eval value= "Aug 7 03:01:12 NSX-Edge03-0 MsgMgr[2349]: [MDCM]: payload len:1019 data:{\"systemEvents\":[{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30302\", \"message\":\"A Loadbalancer Server Status is changed to DOWN\", \"timestamp\":1596794458, \"metaData\":{ \"listener\" : \"Carson_MDCM_Servers\", \"server\" : \"Server69\" } },{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30302\", \"message\":\"A Loadbalancer Server Status is changed to DOWN\", \"timestamp\":1596794458, \"metaData\":{ \"listener\" : \"WT_MDCM_Servers\", \"server\" : \"Server81\" } },{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30301\", \"message\":\"A Loadbalancer Server Status is changed to UP\", \"timestamp\":1596794468, \"metaData\":{ \"listener\" : \"WT_MDCM_Servers\", \"server\" : \"Server81\" } },{ \"moduleName\":\"vShield Edge LoadBalancer\", \"severity\":\"Informational\", \"eventCode\":\"30301\", \"message\":\"A Loadbalancer Server Status is changed to UP\", \"timestamp\":1596794478, \"metaData\":{ \"listener\" : \"Carson_MDCM_Servers\", \"server\" : \"Server69\" } }]}"
| rex field=value max_match=0 "message\":\"(?<TotalMessage>[^\"]+)"
| rex field=value max_match=0 "listener\" : \"(?<Site>[^\"]+)"
| rex field=value max_match=0 "server\" : \"(?<Server>[^\"]+)"
| table Server,Site,TotalMessage

Re: How to extract multiple values for multiple fields within a single event?

cbwillh — Fri, 07 Aug 2020 19:01:26 GMT

hello to4kawa

thanks so much for your help. that did the trick.

using your search as a base I simply added a table to eliminate a few of the fields we do not need and renamed the fields to simpler ones for our needs and it worked great!

very much appreciated.

I do wish I could give some points to spitchika as well. the solutions offered by spitchika (though a different approach from your solution) really got me close.

but in the end yours really worked great and displays everything accurately and just what we needed.

thanks so much for your help. I have created a lot of alerts for our business but still learning a LOT as regex is very hard to get my head around.

kind regards and thanks again!

Will