Dashboards & Visualizations
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

How to combine two set of logs and create a table out of it?

Mrig342
Contributor
‎09-05-2022 10:10 AM

Hi All,

I have two set of logs as below and I want a create a table combining them.

 

 

 

Type1:
Log1: MACHINE@|@Port@|@Country@|@Count MEMORY STATUS
mwgcb-csrla01u|8070|EAS|5 CNF_| PASS|mwgcb-csrla01u PASS|mwgcb-csrla02u

Type2:
Log1: source.mq.apac.sg.cards.eas.eas.raw.int.rawevent RUNNING|mwgcb-csrla02u RUNNING|mwgcb-csrla01u RUNNING|mwgcb-csrla02u
Log2: source.mq.apac.in.cards.eas.eas.raw.int.rawevent RUNNING|mwgcb-csrla01u FAILED|mwgcb-csrla02u NA
Log3: source.mq.apac.my.cards.eas.eas.raw.int.rawevent FAILED|mwgcb-csrla02u RUNNING|mwgcb-csrla01u NA
Log4: source.mq.apac.th.cards.eas.eas.raw.int.rawevent RUNNING|mwgcb-csrla01u RUNNING|mwgcb-csrla01u NA
Log5: source.mq.apac.hk.cards.eas.eas.raw.int.rawevent UNASSIGNED|mwgcb-csrla01u RUNNING|mwgcb-csrla01u RUNNING|mwgcb-csrla02u

 

 

 

I  extracted the required fields from each of the log types and am trying to create a table with the fields Machine_Name, Port, Worker_Node, Connector_Count, Success_Count where Success_Count is the number of Connectors that are in RUNNING state for a Worker_Node.

For e.g.: For the above set of logs the table should look like

Machine_Name Port Worker_Node Connector_Count Success_Count
mwgcb-csrla01u 8070 EAS 5 3

I tried to combine the two set of logs by creating a query as below but not successful in getting the above table.

 

 

 

| multisearch [
  search index=ABC host=XYZ source=KLM 
  | regex _raw="\w+\-\w+\|\d+" 
  | rex field=_raw "(?P<Machine_Name>\w+\-\w+)\|(?P<Port>\d+)\|(?P<Worker_Node>\w+)\|(?P<Connector_Count>\d+)\s"
  ] 
  [search index=ABC host=XYZ source=KLM | regex _raw!="\w+\-\w+\|\d+"
  | regex _raw!="properties" 
  | regex _raw!="MACHINE" 
  | regex _raw!="CONNECTOR_NAME" 
  | regex _raw!="CNF" 
  | regex _raw!="Detailed" 
  | 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 
] 
| lookup Worker_Connector_List.csv "Connector_Name" 
| search Worker_Node=EAS 
| stats latest(Connector_State) as Connector_State by Connector_Name 
| eval Status=if(Connector_State="RUNNING", "1","0") 
| stats sum(Status) as Success_Count 
| table Machine_Name,Port,Worker_Node,Connector_Count,Success_Count

 

 

 

 

Please help me to create/modify the query so that I can get the table in the desired manner.

 

Thank you All..!!

Labels (1)
Labels
0 Karma
Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎09-06-2022 03:14 AM

Does something like this work for you?

| stats values(Machine_Name) as Machine_Name, values(Port) as Port, values(Worker_Node) as Worker_Node, values(Connector_Count) as  Connector_Count, latest(Connector_State) as Connector_State, count(eval(Connector_State="RUNNING")) as Success_Count by Connector_Name
| table Machine_Name,Port,Worker_Node,Connector_Count,Success_Count
Reply

Mrig342
Contributor
‎09-06-2022 05:00 AM

Thank you @ITWhisperer ...!!

But it didn't help create the table in the expected manner. Upon using the given query the table came out as below:

Machine_NamePortWorker_NodeConnector_CountSuccess_Count
    3

 

Please help me to create/modify the script to get the expected table.

You kind help is highly appreciated..!!

0 Karma
Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎09-06-2022 07:53 AM

What was your full search for this result?

0 Karma
Reply

Mrig342
Contributor
‎09-06-2022 10:26 AM

Hi @ITWhisperer 

For the above result, the full query was:

| multisearch [
  search index=ABC host=XYZ source=KLM 
  | regex _raw="\w+\-\w+\|\d+" 
  | rex field=_raw "(?P<Machine_Name>\w+\-\w+)\|(?P<Port>\d+)\|(?P<Worker_Node>\w+)\|(?P<Connector_Count>\d+)\s"
  ] 
  [search index=ABC host=XYZ source=KLM | regex _raw!="\w+\-\w+\|\d+"
  | regex _raw!="properties" 
  | regex _raw!="MACHINE" 
  | regex _raw!="CONNECTOR_NAME" 
  | regex _raw!="CNF" 
  | regex _raw!="Detailed" 
  | 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 
] 
| lookup Worker_Connector_List.csv "Connector_Name" 
| search Worker_Node=EAS  | stats values(Machine_Name) as Machine_Name, values(Port) as Port, values(Worker_Node) as Worker_Node, values(Connector_Count) as  Connector_Count, latest(Connector_State) as Connector_State by Connector_Name | stats count(eval(Connector_State="RUNNING")) as Success_Count | table Machine_Name,Port,Worker_Node,Connector_Count,Success_Count

 

