How to create a common table with two set of logs?

[Mrig342]

Hi All,

I have got 2 set of logs, one of which has the Connector details and the other has got the error details if any connector gets failed.

First set:
Log1: sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents FAILED|mwgcb-csrla02u RUNNING|mwgcb-csrla02u NA
Log2: sink.http.apac.au.dna.eap.adobedmp.derived.int.aamrtevents RUNNING|mwgcb-csrla02u RUNNING|mwgcb-csrla02u NA
Log3: sink.http.apac.ph.dna.eap.adobedmp.derived.int.aamrtevents FAILED|mwgcb-csrla01u FAILED|mwgcb-csrla01u NA
Log4: sink.http.apac.th.dna.eap.adobedmp.derived.int.aamrtevents FAILED|swgcb-csrla01u FAILED|mwgcb-csrla01u NA

Here I used the below query to extract the required fields:
... | rex field=_raw "(\w+\.)(?P<Component>\w+)\." | rex field=_raw "(?P<Connector_Name>(\w+\.){3,12}\w+)\s" | rex field=_raw "(?P<Connector_Name>(\w+\-){3,12}\w+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Connector_State>\w+)\|" | rex field=_raw "(\w+\-){3,12}\w+\s(?P<Connector_State>\w+)\|"  | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|(?P<Worker_ID>\w+\-\w+)\s" | rex field=_raw "(\w+\-){3,12}\w+\s\w+\|(?P<Worker_ID>\w+\-\w+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task1_State>\w+)\|" | rex field=_raw "(\w+\-){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task1_State>\w+)\|" | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|(?P<Worker1_ID>\w+\-\w+)\s" | rex field=_raw "(\w+\-){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|(?P<Worker1_ID>\w+\-\w+)\s" | replace "mwgcb-csrla01u_XX_" with "mwgcb-csrla01u" in Worker1_ID | replace "mwgcb-csrla02u_XX_" with "mwgcb-csrla02u" in Worker1_ID | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task2_State>\w+)" | rex field=_raw "(\w+\-){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task2_State>\w+)" | replace "NA" with "Not_Available" in Task2_State | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|(?P<Worker2_ID>\w+\-\w+)" | rex field=_raw "(\w+\-){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|(?P<Worker2_ID>\w+\-\w+)" | replace "mwgcb-csrla01u_XX_" with "mwgcb-csrla01u" in Worker2_ID | replace "mwgcb-csrla02u_XX_" with "mwgcb-csrla02u" in Worker2_ID | fillnull value="Not_Available" Task1_State,Worker1_ID,Task2_State,Worker2_ID

