7 Replies Latest reply on Aug 11, 2017 12:54 PM by Manilyn Ramos

    python/sql variables

    timboslice90

      Was hoping someone had some experience pulling a single field out of a query and assigning it to a variable inside a python script.

       

      Use Case: I want to pull the interfaceName field and assign a variable to that specific field that i can use in my python script. I understand how to query the database through the swisclient python module, but am having a hard time finding any documentation on how to assign a variable based on the output. Of course if there is a smarter way I should be looking at this i'm open to new ideas.

        • Re: python/sql variables
          tdanner

          I recommend playing with swisclient in the Python repl (interactive mode). This way you can see the structure of the object returned from calling .query() and figure out how to extract the value you want into a python variable.

          1 of 1 people found this helpful
          • Re: python/sql variables
            Steven Klassen

            Hey there,

             

            You'll want to set up your development environment and get the orionsdk module installed first:

             

            [sklassen@L1HDSandbox ~]$ mkproject thwack
            Using base prefix '/usr/local'
            New python executable in /home/sklassen/.virtualenvs/thwack/bin/python3.5
            Also creating executable in /home/sklassen/.virtualenvs/thwack/bin/python
            Installing setuptools, pip, wheel...done.
            virtualenvwrapper.user_scripts creating /home/sklassen/.virtualenvs/thwack/bin/predeactivate
            virtualenvwrapper.user_scripts creating /home/sklassen/.virtualenvs/thwack/bin/postdeactivate
            virtualenvwrapper.user_scripts creating /home/sklassen/.virtualenvs/thwack/bin/preactivate
            virtualenvwrapper.user_scripts creating /home/sklassen/.virtualenvs/thwack/bin/postactivate
            virtualenvwrapper.user_scripts creating /home/sklassen/.virtualenvs/thwack/bin/get_env_details
            Creating /home/sklassen/dev/thwack
            Setting project for thwack to /home/sklassen/dev/thwack
            (thwack) [sklassen@L1HDSandbox thwack]$ pip install orionsdk
            Collecting orionsdk
            Collecting six (from orionsdk)
              Using cached six-1.10.0-py2.py3-none-any.whl
            Collecting requests (from orionsdk)
              Using cached requests-2.9.1-py2.py3-none-any.whl
            Installing collected packages: six, requests, orionsdk
            Successfully installed orionsdk-0.0.3 requests-2.9.1 six-1.10.0
            (thwack) [sklassen@L1HDSandbox thwack]$
            

             

            Then this should get you the node and interface names. Of course add your own local SolarWinds npm_server, username, and password:

             

            #!/usr/bin/env python
            import requests
            from orionsdk import SwisClient
            
            
            npm_server = '<hostname>'
            username = '<username>'
            password = '<password>'
            
            verify = False
            if not verify:
                from requests.packages.urllib3.exceptions import InsecureRequestWarning
                requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
            
            def main():
                swis = SwisClient(npm_server, username, password)
            
                query = """
                    SELECT TOP 10
                        n.NodeID,
                        n.Caption AS NodeName,
                        i.InterfaceID,
                        i.Caption AS InterfaceName
                    FROM
                        Orion.Nodes n
                    JOIN
                        Orion.NPM.Interfaces i ON n.NodeID = i.NodeID
                """
            
                results = swis.query(query)
            
                for row in results['results']:
                    print("{NodeID} [{NodeName}] : {InterfaceID} [{InterfaceName}]".format(**row))
            
            
            if __name__ == '__main__':
                main()
            
            # Sample Output...
            #
            # 3 [L1HDESX03] : 168 [Device vmnic0 at 01:00.0 bnx2 · 01:00.0]
            # 3 [L1HDESX03] : 169 [Traditional Virtual VMware switch: vSwitch0 · vSwitch0]
            # 9 [L1S2811] : 10 [FastEthernet0/0 · Corp Network]
            # 9 [L1S2811] : 11 [FastEthernet0/1 · Classroom Network]
            # 12 [L1CORPESX01.l1s.local] : 190 [Device vmnic0 at 03:00.0 bnx2 · 03:00.0]
            # 27 [L1SALESORION] : 134 [Intel(R) PRO/1000 MT Network Connection · Local Area Connection]
            # 45 [L1S-4948.loop1systems.com] : 53 [GigabitEthernet1/44 - Gi1/44]
            # 45 [L1S-4948.loop1systems.com] : 55 [GigabitEthernet1/48 - Gi1/48]
            # 46 [L1SCRMSQL] : 283 [Broadcom BCM5709C NetXtreme II GigE (NDIS VBD Client) #35 · Local Area Connection 3]
            # 60 [L1TRAINSQL] : 58 [Broadcom BCM5708C NetXtreme II GigE (NDIS VBD Client) · Local Area Connection]
            

             

            --

            Steven W. Klassen

            Programmer Analyst @ Loop1 Systems

            http://www.loop1systems.com/

            http://www.linkedin.com/in/mrxinu

            2 of 2 people found this helpful
              • Re: python/sql variables
                clarv02

                I'm doing something similar and need to create a list from the NodeID's. By the way, thanks for the tip on formatting rows for output - that works great!

                 

                Thanks in advance.

                 

                Val

                  • Re: python/sql variables
                    Steven Klassen

                    Hey Val, what are you trying to do with the list of NodeIDs?

                      • Re: python/sql variables
                        clarv02

                        hi Steven,

                         

                        After we get the NodeID's in a list, we'll modify custom properties for each node in the list. We came up with this earlier today and it does the trick. Let me know what you think:

                         

                        nodelist = swis.query("SELECT NodeID, Caption AS NodeName, IPAddress FROM Orion.Nodes WHERE Caption LIKE 'rtus00375%'")

                        print(nodelist)

                         

                        NodeBucket = []

                         

                        for row in nodelist['results']:

                          print("{NodeID} [{NodeName}] [{IPAddress}]".format(**row))

                          nodeid = row['NodeID']

                          print(nodeid)

                          NodeBucket.append(nodeid)

                          print(NodeBucket)

                         

                        def main():

                          for x in NodeBucket:

                          print(x)

                          print("Custom Property Update Test:")

                          results = swis.query(

                          "SELECT Uri FROM Orion.Nodes WHERE NodeID=@id",
                          id=x)

                          uri = results['results'][0]['Uri']

                          swis.update(uri + '/CustomProperties', Store_Number=StoreNum)

                          swis.update(uri + '/CustomProperties', BusinessUnit=BU)

                          swis.update(uri + '/CustomProperties', City=City)

                          swis.update(uri + '/CustomProperties', Country=Country)

                          obj = swis.read(uri + '/CustomProperties')

                          print(obj)

                  • Re: python/sql variables
                    mblackburn

                    If you set your python swiss query up exactly like the example in the video swiss api on youtube then the results of your swql query will land in a dictionary in python under data["results"] ---each row in query is a new index number in results with each each thing you query is an item of key,value example:

                     

                    data = swiss.query('SELECT DateTime, InAveragebps,OutAveragebps FROM Orion.NPM.InterfaceTraffic where InterfaceID = ' + str(interfaces) + ' AND DateTime>ADDHOUR(-96, GetDate()) ORDER by DateTime ASCENDING').json()

                     

                    timedata=(data["results"][2].items()[2][1])

                     

                    timedata takes thew value of the 3th row in results (index starts at 0), third item (again index at 0) and im asking for the value not the key the key is 0 the value is 1

                     

                    so data becomes:

                     

                    results:

                           

                     

                    Results

                    0

                    1

                    2

                    Rows returned

                    0

                    1

                    0

                    1

                    0

                    1

                    0

                    InAverageBps

                    24323

                    OutAverageBps

                    234234

                    DateTime

                    12:01PM

                    1

                    InAverageBps

                    35235

                    OutAverageBps

                    42423

                    DateTime

                    12:10PM

                    2

                    InAverageBps

                    2523

                    OutAverageBps

                    234234

                    DateTime

                    12:20PM

                    3

                    InAverageBps

                    2352

                    OutAverageBps

                    45334

                    DateTime

                    12:30PM

                    4

                    InAverageBps

                    2352

                    OutAverageBps

                    354

                    DateTime

                    12:01PM

                    5

                    InAverageBps

                    252352

                    OutAverageBps

                    345345

                    DateTime

                    12:01PM

                    6

                    InAverageBps

                    2525523

                    OutAverageBps

                    235

                    DateTime

                    12:01PM

                     

                     

                    you can just access the dictionary or assign one result from the dict to a variable or like i do loop through the dictionary and pull off lists with

                     

                    listname.append(data["results"][count].items()[2][1])

                    where count is your current pass through the loop

                     

                    if your query works and you have data (the variable I assigned the results of my query)

                     

                    you an do

                    print data and see the full structure result of your query (i believe)

                     

                    Edit:  since results is everything and there is only one result 0 I would carve results out so you have one less level of hierarchy to navigate (i haven't so far on my application of this) just a thought