2 Replies Latest reply on Dec 1, 2017 11:00 AM by jdixon77

    Auto inject Site information

    jdixon77

      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

        • Re: Auto inject Site information
          mesverrum

          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 .

          1 of 1 people found this helpful
            • Re: Auto inject Site information
              jdixon77

              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 :-)