Second set:
Log1: }
Log2:     "type": "sink"
Log3:     "tasks": [],
Log4:     "name": "sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents",
Log5:     },
Log6:         "worker_id": "mwgcb-csrla02u.nam.nsroot.net:8074"
Log7:         "trace": "org.apache.kafka.common.errors.SslAuthenticationException: SSL handshake failed\nCaused by: javax.net.ssl.SSLProtocolException: Unexpected handshake message: server_hello\n\tat sun.security.ssl.Alert.createSSLException(Alert.java:129)\n\tat sun.security.ssl.Alert.createSSLException(Alert.java:117)\n\tat sun.security.ssl.TransportContext.fatal(TransportContext.java:356)\n\tat sun.security.ssl.TransportContext.fatal(TransportContext.java:312)\n\tat sun.security.ssl.TransportContext.fatal(TransportContext.java:303)\n\tat sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:473)\n\tat sun.security.ssl.SSLEngineImpl$DelegatedTask$DelegatedAction.run(SSLEngineImpl.java:990)\n\tat sun.security.ssl.SSLEngineImpl$DelegatedTask$DelegatedAction.run(SSLEngineImpl.java:977)\n\tat java.security.AccessController.doPrivileged(Native Method)\n\tat sun.security.ssl.SSLEngineImpl$DelegatedTask.run(SSLEngineImpl.java:924)\n\tat org.apache.kafka.common.network.SslTransportLayer.runDelegatedTasks(SslTransportLayer.java:501)\n\tat org.apache.kafka.common.network.SslTransportLayer.handshakeUnwrap(SslTransportLayer.java:593)\n\tat org.apache.kafka.common.network.SslTransportLayer.doHandshake(SslTransportLayer.java:439)\n\tat org.apache.kafka.common.network.SslTransportLayer.handshake(SslTransportLayer.java:324)\n\tat org.apache.kafka.common.network.KafkaChannel.prepare(KafkaChannel.java:205)\n\tat org.apache.kafka.common.network.Selector.pollSelectionKeys(Selector.java:561)\n\tat org.apache.kafka.common.network.Selector.poll(Selector.java:498)\n\tat org.apache.kafka.clients.NetworkClient.poll(NetworkClient.java:575)\n\tat org.apache.kafka.clients.admin.KafkaAdminClient$AdminClientRunnable.processRequests(KafkaAdminClient.java:1358)\n\tat org.apache.kafka.clients.admin.KafkaAdminClient$AdminClientRunnable.run(KafkaAdminClient.java:1289)\n\tat java.lang.Thread.run(Thread.java:750)\n",
Log8:         "state": "FAILED",
Log9:     "connector": {
Log10: {

And then the set repeats for the next Connector.
I tried to extract the required fields using below queries:
Query1: ... | regex _raw="name" | rex field=_raw "name\"\:\s\"(?P<Connector>[^\"]+)\"\,"

Query2: ... | rex field=_raw "Caused\sby\:\s(?P<Exception>[^\:]+)\:\s" | search Exception!="org.apache.kafka.common.KafkaException" | rex field=_raw "Caused\sby\:\s([^\:]+)\:\s(?P<Error_Msg>(\w+\:\s){0,1}(\w+\s){1,15}\w+)"

Note that, both the set of logs have different source but same index.

I want to create a table using both the set of logs such that along with the failed connector we can see the error details also.

For e.g.

Connector_Name	Connector_State	Worker_ID	Task1_State	Worker1_ID	Task2_State	Worker2_ID	Exception	Error_Msg
sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents	FAILED	mwgcb-csrla02u	RUNNING	mwgcb-csrla02u	NA	NA	javax.net.ssl.SSLProtocolException	Unexpected handshake message
sink.http.apac.au.dna.eap.adobedmp.derived.int.aamrtevents	RUNNING	mwgcb-csrla02u	RUNNING	mwgcb-csrla02u	NA	NA	NA	NA

I need to Connector name from second set and compare with first set, and whichever connector has failed state, I need to add the Exception and Error_Msg there.

Please help me to get my desired table. Your kind help is highly appreciated.

Thank you..!!

[ITWhisperer]

Your second set looks like a JSON object but the lines are in the reverse order. Is this just the way you have shown them, or the way they have been extracted? Are these lines really part of the same event? Can you share some of your actual events? How are these events ingested? Do you have the correct line breaker configured?

[Mrig342]

Hi @ITWhisperer,

I made some changes to the input logs. Please see below and help to get desired table.

First set:
Log1: sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents FAILED|swgcb-csrla02u RUNNING|mwgcb-csrla02u NA
Log2: sink.http.apac.au.dna.eap.adobedmp.derived.int.aamrtevents RUNNING|mwgcb-csrla01u RUNNING|mwgcb-csrla01u NA
Log3: sink.http.apac.ph.dna.eap.adobedmp.derived.int.aamrtevents FAILED|mwgcb-csrla01u FAILED|mwgcb-csrla01u NA
Log4: sink.http.apac.th.dna.eap.adobedmp.derived.int.aamrtevents FAILED|swgcb-csrla01u FAILED|mwgcb-csrla01u NA

Here I used the below query to extract the required fields:
... | rex field=_raw "(\w+\.)(?P<Component>\w+)\." | search Component=http | rex field=_raw "(?P<Connector_Name>(\w+\.){3,12}\w+)\s" | rex field=_raw "(?P<Connector_Name>(\w+\-){3,12}\w+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Conn_State>\w+)\|" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Connector_State>\w+\|[^\s]+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task1_State>\w+\|[^\s]+)\s" | replace "RUNNING|mwgcb-csrla01u_XX_" with "RUNNING|mwgcb-csrla01u" in Task1_State | replace "RUNNING|mwgcb-csrla02u_XX_" with "RUNNING|mwgcb-csrla02u" in Task1_State | replace "FAILED|mwgcb-csrla01u_XX_" with "FAILED|mwgcb-csrla01u" in Task1_State | replace "FAILED|mwgcb-csrla02u_XX_" with "FAILED|mwgcb-csrla02u" in Task1_State | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task2_State>\w+\|[^\s]+)" | replace "RUNNING|mwgcb-csrla01u_XX_" with "RUNNING|mwgcb-csrla01u" in Task2_State | replace "RUNNING|mwgcb-csrla02u_XX_" with "RUNNING|mwgcb-csrla02u" in Task2_State | replace "FAILED|mwgcb-csrla01u_XX_" with "FAILED|mwgcb-csrla01u" in Task2_State | replace "FAILED|mwgcb-csrla02u_XX_" with "FAILED|mwgcb-csrla02u" in Task2_State | fillnull value="Not_Available" Task1_State,Task2_State

Second set:
Log1: {
    "connector": {
        "state": "RUNNING",
        "worker_id": "mwgcb-csrla01u.nam.nsroot.net:8074"
    },
    "name": "sink.http.apac.au.dna.eap.adobedmp.derived.int.aamrtevents",
    "tasks": [
        {
            "id": 0,
            "state": "RUNNING",
            "worker_id": "mwgcb-csrla01u.nam.nsroot.net:8074"
        }
    ],
    "type": "sink"
}

Log2: 	
{
    "connector": {
        "state": "FAILED",
        "trace": "org.apache.kafka.common.errors.SslAuthenticationException: SSL handshake failed\nCaused by: javax.net.ssl.SSLProtocolException: Unexpected handshake message: server_hello\n\tat sun.security.ssl.Alert.createSSLException(Alert.java:129)\n\tat sun.security.ssl.Alert.createSSLException(Alert.java:117)\n\tat sun.security.ssl.TransportContext.fatal(TransportContext.java:356)\n\tat sun.security.ssl.TransportContext.fatal(TransportContext.java:312)\n\tat sun.security.ssl.TransportContext.fatal(TransportContext.java:303)\n\tat sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:473)\n\tat sun.security.ssl.SSLEngineImpl$DelegatedTask$DelegatedAction.run(SSLEngineImpl.java:990)\n\tat sun.security.ssl.SSLEngineImpl$DelegatedTask$DelegatedAction.run(SSLEngineImpl.java:977)\n\tat java.security.AccessController.doPrivileged(Native Method)\n\tat sun.security.ssl.SSLEngineImpl$DelegatedTask.run(SSLEngineImpl.java:924)\n\tat org.apache.kafka.common.network.SslTransportLayer.runDelegatedTasks(SslTransportLayer.java:501)\n\tat org.apache.kafka.common.network.SslTransportLayer.handshakeUnwrap(SslTransportLayer.java:593)\n\tat org.apache.kafka.common.network.SslTransportLayer.doHandshake(SslTransportLayer.java:439)\n\tat org.apache.kafka.common.network.SslTransportLayer.handshake(SslTransportLayer.java:324)\n\tat org.apache.kafka.common.network.KafkaChannel.prepare(KafkaChannel.java:205)\n\tat org.apache.kafka.common.network.Selector.pollSelectionKeys(Selector.java:561)\n\tat org.apache.kafka.common.network.Selector.poll(Selector.java:498)\n\tat org.apache.kafka.clients.NetworkClient.poll(NetworkClient.java:575)\n\tat org.apache.kafka.clients.admin.KafkaAdminClient$AdminClientRunnable.processRequests(KafkaAdminClient.java:1358)\n\tat org.apache.kafka.clients.admin.KafkaAdminClient$AdminClientRunnable.run(KafkaAdminClient.java:1289)\n\tat java.lang.Thread.run(Thread.java:750)\n",
        "worker_id": "swgcb-csrla02u.nam.nsroot.net:8098"
    },
    "name": "sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents",
    "tasks": [
        {
            "id": 0,
            "state": "RUNNING",
            "worker_id": "mwgcb-csrla02u.nam.nsroot.net:8098"
        }
    ],
    "type": "source"
}

Here I used the below query to extract the required fields:
... | rex field=_raw "\"name\"\:\s\"(?P<Connector_Name>(\w+\.){1,12}\w+)\"\," | rex field=_raw "\{\s+\"state\"\:\s\"(?P<Conn_State>\w+)\"\," | rex field=_raw "\{\s+\"state\"\:\s\"\w+\"\,\s+\"trace\"\:\s\"(\w+\.){1,6}(?P<Exception>\w+)\:\s" | rex field=_raw "\{\s+\"state\"\:\s\"\w+\"\,\s+\"trace\"\:\s\"(\w+\.)+\w+\:\s(?P<Error_Msg>(\w+\s){1,15}\w+)"

Note that, both the set of logs have different source but same index.

I want to create a table using both the set of logs such that along with the failed connector we can see the error details also.

For e.g.

Connector_Name	Connector_State	Task1_State	Task2_State	Exception	Error_Msg
sink.http.apac.au.dna.eap.adobedmp.derived.int.aamrtevents	RUNNING|mwgcb-csrla01u	RUNNING|mwgcb-csrla01u	Not_Available	Not_Available	Not_Available
sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents	FAILED|swgcb-csrla02u	RUNNING|mwgcb-csrla02u	Not_Available	SslAuthenticationException	SSL handshake failed

I need Connector_Name from second set tocompare with Connector_Name from first set, and whichever connector has failed state, I need to add the Exception and Error_Msg there.

Please help me to get my desired table. Your kind help is highly appreciated.

Thank You..!!

[ITWhisperer]

You  could try searching for both sources at once then apply your rex's etc. clear the source 1 fields for source 2 events, then gather the events together.

| rex field=_raw "(\w+\.)(?P<Component>\w+)\." | rex field=_raw "(?P<Connector_Name>(\w+\.){3,12}\w+)\s" | rex field=_raw "(?P<Connector_Name>(\w+\-){3,12}\w+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Conn_State>\w+)\|" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Connector_State>\w+\|[^\s]+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task1_State>\w+\|[^\s]+)\s" | replace "RUNNING|mwgcb-csrla01u_XX_" with "RUNNING|mwgcb-csrla01u" in Task1_State | replace "RUNNING|mwgcb-csrla02u_XX_" with "RUNNING|mwgcb-csrla02u" in Task1_State | replace "FAILED|mwgcb-csrla01u_XX_" with "FAILED|mwgcb-csrla01u" in Task1_State | replace "FAILED|mwgcb-csrla02u_XX_" with "FAILED|mwgcb-csrla02u" in Task1_State | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task2_State>\w+\|[^\s]+)" | replace "RUNNING|mwgcb-csrla01u_XX_" with "RUNNING|mwgcb-csrla01u" in Task2_State | replace "RUNNING|mwgcb-csrla02u_XX_" with "RUNNING|mwgcb-csrla02u" in Task2_State | replace "FAILED|mwgcb-csrla01u_XX_" with "FAILED|mwgcb-csrla01u" in Task2_State | replace "FAILED|mwgcb-csrla02u_XX_" with "FAILED|mwgcb-csrla02u" in Task2_State | fillnull value="Not_Available" Task1_State,Task2_State
| rex field=_raw "\"name\"\:\s\"(?P<Connector_Name>(\w+\.){1,12}\w+)\"\," | rex field=_raw "\{\s+\"state\"\:\s\"(?P<Conn_State>\w+)\"\," | rex field=_raw "\{\s+\"state\"\:\s\"\w+\"\,\s+\"trace\"\:\s\"(\w+\.){1,6}(?P<Exception>\w+)\:\s" | rex field=_raw "\{\s+\"state\"\:\s\"\w+\"\,\s+\"trace\"\:\s\"(\w+\.)+\w+\:\s(?P<Error_Msg>(\w+\s){1,15}\w+)"
| eval Component=if(source==1,Component,null())
| eval Task1_State=if(source==1,Task1_State,null())
| eval Task2_State=if(source==1,Task2_State,null())
| where source==2 OR Component="http"
| stats values(*) as * by Connector_Name Conn_State