While I used the query you provide, the full search was:

| multisearch [
  search index=ABC host=XYZ source=KLM 
  | regex _raw="\w+\-\w+\|\d+" 
  | rex field=_raw "(?P<Machine_Name>\w+\-\w+)\|(?P<Port>\d+)\|(?P<Worker_Node>\w+)\|(?P<Connector_Count>\d+)\s"
  ] 
  [search index=ABC host=XYZ source=KLM | regex _raw!="\w+\-\w+\|\d+"
  | regex _raw!="properties" 
  | regex _raw!="MACHINE" 
  | regex _raw!="CONNECTOR_NAME" 
  | regex _raw!="CNF" 
  | regex _raw!="Detailed" 
  | 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 
] 
| lookup Worker_Connector_List.csv "Connector_Name" 
| search Worker_Node=EAS | stats values(Machine_Name) as Machine_Name, values(Port) as Port, values(Worker_Node) as Worker_Node, values(Connector_Count) as  Connector_Count, latest(Connector_State) as Connector_State, count(eval(Connector_State="RUNNING")) as Success_Count by Connector_Name
| table Machine_Name,Port,Worker_Node,Connector_Count,Success_Count

And it gave the below table:

Machine_NamePortWorker_NodeConnector_CountSuccess_Count
  EAS 1
  EAS 1
  EAS 0
  EAS 1
  EAS 0

This is not expected table. The table should have one row for each Worker_Node instead of one row of each Connector_Name.

0 Karma
Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎09-06-2022 12:38 PM

How are the events from the first search connected to the events of the second search?

0 Karma
Reply

Mrig342
Contributor
‎09-07-2022 09:53 AM

Hi @ITWhisperer 

First of all both the log types are from the same index, host and source and if I search "search index=ABC host=XYZ source=KLM" I get below data:

Log1: MACHINE@|@Port@|@Country@|@Count MEMORY STATUS
mwgcb-csrla01u|8070|EAS|5 CNF_| PASS|mwgcb-csrla01u PASS|mwgcb-csrla02u
Log2: source.mq.apac.sg.cards.eas.eas.raw.int.rawevent RUNNING|mwgcb-csrla02u RUNNING|mwgcb-csrla01u RUNNING|mwgcb-csrla02u
Log3: source.mq.apac.in.cards.eas.eas.raw.int.rawevent RUNNING|mwgcb-csrla01u FAILED|mwgcb-csrla02u NA
Log4: source.mq.apac.my.cards.eas.eas.raw.int.rawevent FAILED|mwgcb-csrla02u RUNNING|mwgcb-csrla01u NA
Log5: source.mq.apac.th.cards.eas.eas.raw.int.rawevent RUNNING|mwgcb-csrla01u RUNNING|mwgcb-csrla01u NA
Log6: source.mq.apac.hk.cards.eas.eas.raw.int.rawevent UNASSIGNED|mwgcb-csrla01u RUNNING|mwgcb-csrla01u RUNNING|mwgcb-csrla02u
Log7: MACHINE@|@Port@|@Country@|@Count MEMORY STATUS
mwgcb-csrla01u|8070|ESB|6 CNF_| PASS|mwgcb-csrla01u PASS|mwgcb-csrla02u
Log8: source.mq.apac.sg.cards.esb.esb.raw.int.rawevent RUNNING|mwgcb-csrla02u RUNNING|mwgcb-csrla01u RUNNING|mwgcb-csrla02u
Log9: source.mq.apac.in.cards.esb.esb.raw.int.rawevent RUNNING|mwgcb-csrla01u FAILED|mwgcb-csrla02u NA
Log10: source.mq.apac.my.cards.esb.esb.raw.int.rawevent FAILED|mwgcb-csrla02u RUNNING|mwgcb-csrla01u NA
Log11: source.mq.apac.th.cards.esb.esb.raw.int.rawevent RUNNING|mwgcb-csrla01u RUNNING|mwgcb-csrla01u NA
Log12: source.mq.apac.hk.cards.esb.esb.raw.int.rawevent UNASSIGNED|mwgcb-csrla01u RUNNING|mwgcb-csrla01u RUNNING|mwgcb-csrla02u
Log13: source.solace.apac.cn.digital.esb.esb.derived.int.esblogs RUNNING|mwgcb-csrla02u RUNNING|mwgcb-csrla01u NA

The first set of logs are giving the Count of Connectors available in each worker Node. And the second set is giving the Connector states from which I want to count the Count the connectors that are Running i.e. successful and put all together in a table in below manner.

Machine_NamePortWorker_NodeConnector_CountSuccess_Count
mwgcb-csrla01u8070EAS53
mwgcb-csrla01u8070ESB64

 

I hope this info helps. Please help me to create the table in the desired manner.

0 Karma
Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎09-08-2022 01:03 AM

How do you know which connector belongs to which node?

0 Karma
Reply

Mrig342
Contributor
‎09-08-2022 10:25 AM

Hi @ITWhisperer 

We have the connector count fixed for each node and in the search I am using a lookup table to identify which connector is for which node.

0 Karma
Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎09-08-2022 11:36 AM

Try changing the by clause to Machine_Name

0 Karma
Reply

Mrig342
Contributor
‎09-05-2022 10:15 PM

Hi All,

Can anyone please help me on this..?

0 Karma
Reply
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...