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

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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...