[Mrig342]

Hi @ITWhisperer,

Thank you for your inputs. I am getting a table using the query you provided. However, rows are getting blank for components other than "http"

I am having Connectors with different Component types. e.g.

1. sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents

2. sink.mq.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents

3. sink.solace.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents

if I am having these connectors in failed state, then I am getting below blanked table:

Connector_Name	Connector_State	Task1_State	Task2_State	Exception	Error_Msg
sink.mq.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents				Not_Available	Not_Available
sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents	FAILED|swgcb-csrla02u	RUNNING|mwgcb-csrla02u	Not_Available	SslAuthenticationException	SSL handshake failed
sink.solace.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents				Not_Available	Not_Available

I added these extra lines to your query to get the above table:

| rex field=_raw "(\w+\.)(?P<Component>\w+)\." | rex field=_raw "(?P<Connector_Name>(\w+\.){3,12}\w+)\s" | rex field=_raw "(?P<Connector_Name>(\w+\-){3,12}\w+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Conn_State>\w+)\|" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Connector_State>\w+\|[^\s]+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task1_State>\w+\|[^\s]+)\s" | replace "RUNNING|mwgcb-csrla01u_XX_" with "RUNNING|mwgcb-csrla01u" in Task1_State | replace "RUNNING|mwgcb-csrla02u_XX_" with "RUNNING|mwgcb-csrla02u" in Task1_State | replace "FAILED|mwgcb-csrla01u_XX_" with "FAILED|mwgcb-csrla01u" in Task1_State | replace "FAILED|mwgcb-csrla02u_XX_" with "FAILED|mwgcb-csrla02u" in Task1_State | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task2_State>\w+\|[^\s]+)" | replace "RUNNING|mwgcb-csrla01u_XX_" with "RUNNING|mwgcb-csrla01u" in Task2_State | replace "RUNNING|mwgcb-csrla02u_XX_" with "RUNNING|mwgcb-csrla02u" in Task2_State | replace "FAILED|mwgcb-csrla01u_XX_" with "FAILED|mwgcb-csrla01u" in Task2_State | replace "FAILED|mwgcb-csrla02u_XX_" with "FAILED|mwgcb-csrla02u" in Task2_State | fillnull value="Not_Available" Task1_State,Task2_State
| rex field=_raw "\"name\"\:\s\"(?P<Connector_Name>(\w+\.){1,12}\w+)\"\," | rex field=_raw "\{\s+\"state\"\:\s\"(?P<Conn_State>\w+)\"\," | rex field=_raw "\{\s+\"state\"\:\s\"\w+\"\,\s+\"trace\"\:\s\"(\w+\.){1,6}(?P<Exception>\w+)\:\s" | rex field=_raw "\{\s+\"state\"\:\s\"\w+\"\,\s+\"trace\"\:\s\"(\w+\.)+\w+\:\s(?P<Error_Msg>(\w+\s){1,15}\w+)"
| eval Component=if(source==1,Component,null())
| eval Task1_State=if(source==1,Task1_State,null())
| eval Task2_State=if(source==1,Task2_State,null())
| where source==2 OR Component="http"
| stats values(*) as * by Connector_Name Conn_State
| table Connector_Name,Connector_State,Task1_State,Task2_State,Exception,Error_Msg
| dedup Connector_Name
| fillnull value=Not_Available Exception,Error_Msg

