This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Auto inject Site information

Hello Thwackies,

    So I am reaching out to ask what would be the best way to script an update to a Nodes custom properties based on a predetermined value set in the 'City' field. So we have a list of known Cities and the associated Address and site POC email. What I would like to see is when the City field is populated, 'Already done by other means', then the script would fill in the custom properties. We have a list of about 15 cities and a corresponding Address and email, so my initial thought was to have this bit of the script repeated for each city. And I also wonder if doing this directly in SQL would be best. This script should be set to run at least twice in a 24 hour period.   What I have so far is this:

# prepare a custom property value

$customProps = @{ 

  Address="123 Street London, England"

  SiteEmail="someguy@someplace.net"

     

# set the custom property

$uris = Get-SwisData $swis "SELECT CP.Uri, CP.City

FROM Orion.Nodes AS N

LEFT JOIN Orion.NodesCustomProperties AS CP ON N.NodeID = CP.NodeID

WHERE Vendor NOT LIKE 'Sierra%' AND City = 'London'"

$uris | Set-SwisObject $swis -Properties $customProps

     I've searched the forums and found bit and pieces to bring me this far, but I would love to hear any suggestions to make this better. Thanks in advance.

V/R, J Dixon

  • I've always done this directly in SQL, there is a pretty elegant solution to this and a number of related problems in a series of posts by user alexslv

    The Magic Of SQL Scripts: Do What Counts, Don't Count What You Do

    In short you create an additional table where you list all of the sites and the properties that would be related to them, such as addresses, POC, GPS coords, time zone offsets, etc

    Alex has a series of scripts in the posts to reference that table to automatically do things like setting up your dependency trees, placing objects on the worldwide map, and setting custom properties.

    Building this out as a script in powershell feels kind of clunky to me for this use case since you really are trying to sync up two tables of data anyway.  You could probably still use powershell and the api if you wanted to point it at a .csv and use that as the source of your sites array but I'm weaker in PS than SQL, has the benefit of being easier to access for people who aren't comfortable in SQL, but on the other hand you risk having someone access it and screw up your tables emoticons_wink.png.

  • I think I am inclined to agree with you about doing this in SQL. I actually did look a some of the posts by alexslv, but didn't see the entire series, with the extra bits of info in there I do believe I have found what I need to move forward with this confidently. So thanks :-)