Pull text file into Solarwinds then display in console?

robert777

Good Day All.

Just handed a new monitoring requirement, as follows:

Linux server runs a regular process to drop a text file onto a certain folder ("drop folder"), similar to below:

PROCESS1 VALUE1 OTHERVALUE1

PROCESS2 VALUE2 OTHERVALUE2

PROCESS3 VALUE3 OTHERVALUE3

...

The customer wants to ingest this file into Solarwinds, and then display the values in a Solarwinds console with rows and columns just like in the text file.

Other limitations: a) there are more than 10 lines in the text file, so it exceeds the maximum number of data elements returned by a script monitor, b) Solarwinds has an SSH login to the Linux host, but the system owners don't want to give that account any permissions other than to read the file drop folder, c) agent for Linux is not an option for now.

Best solution I can come up with is to...

assign the contents of the text file to a variable (Bash script)
send that loooooong string variable to Solarwinds in the 'Message' part of a return variable
use SQL to parse that text string into rows and columns in the data source of the display widget

I've been researching the SQL part on the Internet, and it seems like SQL can in fact parse a text string into rows and columns. However... I'm at best a Padawan SQL coder. Getting lost in the complexity of these example queries (lots of CHARINDEX, LEFT, STUFF, and UNION ALLs).

Btw I am aware that Solarwinds no longer retains historical Message data from script monitors. So we have only the latest read to work with.

Can anyone in the community provide a good example of using SQL to parse a long text string into rows and columns for display in a Solarwinds widget? Also please advise if there is a better way to do this given the limitations.

Thanks in advance for any responses.

Find more posts tagged with

text_file

sam

linux_script

sql_query

Accepted answers

adam.beedell

I have an example but it's very custom and so doesnt serve well as an example, I can tell you some routes through having been through this

1) Do as you described, use either REPLACE()s to deal with line breaks, and/or
2) Use FOR XML PATH and use that to build a table. (this is a faff but if it's a set and forget thing it's maybe worth working out. I have to refresh myself on this each time, but it's basically building a table in HTML
or
3) you can import the file into SQL using some sort of job (scheduled task, SSIS, fancy container thing, whatever) and use a SQL widget to call it up ***Note - not compatible with modern dashboards, and also you should secure that page if so

or
4) you could host that text somewhere and call for it with with a custom HTML object
...

adam.beedell

I'd probably try a stringsplit on the linebreak in a CTE, then transpose that, then stringsplit for columns or something. I guess those are triple spaces for you?

https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows

This is better but probably something you've got open already

You may be picking one of the harder options haha

The one I'd done keeps the data as a single cell in SQL, but then expands it out in HTML after it's prepped in SQL by using FOR XML PATH to put <tr> and <td> tags in

All comments

bobmarley

One solution to possibly look at is have the Linux servers send the PROCESS1 VALUE1 OTHERVALUE1 as a syslog message to your SolarWinds system. They will get stored into the database and then you can create any custom query you wish to display and you will retain historic data for a period of time.

adam.beedell

or
4) you could host that text somewhere and call for it with with a custom HTML object
...

bobmarley

I use Option #4 for several of the items displayed on the main screen. The small chunk of code in the custom HTML Object is below. This file is copied into the IIS directory

on the Orion server by the script that is calculating the results.

robert777

Thank you! This is very helpful.

One question: with regard to option #1 - sql programming on the display side to parse a large text field into rows and columns. Do you know of any examples where someone has done this? I'm at best a fair SQL programmer. Having some trouble figuring out how to make the field parsing work.

adam.beedell

robert777

Thank you everyone for all responses.

Postscript: the Linux admin I worked with on the monitor was reluctant to share details about the text file and the script that produced it. Once I actually got a look at the file I realized the script was just spitting out Linux process data for the target application.

In any case armed with that information I was able to create a Linux process monitor using snmp polling, with a dashboard displaying status and selected metrics for the processes making up the application.

Based on responses so far I think leadership prefers the SW process monitor / dashboard to the text file.

Cheers.