When I change the line (| where source==2 OR Component="http") to (| where source==2 OR Component="http" OR Component="mq" OR Component="solace"), I am getting the table properly filled with the data.

However, I need the table to be showing one component at a time. I tried adding the query (| search Component="solace"), but it makes the Exception & Error_Msg fields empty.

Similarly, when trying to add a Time_stamp column to the table, it makes the Exception & Error_Msg fields empty again.

For adding time stamp, I added these lines of query:

| rex field=_raw "(\w+\.)(?P<Component>\w+)\." | rex field=_raw "(?P<Connector_Name>(\w+\.){3,12}\w+)\s" | rex field=_raw "(?P<Connector_Name>(\w+\-){3,12}\w+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Conn_State>\w+)\|" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Connector_State>\w+\|[^\s]+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task1_State>\w+\|[^\s]+)\s" | replace "RUNNING|mwgcb-csrla01u_XX_" with "RUNNING|mwgcb-csrla01u" in Task1_State | replace "RUNNING|mwgcb-csrla02u_XX_" with "RUNNING|mwgcb-csrla02u" in Task1_State | replace "FAILED|mwgcb-csrla01u_XX_" with "FAILED|mwgcb-csrla01u" in Task1_State | replace "FAILED|mwgcb-csrla02u_XX_" with "FAILED|mwgcb-csrla02u" in Task1_State | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task2_State>\w+\|[^\s]+)" | replace "RUNNING|mwgcb-csrla01u_XX_" with "RUNNING|mwgcb-csrla01u" in Task2_State | replace "RUNNING|mwgcb-csrla02u_XX_" with "RUNNING|mwgcb-csrla02u" in Task2_State | replace "FAILED|mwgcb-csrla01u_XX_" with "FAILED|mwgcb-csrla01u" in Task2_State | replace "FAILED|mwgcb-csrla02u_XX_" with "FAILED|mwgcb-csrla02u" in Task2_State | fillnull value="Not_Available" Task1_State,Task2_State
| rex field=_raw "\"name\"\:\s\"(?P<Connector_Name>(\w+\.){1,12}\w+)\"\," | rex field=_raw "\{\s+\"state\"\:\s\"(?P<Conn_State>\w+)\"\," | rex field=_raw "\{\s+\"state\"\:\s\"\w+\"\,\s+\"trace\"\:\s\"(\w+\.){1,6}(?P<Exception>\w+)\:\s" | rex field=_raw "\{\s+\"state\"\:\s\"\w+\"\,\s+\"trace\"\:\s\"(\w+\.)+\w+\:\s(?P<Error_Msg>(\w+\s){1,15}\w+)"
| eval Time_Stamp=strftime(_time, "%b %d, %Y %I:%M:%S %p")
| eval Component=if(source==1,Component,null())
| eval Task1_State=if(source==1,Task1_State,null())
| eval Task2_State=if(source==1,Task2_State,null())
| where source==2 OR Component="http"
| stats values(*) as * by Connector_Name Conn_State Time_Stamp
| table Time_Stamp,Connector_Name,Connector_State,Task1_State,Task2_State,Exception,Error_Msg
| dedup Connector_Name
| fillnull value=Not_Available Exception,Error_Msg 

