Dashboards & Visualizations

How to put the values row-wise in a table?

Mrig342
Contributor

Hi All,

I have logs in splunk like below (this is one log):

{
"connector": {
"state": "RUNNING",
"worker_id": "mwgcb-csrla02u.nam.nsroot.net:8084"
},
"name": "source.mq.apac.tw.ebs.ft.ft.raw.int.rawevent",
"tasks": [
{
"id": 0,
"state": "RUNNING",
"worker_id": "mwgcb-csrla02u.nam.nsroot.net:8084"
}
],
"type": "source"
}
{
"connector": {
"state": "RUNNING",
"worker_id": "mwgcb-csrla01u.nam.nsroot.net:8084"
},
"name": "source.mq.apac.tw.cards.ecms.ecms.raw.int.rawevent",
"tasks": [
{
"id": 0,
"state": "RUNNING",
"worker_id": "mwgcb-csrla02u.nam.nsroot.net:8084"
}
],
"type": "source"
}
{
"connector": {
"state": "RUNNING",
"worker_id": "mwgcb-csrla01u.nam.nsroot.net:8084"
},
"name": "sink.mq.apac.tw.cards.ecms.ecms.derived.int.sinkevents",
"tasks": [
{
"id": 0,
"state": "RUNNING",
"worker_id": "mwgcb-csrla01u.nam.nsroot.net:8084"
},
{
"id": 1,
"state": "RUNNING",
"worker_id": "mwgcb-csrla01u.nam.nsroot.net:8084"
}
],
"type": "sink"
}

I have created below query to extract the fields and create a table of those values:

..... | rex field=_raw max_match=0 "\"connector\"\:\s\{\s+\"state\"\:\s\"(?P<Connector_State>[^\"]+)\""
| rex field=_raw max_match=0 "\"connector\"\:\s\{\s+\"state\"\:\s\"\w+\"\,\s+\"\w+\"\:\s\"(?P<Worker_ID>[^\:]+)"
| rex field=_raw max_match=0 "\"connector\"\:\s\{\s+\"state\"\:\s\"\w+\"\,\s+\"\w+\"\:\s\"[^\:]+\:(?P<Port>\d+)\""
| rex field=_raw max_match=0 "\"connector\"\:\s\{\s+\"state\"\:\s\"\w+\"\,\s+\"\w+\"\:\s\"[^\"]+\"\s+\}\,\s+\"name\"\:\s\"(?P<Connector_Name>[^\"]+)\""
| search Connector_State=RUNNING
| table Connector_Name,Worker_ID,Port

It gives me the table in below format:

Connector_Name Worker_ID Port

source.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent
sink.mq.apac.hk.ebs.im.im.derived.int.sinkevents
gtgcb-csrla02s.nam.nsroot.net
gtgcb-csrla01s.nam.nsroot.net
gtgcb-csrla02s.nam.nsroot.net
8087
8087
8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkeventsgtgcb-csrla02s.nam.nsroot.net8087
source.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent
gtgcb-csrla02s.nam.nsroot.net
gtgcb-csrla01s.nam.nsroot.net
8087
8087

But the requirement is to get the table as below:

Connector_Name Worker_ID Port

source.mq.apac.tw.cards.ecs.ecs.raw.sit.raweventgtgcb-csrla02s.nam.nsroot.net8087
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.raweventgtgcb-csrla01s.nam.nsroot.net8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkeventsgtgcb-csrla02s.nam.nsroot.net8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkeventsgtgcb-csrla02s.nam.nsroot.net8087
source.mq.apac.tw.cards.ecs.ecs.raw.sit.raweventgtgcb-csrla02s.nam.nsroot.net8087
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.raweventgtgcb-csrla01s.nam.nsroot.net8087

Please help to modify the query to get the output in the desired manner.

Labels (1)
0 Karma

SanjayReddy
Builder

Hi @Mrig342 

can you try using 

| table Connector_Name,Worker_ID,Port

| mvexpand Connector_Name 

0 Karma

Mrig342
Contributor

Hi @SanjayReddy 

Upon using "| mvexpand Connector_Name" the table comes up in below manner:

Connector_Name Worker_ID Port

source.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent
gtgcb-csrla02s.nam.nsroot.net
gtgcb-csrla01s.nam.nsroot.net
gtgcb-csrla02s.nam.nsroot.net
8087
8087
8087
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent
gtgcb-csrla02s.nam.nsroot.net
gtgcb-csrla01s.nam.nsroot.net
gtgcb-csrla02s.nam.nsroot.net
8087
8087
8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkevents
gtgcb-csrla02s.nam.nsroot.net
gtgcb-csrla01s.nam.nsroot.net
gtgcb-csrla02s.nam.nsroot.net
8087
8087
8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkeventsgtgcb-csrla02s.nam.nsroot.net8087
source.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent
gtgcb-csrla02s.nam.nsroot.net
gtgcb-csrla01s.nam.nsroot.net
8087
8087
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent
gtgcb-csrla02s.nam.nsroot.net
gtgcb-csrla01s.nam.nsroot.net
8087
8087

 

But the requirement is to get the table as below:

Connector_Name Worker_ID Port

source.mq.apac.tw.cards.ecs.ecs.raw.sit.raweventgtgcb-csrla02s.nam.nsroot.net8087
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.raweventgtgcb-csrla01s.nam.nsroot.net8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkeventsgtgcb-csrla02s.nam.nsroot.net8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkeventsgtgcb-csrla02s.nam.nsroot.net8087
source.mq.apac.tw.cards.ecs.ecs.raw.sit.raweventgtgcb-csrla02s.nam.nsroot.net8087
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.raweventgtgcb-csrla01s.nam.nsroot.net8087

Also tried "| mvexpand Connector_Name | mvexpand Worker_ID | mvexpand Port". But it didn't work either. Please help modify the query to get the output as desired.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try separating the connectors into different event, then use spath to extract the fields

| rex max_match=0 "(?s)(?<connector>\{\s*\"connector\"\:.+?\}(?=\s\{|$))"
| mvexpand connector
| spath input=connector name output=Connector_Name
| spath input=connector connector.worker_id output=worker_id
| spath input=connector connector.state output=Connector_State
| rex field=worker_id "(?<Worker_ID>[^:]+):(?<Port>\d+)"
| search Connector_State=RUNNING
| table Connector_Name,Worker_ID,Port
Get Updates on the Splunk Community!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...