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..!!
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
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_Name | Port | Worker_Node | Connector_Count | Success_Count |
3 |
Please help me to create/modify the script to get the expected table.
You kind help is highly appreciated..!!
What was your full search for this result?
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_Name | Port | Worker_Node | Connector_Count | Success_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.
How are the events from the first search connected to the events of the second search?
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_Name | Port | Worker_Node | Connector_Count | Success_Count |
mwgcb-csrla01u | 8070 | EAS | 5 | 3 |
mwgcb-csrla01u | 8070 | ESB | 6 | 4 |
I hope this info helps. Please help me to create the table in the desired manner.
How do you know which connector belongs to which node?
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.
Try changing the by clause to Machine_Name
Hi All,
Can anyone please help me on this..?