Please help on the Component & Time_Stamp requirement to get the table in a meaningful way.

Thank You..!!

[ITWhisperer]

| rex field=_raw "(\w+\.)(?P<Component>\w+)\." | rex field=_raw "(?P<Connector_Name>(\w+\.){3,12}\w+)\s" | rex field=_raw "(?P<Connector_Name>(\w+\-){3,12}\w+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Conn_State>\w+)\|" | rex field=_raw "(\w+\.){3,12}\w+\s(?P<Connector_State>\w+\|[^\s]+)\s" | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task1_State>\w+\|[^\s]+)\s" | replace "RUNNING|mwgcb-csrla01u_XX_" with "RUNNING|mwgcb-csrla01u" in Task1_State | replace "RUNNING|mwgcb-csrla02u_XX_" with "RUNNING|mwgcb-csrla02u" in Task1_State | replace "FAILED|mwgcb-csrla01u_XX_" with "FAILED|mwgcb-csrla01u" in Task1_State | replace "FAILED|mwgcb-csrla02u_XX_" with "FAILED|mwgcb-csrla02u" in Task1_State | rex field=_raw "(\w+\.){3,12}\w+\s\w+\|\w+\-\w+\s((\_KK\_){0,1})\w+\|\w+\-\w+\s((\_KK\_){0,1})(?P<Task2_State>\w+\|[^\s]+)" | replace "RUNNING|mwgcb-csrla01u_XX_" with "RUNNING|mwgcb-csrla01u" in Task2_State | replace "RUNNING|mwgcb-csrla02u_XX_" with "RUNNING|mwgcb-csrla02u" in Task2_State | replace "FAILED|mwgcb-csrla01u_XX_" with "FAILED|mwgcb-csrla01u" in Task2_State | replace "FAILED|mwgcb-csrla02u_XX_" with "FAILED|mwgcb-csrla02u" in Task2_State | fillnull value="Not_Available" Task1_State,Task2_State
| rex field=_raw "\"name\"\:\s\"(?P<Connector_Name>(\w+\.){1,12}\w+)\"\," | rex field=_raw "\{\s+\"state\"\:\s\"(?P<Conn_State>\w+)\"\," | rex field=_raw "\{\s+\"state\"\:\s\"\w+\"\,\s+\"trace\"\:\s\"(\w+\.){1,6}(?P<Exception>\w+)\:\s" | rex field=_raw "\{\s+\"state\"\:\s\"\w+\"\,\s+\"trace\"\:\s\"(\w+\.)+\w+\:\s(?P<Error_Msg>(\w+\s){1,15}\w+)"
| eval Component=if(source==1,Component,null())
| eval Task1_State=if(source==1,Task1_State,null())
| eval Task2_State=if(source==1,Task2_State,null())
| where source==2 OR Component="http" OR Component="mq" OR Component="solace"
| stats values(*) as * by Connector_Name Conn_State
| where source==2 OR Component="http"
| table Connector_Name,Connector_State,Task1_State,Task2_State,Exception,Error_Msg
| dedup Connector_Name
| fillnull value=Not_Available Exception,Error_Msg 

