topic Re: Need help creating a table from column type logs in Dashboards & Visualizations

Need help creating a table from column type logs

Mrig342 — Thu, 30 Jun 2022 10:10:31 GMT

Hi All,

I am trying to create a table out of the log below:

log:

ServerA ServerB ServerC
ADFILES41-6.2-4 not_available ADFILES41-6.2-4.2
ADM41-5.10.1-4 ADM41-5.10.1-4 ADM41-5.10.1-4
ADM41HF-5.10.1HF004-4 ADM41HF-5.10.1HF004-4 ADM41HF-5.10.1HF004-4
ADM42-5.11-4 ADM42-5.11-4 ADM42-5.11-4
ADM42HF-5.11HF03-4 ADM42HF-5.11HF03-4 not_available
TRA42-5.11-4 TRA42-5.11-4 not_available
not_available ADFILES42-6.2-4 not_available
not_available not_available TRA42-5.13-4

Here you can see that the 1st line gives the server names. 2nd, 3rd,4th and so on lines are applications available in the server.

For eg. From 2nd line you can see that the application ADFILES41-6.2-4 is available in A&C but not in B. Similarly from 9th line you can see that the application TRA42-5.13-4 is available in C but not in A&B.

So the requirement is to create a table in the below way to show if any servers is missing any application.

Server	ServerA	ServerB	ServerC
Application	ADFILES41-6.2-4	not_available	ADFILES41-6.2-4
Application	ADM41-5.10.1-4	ADM41-5.10.1-4	ADM41-5.10.1-4
Application	ADM41HF-5.10.1HF004-4	ADM41HF-5.10.1HF004-4	ADM41HF-5.10.1HF004-4
Application	ADM42-5.11-4	ADM42-5.11-4	ADM42-5.11-4
Application	ADM42HF-5.11HF03-4	ADM42HF-5.11HF03-4	not_available
Application	TRA42-5.11-4	TRA42-5.11-4	not_available
Application	not_available	ADFILES42-6.2-4	not_available
Application	not_available	not_available	TRA42-5.13-4

Please help me to create a query to get the table in the desired manner.

Any help on the problem would be highly appreciated.

Thank you All..!!

Re: Need help creating a table from column type logs

richgalloway — Thu, 30 Jun 2022 12:30:59 GMT

Check out the multikv command. It helps to ingest table-formatted data like that.

Re: Need help creating a table from column type logs

Mrig342 — Sat, 02 Jul 2022 16:56:09 GMT

Thank you @richgalloway ...!!

The command multikv helped me break the events per line-wise. However when I used table command to create a table for dashboard it is not what I desired.

I used this query "**** | multikv forceheader=1 | table ServerA,ServerB,ServerC" and this gave the table as:

Server	ServerA	ServerB	ServerC
Application	ADFILES41-6.2-4		not_available ADFILES41-6.2-4
Application	ADM41-5.10.1-4		ADM41-5.10.1-4 ADM41-5.10.1-4
Application	ADM41HF-5.10.1HF004-4		ADM41HF-5.10.1HF004-4 ADM41HF-5.10.1HF004-4
Application	ADM42-5.11-4		ADM42-5.11-4 ADM42-5.11-4
Application	ADM42HF-5.11HF03-4		ADM42HF-5.11HF03-4 not_available
Application	TRA42-5.11-4		TRA42-5.11-4 not_available
Application	not_available	ADFILES42-6.2-4	not_available
Application	not_available	not_available	TRA42-5.13-4

I am not able to understand what went wrong here in the query, as the logs are equally spaced and are in a tabular format.

Please help me identify what might have gone wrong or help me modify the query to get the desired output.

Any help on this is highly appreciated.

Thank You All..!!

Re: Need help creating a table from column type logs

richgalloway — Sat, 02 Jul 2022 20:36:43 GMT

Double-check the spacing of the columns. The multikv command is thrown off by uneven columns.

This worked for me:

| makeresults | eval _raw="ServerA ServerB ServerC ADFILES41-6.2-4 not_available ADFILES41-6.2-4.2 ADM41-5.10.1-4 ADM41-5.10.1-4 ADM41-5.10.1-4 ADM41HF-5.10.1HF004-4 ADM41HF-5.10.1HF004-4 ADM41HF-5.10.1HF004-4 ADM42-5.11-4 ADM42-5.11-4 ADM42-5.11-4 ADM42HF-5.11HF03-4 ADM42HF-5.11HF03-4 not_available TRA42-5.11-4 TRA42-5.11-4 not_available not_available ADFILES42-6.2-4 not_available not_available not_available TRA42-5.13-4" | multikv forceheader=1 | table ServerA ServerB ServerC

Re: Need help creating a table from column type logs

Mrig342 — Tue, 05 Jul 2022 16:11:56 GMT

Thank you @richgalloway for your insights.

I checked for the spaces between the values and they were equally spaced. Still couldn't get the expected result.

Then I replaced the normal spaces between the values with tab spaces in the logs. And that gave me the result in the expected manner.