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

[Mrig342]

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.sinkevents	gtgcb-csrla02s.nam.nsroot.net	8087
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.rawevent	gtgcb-csrla02s.nam.nsroot.net	8087
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent	gtgcb-csrla01s.nam.nsroot.net	8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkevents	gtgcb-csrla02s.nam.nsroot.net	8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkevents	gtgcb-csrla02s.nam.nsroot.net	8087
source.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent	gtgcb-csrla02s.nam.nsroot.net	8087
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent	gtgcb-csrla01s.nam.nsroot.net	8087

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

[SanjayReddy]

Hi @Mrig342 

can you try using 

| table Connector_Name,Worker_ID,Port

| mvexpand Connector_Name 

[Mrig342]

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.sinkevents	gtgcb-csrla02s.nam.nsroot.net	8087
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.rawevent	gtgcb-csrla02s.nam.nsroot.net	8087
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent	gtgcb-csrla01s.nam.nsroot.net	8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkevents	gtgcb-csrla02s.nam.nsroot.net	8087
sink.mq.apac.hk.ebs.im.im.derived.int.sinkevents	gtgcb-csrla02s.nam.nsroot.net	8087
source.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent	gtgcb-csrla02s.nam.nsroot.net	8087
sink.mq.apac.tw.cards.ecs.ecs.raw.sit.rawevent	gtgcb-csrla01s.nam.nsroot.net	8087

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.

[ITWhisperer]

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