Which timestamp _time do you want to show? It is unclear from your sample events whether they all have the same timestamp or not and by adding Time_Stamp to the by clause of the stats command will only work if all the events you are interested in have the same _time value (so I removed this from the search query).

[Mrig342]

Hi @ITWhisperer,

Thank you for your inputs. The added query line (| where source==2 OR Component="http" OR Component="mq" OR Component="solace") is giving the table all filled for all type of components in a single table. However, I need the table to be showing one component at a time. For e.g. the table for "mq" component should have only the connectors with mq component.

I need to add a query line (| search Component="mq") so that it can be used for drilldown also.

And, for the Time_Stamp requirement, I want to consider the time vale from source 1.

Please help on the Component & Time_Stamp requirement to get the table in the desired way.

Thank You..!!

[ITWhisperer]

The second where command deals with selecting which component you want. For the Time_Stamp you can use similar eval commands

| eval Component=if(source==1,Component,null())
| eval Task1_State=if(source==1,Task1_State,null())
| eval Task2_State=if(source==1,Task2_State,null())
| eval Time_Stamp=if(source==1,_time,null())
| where source==2 OR Component="http" OR Component="mq" OR Component="solace"
| stats values(*) as * by Connector_Name Conn_State
| where source==2 OR Component="mq"
| table Time_Stamp,Connector_Name,Connector_State,Task1_State,Task2_State,Exception,Error_Msg

