Parsing data presented in the form of a table

Elastic Stack Logstash
1

Hello team.
More and more often you have to work with logos that come to logstash in the form of a table:

Network Management Card AOS v3.7.3
Symmetra APP v1.1.2

Date     	Time     	Name	Contact	Location	System IP
23.03.2021	10:33:07	test-UPS02	QWER	 Elektric	10.10.10.1

		Symmetra LX 16000 RM   	   	   	   	   	   	   	   	   	   	   	   	   	   	   	Integrated EMU   	   	
Date	Time	Vmin1	Vmin2	Vmin3	Vmax1	Vmax2	Vmax3	Vout	Iout	%Wout	kVAout	%VAout	Freq	%Cap	Vbat	TupsC	TIambC	%HumI
18.03.2021	08:32:11	226.7	228.4	226.7	226.7	230.2	228.4	221.6	2.7	4	0.59	4	50.02	100.0	138.2	31.0	18.0	   	
18.03.2021	08:37:11	225.0	228.4	226.7	228.4	230.2	228.4	221.6	2.7	3	0.59	4	49.99	100.0	138.2	32.0	17.5	   	
18.03.2021	08:42:11	225.0	228.4	226.7	228.4	230.2	228.4	221.6	2.7	3	0.59	4	49.99	100.0	138.2	31.0	17.5	   	
18.03.2021	08:47:11	226.7	226.7	226.7	228.4	230.2	228.4	221.6	2.7	4	0.59	4	49.99	100.0	138.2	31.0	17.5	   	
18.03.2021	08:52:11	225.0	226.7	228.4	228.4	230.2	228.4	221.6	2.7	3	0.59	4	50.00	100.0	138.2	31.0	18.0	   	
18.03.2021	08:57:11	226.7	230.2	228.4	228.4	230.2	228.4	221.6	2.7	3	0.59	4	50.03	100.0	138.3	31.0	18.0	   	
18.03.2021	09:02:11	226.7	230.2	228.4	228.4	230.2	228.4	221.6	2.7	4	0.59	4	50.00	100.0	138.3	32.0	18.0	   	
18.03.2021	09:07:11	226.7	230.2	228.4	228.4	231.9	228.4	221.6	2.7	3	0.59	4	50.00	100.0	138.2	32.0	18.0	   	
18.03.2021	09:12:11	225.0	228.4	226.7	228.4	230.2	228.4	221.6	2.7	3	0.59	4	50.01	100.0	138.2	31.0	17.5	   	
18.03.2021	09:17:11	226.7	228.4	226.7	228.4	230.2	228.4	221.6	2.7	3	0.59	4	49.99	100.0	138.2	31.0	17.5	   	
18.03.2021	09:22:11	226.7	230.2	228.4	228.4	230.2	228.4	221.6	2.7	3	0.59	4	50.01	100.0	138.2	31.0	17.5	   	
18.03.2021	09:27:11	226.7	230.2	228.4	228.4	230.2	228.4	221.6	2.7	3	0.59	4	49.98	100.0	138.2	31.0	17.5	   	
18.03.2021	09:32:11	226.7	230.2	228.4	228.4	230.2	228.4	221.6	2.7	3	0.59	4	49.98	100.0	138.2	31.0	17.5	   	
18.03.2021	09:37:11	226.7	228.4	228.4	228.4	230.2	228.4	221.6	2.7	3	0.59	4	49.99	100.0	138.2	31.0	18.0	   	
18.03.2021	09:42:11	226.7	230.2	228.4	228.4	231.9	228.4	221.6	2.7	3	0.59	4	49.99	100.0	138.3	31.0	17.5

Depending on the equipment model, the number of columns and the description of the model before the table may vary.
and
Another type of data in the form of a table that arrives at the input:

Security audit trail analysis

