Dashboards & Visualizations

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

Mrig342
Contributor

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..!!

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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"

 

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

Mrig342
Contributor

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_NameConnector_StateTask1_StateTask2_StateExceptionError_Msg
sink.http.apac.au.dna.eap.adobedmp.derived.int.aamrteventsRUNNING|mwgcb-csrla01uRUNNING|mwgcb-csrla01uNot_AvailableNot_AvailableNot_Available
sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrteventsFAILED|swgcb-csrla02uRUNNING|mwgcb-csrla02uNot_AvailableSslAuthenticationExceptionSSL 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..!!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Contributor

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_NameConnector_StateTask1_StateTask2_StateExceptionError_Msg
sink.mq.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents   Not_AvailableNot_Available
sink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrteventsFAILED|swgcb-csrla02uRUNNING|mwgcb-csrla02uNot_AvailableSslAuthenticationExceptionSSL handshake failed
sink.solace.apac.hk.dna.eap.adobedmp.derived.int.aamrtevents   Not_AvailableNot_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..!!

0 Karma

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

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..!!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Contributor

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_StampConnector_NameConnector_StateTask1_StateTask2_StateExceptionError_Msg
Jan 24, 2023 01:50:06 PMsink.http.apac.au.digital.mbol.mbk.raw.int.raweventFAILED|mwgcb-csrla02uRUNNING|mwgcb-csrla01uNot_AvailableNot_AvailableNot_Available
Jan 24, 2023 01:50:05 PMsink.http.apac.au.dna.eap.ec.derived.int.amplifycarddetailsRUNNING|mwgcb-csrla01uRUNNING|mwgcb-csrla01uRUNNING|mwgcb-csrla01uNot_AvailableNot_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..!!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Contributor

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_StampConnector_NameConnector_StateTask1_StateTask2_StateExceptionError_Msg
Jan 24, 2023 11:46:11 PMsink.mq.apac.hk.dna.eap.adobedmp.derived.int.aamrteventsRUNNING|mwgcb-csrla01uRUNNING|mwgcb-csrla01uRUNNING|mwgcb-csrla01uNot_AvailableNot_Available
Jan 24, 2023 11:46:10 PMsink.http.apac.hk.dna.eap.adobedmp.derived.int.aamrteventsFAILED|swgcb-csrla02uRUNNING|mwgcb-csrla02uNot_AvailableSslAuthenticationExceptionSSL handshake failed
Jan 24, 2023 11:46:07 PMsink.solace.apac.hk.dna.eap.adobedmp.derived.int.aamrteventsRUNNING|mwgcb-csrla01uRUNNING|mwgcb-csrla02uNot_AvailableNot_AvailableNot_Available

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

Thank you..!!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...