[Mrig342]

Hi @ITWhisperer,

Thank you for your inputs..!! I am able to get the table for one component at a time now. However, the time requirement is still not resolved. Using the below query:

| eval Time_Stamp=strftime(_time, "%b %d, %Y %I:%M:%S %p")
| eval Component=if(source==1,Component,null())
| eval Task1_State=if(source==1,Task1_State,null())
| eval Task2_State=if(source==1,Task2_State,null())
| eval Time_Stamp=if(source==1,Time_Stamp,null())
| where source==2 OR Component="http" OR Component="mq" OR Component="solace"
| stats values(*) as * by Connector_Name Conn_State Time_Stamp
| where source==2 OR Component="mq"
| table Time_Stamp,Connector_Name,Connector_State,Task1_State,Task2_State,Exception,Error_Msg

And getting this below table:

Time_Stamp	Connector_Name	Connector_State	Task1_State	Task2_State	Exception	Error_Msg
Jan 24, 2023 01:50:06 PM	sink.http.apac.au.digital.mbol.mbk.raw.int.rawevent	FAILED|mwgcb-csrla02u	RUNNING|mwgcb-csrla01u	Not_Available	Not_Available	Not_Available
Jan 24, 2023 01:50:05 PM	sink.http.apac.au.dna.eap.ec.derived.int.amplifycarddetails	RUNNING|mwgcb-csrla01u	RUNNING|mwgcb-csrla01u	RUNNING|mwgcb-csrla01u	Not_Available	Not_Available

