cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Set Caption = stripped FQDN

We've had a SQL job for a long time now that does this job. I'd like to do this instead via SWQL, and not sure how to write it. Here is the SQL job. It basically forces Caption to equal the FQDN stripped down to the Hostname:

 

Update NodesData
SET Caption = LEFT(DNS, CHARINDEX('.', DNS) - 1)
WHERE CHARINDEX('.', DNS) > 0
AND Caption <> LEFT(DNS, CHARINDEX('.', DNS) - 1)

 

I know how to modify the Caption via SWQL, just not sure about the WHERE clause, which doesn't seem to work in SWQL Studio as is. Maybe that SQL syntax cannot be used exactly as is, and needs some tweaking.

Any help will be greatly appreciated.

 

Tags (1)
0 Kudos
10 Replies
MVP
MVP

Why not just adjust the ones where the sysname != caption?

0 Kudos

because DNS hostname and SysName.are quite otten different in our environment. And the one we want is DNS hostname.

0 Kudos

Ah sorry, DNS then. What I meant was to avoid the complicated comparison between the existing caption and whatever column you want it to look like after the change.

0 Kudos

SWQL itself is read only. You would need to use the SDK verbs to update the captions.
I do have a script sitting around somewhere that I can share. I just have to find it.
0 Kudos

It does seem in the latest download of SWQL studio there is an update option, but I don't quite understand it.  Maybe someone could take this opportunity to esplain it to us (in a cuban accent). 

0 Kudos

You can perform CRUD operations on many of the entities that SWIS provides, but updates don't work like SQL UPDATE statements, where you can specify the nature of the update operation to perform and the filtering logic in a single statement.  Instead, you would need to perform one query to identify the nodes where the caption needs to be updated, and then you would perform a series of updates (one per node) to change the caption.  An example of updating properties via the API using Python is supplied below.  You can perform the same via PowerShell or REST.

https://thwack.solarwinds.com/t5/Discussions/How-Do-I-Change-Caption-Node-Interface-Volume-Via-API/m...

0 Kudos

sorry, I was hoping I wouldn't confuse what I'm looking for. I already know how to update Nodes via SWIS. I just showed the SQL syntax for reference. That SQL job, I want to do the same thing via API, and I'm just having issues with the WHERE clause.

0 Kudos

ok I'm definitely confusing things. Before I write a Python script to update the Nodes, I want to query them in SWQL Studio so I can see how many will be affected. And that's where I'm trying to use this WHERE clause (which originated from a TSQL script).

SELECT Caption, DNS, SysName
FROM Orion.Nodes
WHERE CHARINDEX('.', DNS) > 0
AND Caption <> LEFT(DNS, CHARINDEX('.', DNS) - 1)

 

Sorry for all the confusion.

0 Kudos

Remember that SWQL is NOT SQL.    There is a subset of functions available to the queries that you do.

In this case the LEFT function is not available, so you'd need to figure out how to substitute it.   Sometimes its easier to do it in the programming language you have wrapping this query, sometimes in the query.   I can't think of a good SWQL function that approximates LEFT, so...

 

https://github.com/solarwinds/OrionSDK/wiki/SWQL-Functions

0 Kudos

SUBSTRING() is available. LEFT(str, n) is equivalent to SUBSTRING(str, 1, n).