Need help with SWQL statement

Question

Hi all, 
 
 I am populating a servicenow action with the CI name with caption field from orion.nodes. Unfortuantely in SNOW all the CI's use shortname so when a FDQN is used it doesnt populate. Is it possible to use regex in a swql or sql query to return just the shortname? ^([^.]*) should return everything up to but not including the (.) but i dont know how to implement this. 
 thanks 
 Matt

KMSigma.SWI · Accepted Answer

Alternatively you could "fix" your caption names. 
 thwack.solarwinds.com/.../2980

Seashore · Answer

This SWQL should sort that out. Gives the "shortname" if a '.' is found, otherwise the whole name. 
 SELECT
CASE
 WHEN CharIndex('.',N.Caption)>0 THEN SubString(N.caption,0,CHARINDEX('.',N.Caption))
 ELSE N.Caption
END AS T
FROM Orion.Nodes AS N
WHERE N.NodeID=10 
 
 Where it says "WHERE N.NodeID=10" you need to change the "10" to a variable to suits your alert entity. Probably ${N=SwisEntity;M=NodeID} 
 Also, the SQL query need to be in one line, not in 7 lines as in my code above.

KITGuy · Answer

This is what I ended up doing. Thanks.

Need help with SWQL statement

Top Replies