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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...