Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

SWQL - Split string into multiple rows

Hi,

I'm trying to split a string into multiple rows and can not work out how to do it or even if it is possible.

I can split the string into multiple columns successfully so I'm assuming the functionally maybe there to split to rows.

Thanks

Craig Boyce

Find more posts tagged with

Accepted answers

All comments

ioan_bucsa

Depends on how many rows you would like to generate. You could use multiple SUBSTRING expressions if the complexity not that high.

mesverrum

In SQL turning a single row of data into multiple rows is a bit tricky, and SWQL is even more limited. You could probably do something with a UNION. Can you post the query you have now to make it easier to give a concrete example?

cboyce

Hi Mesverrum, Ioan_bucsa,

The context is that I have a Powershell Monitor that is pulling the list of VDI's, The User / Group entitled to use it, The logged on user, The status of the connection

Due to the limitation of 10 messages I have put it all together in one long string using '$' as a delimiter with the output that looks like this

VDIWIN10D126$xxxx.int\xxxxxx$xxxxxx$AVAILABLE$VDIHCL2013$xxxx.int\xxxxxx$xxxxx$DISCONNECTED$VDISDW1003$ $ $AVAILABLE$VDIPRO1011$ $ $AVAILABLE$VDIWIN10D031$xxxx.int\xxxxxx$xxxxxx$
CONNECTED$VDIWIN10F004$ $ $AVAILABLE$VDIWIN10F019$ $ $CONNECTED$VDIBREM1001$ $ $AVAILABLE$SGCPS2014$xxxx.int\xxxxxxx$xxxxxxx$AVAILABLE

I can split the string into the 4 columns with the below query to get the first record.

SELECT StringData as ArrVal

,ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(ArrVal,'$','|§|§|')),0) AS [VDI]
,ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(ArrVal,'$','|§|§|')),1) AS [Entitlement]
,ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(ArrVal,'$','|§|§|')),2) AS [Logged on User]
,ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(ArrVal,'$','|§|§|')),3) AS [Connection Status]

FROM Orion.APM.MultipleStatisticData Where ComponentID = '176620'

VDI	Entitlement	Logged on User	Connection Status
VDIABB1001			AVAILABLE

Now I'm trying to split it into multiple rows after each 4th record in the string.

Maybe this is not the path to go down to get around the 10 message limitation but I thought I would try it.

Regards

Craig

jfoxrfc3092

If I understand correctly, you have a long string in a PoSH script separated by $ characters. You could do this:

## Split the string using $ as the delimiter.

$longString -split "$"

## This will produce separate strings that you can edit further. The first string would be $longstring[0].

Does this help?

ioan_bucsa

Actually scratch that; SUBSTRING generates a new column rather than a new row.

ioan_bucsa

@cboyce Tough one. Like @mesverrum mentioned, this stuff is tricky even in SQL.

Trying to think if there isn't a better approach altogether...

mesverrum

Honestly for use cases like this I've always just wrote myself a script in powershell to create separate components for each vdi name. I would do something like my base template only contains a single component with the script set up to filter using the vdi name as an argument and tell it to pull the 4 values back as separate statistics with their own logic around thresholds and such. Then I'd just run a scheduled task to query the server maybe once every few hours to get the list, then check against my component list in orion for that node and every time it sees a new VDI instance that doesnt have a matching component it just reaches in the database and creates a fresh component monitor with all the same componentsettings except i change the argument to my next vdi. I don't have any samples of that kind of code handy right now because I haven't done it in a while but hopefully that makes sense.

Probably worth mentioning that this is only a good idea where you have an old unlimited SAM license or the new node based license, because it can quickly eat up a lot of components for people who are limited on those.

cboyce

Thanks ioan_bucsa, I have looked at other options with no success so far.

I have tried writing the data directly into a table in the Solarwinds database but then found SWQL could only look at it's own tables.

Any suggestions are welcome

cboyce

Hi Mesverrum,

I had not considered that option, It is something I'll look into.

Would mean about 400 components to cover all the VDI's though, We have an unlimited SAM license so we are fine that regard.

Craig