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!

Don't wait! Accept the Mission Possible: Splunk Adoption Challenge Now and Win ...

Attention everyone! We have exciting news to share! We are recruiting new members for the Mission Possible: ...

Unify Your SecOps with Splunk Mission Control

In today’s post, I'm excited to share some recent Splunk Mission Control innovations. With Splunk Mission ...

Data Preparation Made Easy: SPL2 for Edge Processor

By now, you may have heard the exciting news that Edge Processor, the easy-to-use Splunk data preparation tool ...