As you can see, in the table the Exception & Error_Msg column is filled blank/Not_Available which should have been filled otherwise.

Please help on the Time_Stamp requirement to get the table in a meaningful way.

Thank You..!!

[ITWhisperer]

Don't put Time_Stamp in the by clause. The value of Time_Stamp from source 1 is picked up by the values(*) as *. Please try the way I suggested.

[Mrig342]

Hi @ITWhisperer,

I tried removing Time_Stamp from by clause, but this has put forward the Component requirement. The table now has the time_stamp and fills the exception & error_msg columns. But the table now shows all the components instead of having one component at a time.

This is the query I used:

| eval Time_Stamp=strftime(_time, "%b %d, %Y %I:%M:%S %p")
| eval Component=if(source=="/tmp/temp_connector1.txt",Component,null())
| eval Task1_State=if(source=="/tmp/temp_connector1.txt",Task1_State,null())
| eval Task2_State=if(source=="/tmp/temp_connector1.txt",Task2_State,null())
| eval Time_Stamp=if(source=="/tmp/temp_connector1.txt",Time_Stamp,null())
| where source=="/kfkapps/Kafka/scripts/final_conn_status.txt" OR Component="http" OR Component="mq" OR Component="solace"
| stats values(*) as * by Connector_Name Conn_State
| where source=="/kfkapps/Kafka/scripts/final_conn_status.txt" OR Component="mq"
| table Time_Stamp,Connector_Name,Port,Connector_State,Task1_State,Task2_State,Exception,Error_Msg
| mvexpand Time_Stamp
| dedup Connector_Name
| fillnull value=Not_Available Exception,Error_Msg

And this is the table it produces:

Time_Stamp	Connector_Name	Connector_State	Task1_State	Task2_State	Exception	Error_Msg
Jan 24, 2023 11:46:11 PM	sink.mq.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents	RUNNING|mwgcb-csrla01u	RUNNING|mwgcb-csrla01u	RUNNING|mwgcb-csrla01u	Not_Available	Not_Available
Jan 24, 2023 11:46:10 PM	sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents	FAILED|swgcb-csrla02u	RUNNING|mwgcb-csrla02u	Not_Available	SslAuthenticationException	SSL handshake failed
Jan 24, 2023 11:46:07 PM	sink.solace.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents	RUNNING|mwgcb-csrla01u	RUNNING|mwgcb-csrla02u	Not_Available	Not_Available	Not_Available

Can you please help me modify the query to meet both the time-stamp and component requirements..!!

Thank you..!!

[ITWhisperer · Accepted solution]

Try with second where using AND instead of OR (my mistake from a couple of posts back)

| where source=="/kfkapps/Kafka/scripts/final_conn_status.txt" AND Component="mq"

[Mrig342]

Hi @ITWhisperer,

Thank you for all the inputs you provided..!! I am now able to get the table as per my requirement.

Your valuable inputs are much appreciated.