The requested period is 05/09/2021 00:00:00 - 05/09/2021 23:59:59
Period selected 09.05.2021 00:00:00 - 09.05.2021 00:00:06
Server s-qz-testq-b1 v-sap-testq05 v-sap-testq12 v-sap-testq10
... v-sap-testq08 v-sap-testq02 v-sap-testq06 v-sap-testq04 v-s
... v-sap-testq13 v-sap-testq16 v-sap-testq14 v-sap-testq01
Critical Events 16
Important events 0
Other events 64.873

+--------------------+----------+--------+---+------------+--------------------+--------------------+------------------------------+--------------------+------------------------------+------+------+------+
| Instance name      | Date     | Time   |MDT| User       | Terminal name      | Transaction code   | Program                      | Text message cont  | VariableData for Messages    | Data | Data | Data |
+--------------------+----------+--------+---+------------+--------------------+--------------------+------------------------------+--------------------+------------------------------+------+------+------+
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:06|300|PM_AZURE_MOB|                    |                    |IREAD_SM_PM_ORDERS            |                    |IREAD_SM_PM_ORDERS            |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:06|300|PM_AZURE_MOB|                    |                    |RBDAGAIN                      |                    |RBDAGAIN                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:06|300|PM_AZURE_MOB|                    |                    |RBDMANIN                      |                    |RBDMANIN                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:06|300|PM_AZURE_MOB|                    |                    |RBDMANI2                      |                    |RBDMANI2                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:06|300|PM_AZURE_MOB|                    |                    |ZPM_205N                      |                    |ZPM_205N                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:07|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:07|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:07|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:08|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:09|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:09|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:09|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:09|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:10|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:13|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:16|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:17|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:18|300|PM_AZURE_MOB|                    |                    |SAPMSSY4                      |                    |SAPMSSY4                      |      |      |      |
+--------------------+----------+--------+---+------------+--------------------+--------------------+------------------------------+--------------------+------------------------------+------+------+------+
+--------------------+----------+--------+---+------------+--------------------+--------------------+------------------------------+--------------------+------------------------------+------+------+------+
| Instance name      | Date     | Time   |MDT| User       | Terminal name      | Transaction code   | Program                      | Text message cont  | VariableData for Messages    | Data | Data | Data |
+--------------------+----------+--------+---+------------+--------------------+--------------------+------------------------------+--------------------+------------------------------+------+------+------+
|s-qz-testq-b1_PEN_00|09.05.2021|00:15:06|300|PM_AZURE_MOB|                    |                    |RBDAGAIN                      |                    |RBDAGAIN                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:15:06|300|ERP_RFC_USER|                    |                    |ZDM_063N_RUN_IDOC2            |                    |ZDM_063N_RUN_IDOC2            |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:15:21|300|ERP_RFC_USER|                    |                    |RSEOUT00                      |                    |RSEOUT00                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:17:06|300|ERP_RFC_USER|                    |                    |ZDM_063N_RUN_IDOC2            |                    |ZDM_063N_RUN_IDOC2            |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:17:22|300|ERP_RFC_USER|                    |                    |RSEOUT00                      |                    |RSEOUT00                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:19:06|300|ERP_RFC_USER|                    |                    |ZDM_063N_RUN_IDOC2            |                    |ZDM_063N_RUN_IDOC2            |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:19:21|300|ERP_RFC_USER|                    |                    |RSEOUT00                      |                    |RSEOUT00                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:21:06|300|ERP_RFC_USER|                    |                    |ZDM_063N_RUN_IDOC2            |                    |ZDM_063N_RUN_IDOC2            |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:21:21|300|ERP_RFC_USER|                    |                    |RSEOUT00                      |                    |RSEOUT00                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:23:06|300|ERP_RFC_USER|                    |                    |ZDM_063N_RUN_IDOC2            |                    |ZDM_063N_RUN_IDOC2            |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:23:21|300|ERP_RFC_USER|                    |                    |RSEOUT00                      |                    |RSEOUT00                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:25:06|300|ERP_RFC_USER|                    |                    |ZDM_063N_RUN_IDOC2            |                    |ZDM_063N_RUN_IDOC2            |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:25:21|300|ERP_RFC_USER|                    |                    |RSEOUT00                      |                    |RSEOUT00                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:27:06|300|ERP_RFC_USER|                    |                    |ZDM_063N_RUN_IDOC2            |                    |ZDM_063N_RUN_IDOC2            |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:27:21|300|ERP_RFC_USER|                    |                    |RSEOUT00                      |                    |RSEOUT00                      |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:29:06|300|ERP_RFC_USER|                    |                    |ZDM_063N_RUN_IDOC2            |                    |ZDM_063N_RUN_IDOC2            |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:29:21|300|ERP_RFC_USER|                    |                    |RSEOUT00                      |                    |RSEOUT00                      |      |      |      |

