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.
Hi @Mrig342
can you try using
| table Connector_Name,Worker_ID,Port
| mvexpand Connector_Name
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.
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