is a an easy way remove domain name from the node caption

I like to see all my devices listed as name only, without the domain name added to the end. It makes the alerts shorter and easier to read on SMS messages, and it just looks nicer, I think.

Anyway, i have been removing the domain name by going to Manage Nodes, then editing the node properties to manually remove the domain from the end of each node name. Its not that big of a deal, but its very time consuming if i import a large number of nodes.

Is there any way to make this process more effectient? Maybe a way to do this in bulk or with a SQL update script?

Thanks,

Jeremy

Find more posts tagged with

device_name

Accepted answers

jcooler

So her is the SQL script that removes the domain names from the caption field. Thanks again for giving the code to make this happen.

UPDATE Nodes
SET caption = (
    SELECT RTRIM( SUBSTRING(
        Caption, 1,( (
            Select CASE
            WHEN (CharIndex ('.', Caption) > 0)
              THEN (CharIndex ('.', Caption)-1)
              ELSE (LEN(Caption))
            END ) )
            )
         )
)

All comments

njoylif

I wanted this as well.
The caption field is auto populated and will be overwritten if you change, so...

Basically, I created a new column called HName (originally used HostName, but Netflow uses that and it caused issues).

This assumes that you don't have any periods (.) in your device name as that is what I cut off from the right using...
Here is a SQL script that I run daily to populate that field.

UPDATE Nodes
SET HostName = (
    SELECT RTRIM( SUBSTRING(
        Caption, 1,( (
            Select CASE
            WHEN (CharIndex ('.', Caption) > 0)
            THEN (CharIndex ('.', Caption)-1)
            ELSE (LEN(Caption))
            END ) )
            )
        )
WHERE HostName is Null"

jcooler

I have not had any problems with the node caption getting over written with the $sysname after i manually change it. I'm running NPM 9.5 SP2. Is this something that happened in another version maybe? Or maybe something happens that overwrites the caption field, and it just hasn't happened to mine yet. I have only been running NPM for about 2 months now.

The script you provided above should give me the basic steps to accomplish what I need to do though. So thank you for that.

I have a question though. You said that you are now using "HName" for the custom property field, but your script is updating the "Hostname" field. Am i misunderstanding something on that one?

I'll post my updated script and the results of whether it worked or not.

Thanks for your help

jcooler

So her is the SQL script that removes the domain names from the caption field. Thanks again for giving the code to make this happen.

njoylif

you are correct, that was a script I had saved off in archive and hadn't changed that yet. Glad to see it worked with your tweaks.

cnorborg

I see there is an open feature request on this for all those who want to vote on it!

https://thwack.solarwinds.com/ideas/3314

rschroeder

Having just migrated off an old version of NPM, can you help me find the spot where this SQL script should be applied?

If you don't mind my asking, it would help our team if you could provide the sequence of steps to get into the location, and the tool(s) to use to apply the script. Feel free to be as literal and specific as possible, to remove the possibilities of any assumptions (for those of us who do not script SQL with any regularity).