Please advise which module can be used to reverse this type of data.

write grok and many conditions i think it's a bad idea

2

Second log with "|" I managed to normalize, in general, everything is fine.

| Instance name      | Date     | Time   |MDT| User       | Terminal name      | Transaction code   | Program                      | Text message cont  | VariableData for Messages    | Data | Data | Data |
+--------------------+----------+--------+---+------------+--------------------+--------------------+------------------------------+--------------------+------------------------------+------+------+------+
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:06|300|PM_AZURE_MOB|                    |                    |IREAD_SM_PM_ORDERS            |                    |IREAD_SM_PM_ORDERS            |      |      |      |
|s-qz-testq-b1_PEN_00|09.05.2021|00:00:06|300|PM_AZURE_MOB|                    |                    |RBDAGAIN                      |                    |RBDAGAIN                      |      |      |      |

but I still have a problem with parsing the first type of logs
so i need to parse two tables, and add some of the data of the first table to every row of the second table
table 1

Date     	Time     	Name	Contact	Location	System IP
23.03.2021	10:33:07	test-UPS02	QWER	 Elektric	10.10.10.1

table 2

Date	Time	Vmin1	Vmin2	Vmin3	Vmax1	Vmax2	Vmax3	Vout	Iout	%Wout	kVAout	%VAout	Freq	%Cap	Vbat	TupsC	TIambC	%HumI
18.03.2021	08:32:11	226.7	228.4	226.7	226.7	230.2	228.4	221.6	2.7	4	0.59	4	50.02	100.0	138.2	31.0	18.0	   	
18.03.2021	08:37:11	225.0	228.4	226.7	228.4	230.2	228.4	221.6	2.7	3	0.59	4	49.99	100.0	138.2	32.0	17.5

the end result in my opinion should look like:

"Name=test-UPS02,"Contact"=QWER,"Location"=Elektric,"System IP"=10.10.10.1,"Date"=18.03.2021,"Time"=08:32:11,"Vmin1"=226.7,"Vmin2"=228.4,"Vmin3"=226.7,"Vmax1"=226.7,"Vmax2"=230.2,"Vmax3"=228.4,"Vout"=221.6,"Iout"=2.7,"%Wout"=4,"kVAout"=0.59,"%VAout"=4,"Freq"=50.02,"%Cap"=100.0,"Vbat"=138.2,"TupsC"=31.0,"TIambC"=18.0,"%HumI"=2,``
3

Any ideas?

4

at least some ideas how you can transfer / add data from the first table to the second?

5

You would need to use a ruby filter. There is an example here and another here.

1 Like
6

Badger,thanks for the parsing suggestion. I'll try to figure out how it works. I haven't used Ruby in my configurations before.
I've come across similar Ruby posts before, but I couldn't figure out how to use them in my code.

Another question, I have several types of files with a different number of columns, can I somehow differentiate them among themselves by the number of columns in order to use the correct headers for each of them in the future?

examples
1.

Date	Time	Vmin1	Vmin2	Vmin3	Vmax1	Vmax2	Vmax3	Vout	Iout	%Wout	kVAout	%VAout	Freq	%Cap	Vbat	TupsC	TIambC	%HumI
18.03.2021	08:32:11	226.7	228.4	226.7	226.7	230.2	228.4	221.6	2.7	4	0.59	4	50.02	100.0	138.2	31.0	18.0	   	
18.03.2021	08:37:11	225.0	228.4	226.7	228.4	230.2	228.4	221.6	2.7	3	0.59	4	49.99	100.0	138.2	32.0	17.5	   	
18.03.2021	08:42:11	225.0	228.4	226.7	228.4	230.2	228.4	221.6	2.7	3	0.59	4	49.99	100.0	138.2	31.0	17.5	   	
18.03.2021	08:47:11	226.7	226.7	226.7	228.4	230.2	228.4	221.6	2.7	4	0.59	4	49.99	100.0	138.2	31.0	17.5	   	
18.03.2021	08:52:11	225.0	226.7	228.4	228.4	230.2	228.4	221.6	2.7	3	0.59	4	50.00	100.0	138.2	31.0	18.0	

Date	Time	Vmin1	Vmin2	Vmin3	Vmax1	Vmax2	Vmax3	Vbp1	Vbp2	Vbp3	Iin1	Iin2	Iin3	Vout1	Vout2	Vout3	Iout1	Iout2	Iout3	%Wout1	%Wout2	%Wout3	kVAout1	kVAout2	kVAout3	Freq	%Cap	Vbat	Ibat	TupsC	T1ambC	%Hum1	T2ambC	%Hum2
18.03.2021	03:44:49	226.0	225.0	226.6	227.4	226.1	228.3	226.3	225.6	226.4	7.79	6.18	6.41	231.5	231.1	230.9	2	1	18	3	3	32	0.6	0.4	4.2	49.99	100.0	218.7	+0.0	22.3	   	   	24.0	   	
18.03.2021	03:49:49	226.2	225.0	226.7	227.4	226.3	228.5	227.2	226.0	226.3	7.79	6.20	6.45	230.9	231.2	230.9	2	1	18	3	3	32	0.6	0.3	4.2	50.00	100.0	218.7	+0.0	22.3	   	   	24.0	   	
18.03.2021	03:54:49	225.0	223.7	225.7	227.1	225.9	227.8	226.1	224.4	226.3	7.76	6.19	6.39	230.6	231.2	231.4	2	1	18	3	3	31	0.6	0.4	4.2	50.00	100.0	219.0	+0.0	22.2	   	   	24.0	   	
18.03.2021	03:59:49	224.6	223.6	225.5	227.0	225.7	227.6	226.3	225.3	225.5	7.82	6.27	6.43	230.5	231.4	231.2	2	1	18	3	3	32	0.6	0.4	4.2	49.99	100.0	218.7	+0.0	22.3	   	   	24.0	   	
18.03.2021	04:04:49	224.3	223.2	224.7	226.6	225.2	227.1	224.3	223.3	224.1	7.87	6.29	6.46	230.8	231.0	231.4	2	1	18	3	3	32	0.6	0.3	4.1	49.99	100.0	218.7	+0.0	22.3	   	   	24.0	   	

Date	Time	Vmin	Vmax	Vout	%Wout	Freq	%Cap	Vbat	TupsC
18.03.2021	06:44:43	228.9	228.9	219.9	11.0	49.99	100.0	54.54	25.2	
18.03.2021	06:49:43	228.9	228.9	221.1	11.0	50.05	100.0	54.54	25.2	
18.03.2021	06:54:43	228.9	228.9	221.1	12.0	49.99	100.0	54.54	25.2	
18.03.2021	06:59:43	228.9	228.9	221.1	12.0	50.00	100.0	54.54	25.2
7

I use the ruby code, it partially worked - metadata was added to some lines, but not to others - in kibana _rubyexception. I can't understand why it works selectively, the messages are of the same type

8

See here for an example.

1 Like
9

Badger, thank you so much. The column counting code works fine. But we had to refuse to add metadata that was found at the beginning of the file, because subsequently, only new lines are transmitted and the initial table only once at the time of reading the file, or you need to constantly re-read the file from the beginning.

10

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.