swis query not returning results

Not familiar with "DATETRUNC" in SWIS.  I personally use ADDDATE without issue.

ex: ADDDATE('Minute', -30, GETUTCDATE())

Thanks for the reply

But even using that format

AND ModifiedTime >=ADDDATE('HOUR',-72,GETUTCDATE())

I get the same outcome, the query returns results when running it in swql studio but when running from python script results are blank

# requests is needed for the underlying web calls
import requests
# getpass is required for password input without displays
import getpass
# orion sdk is required for the client connectivity
from orionsdk import SwisClient

# main() is our main function to do the thing
def main():
    # these are the variables where we store your connection information
    hostName  = 'orion.demo.lab' # Put your server ip/hostname here

    change = '''
    SELECT Nodes.Displayname FROM Orion.Nodes as Nodes
    JOIN NCM.Nodes as n on Nodes.NodeID = n.CoreNodeID
    JOIN NCM.ConfigArchive as c on n.NodeID = c.NodeID
    -- I don't have any matching elements here, so I elected to just remove the filter
    --WHERE Nodes.Vendor LIKE '%uroam%'
    AND c.ConfigType = 'Running'
    AND ModifiedTime >= DATETRUNC('HOUR',ADDHOUR(-72,GETUTCDATE()))
    '''

    # Build a connection to the server
    print("Enter the username and password for '" + hostName + "'")
    username = input("Username: ")
    password = getpass.getpass(prompt='Password: ')
    swis = SwisClient(hostName, username, password)
    
    # let's run the query and store the results in a variable
    response = swis.query(change)
    
    # there are multiple responses, so we'll need to go through each entry
    print("DisplayName")
    for result in response['results']:
        # output the caption and uri
        print(result['Displayname'])

requests.packages.urllib3.disable_warnings()

if __name__ == '__main__':
    main()

The above is returning the following for my environment (SolarWinds Platform 2023.1)

Enter the username and password for 'orion.demo.lab'
Username: admin
Password: 
DisplayName
AP6-2nd
AP6-Ground
AP6-3rd
AP6-1st

Are you sure you are extracting the contents from the returned results properly?

Thanks for your input

I used your format and i get the same result

======= RESTART: C:/Users/xxxx/Desktop/Python tests/Test-main-query.py ======
DisplayName
>>>

original python script

##omitted connection to server##

So both queries run but i only see results from engineid, i dont see any results to extract from cfgchange

========= RESTART: C:\Users\xxxx\Desktop\Python tests\Test-query.py =========
{'results': [{'EngineID': 10}]}
10
{'results': []}
[]
>>>

If i hash out engineid() so only cfgchange runs its the same thing no results, yet running that exact query in swql studio returns the results i am after

What platform version are you running?

We are running  2020.2.6

Testing the query in python and it seems it is the NCM. bit that is breaking it

if i  only use Orion.Nodes it returns results

"""SELECT TOP 10 Nodes.Displayname FROM Orion.Nodes as Nodes"""

But using only any of the ncm tables it returns nothing

"""SELECT TOP 10 CoreNodeID FROM NCM.Nodes"""

or

"""SELECT TOP 10 NodeID  FROM NCM.ConfigArchive"""

but it works for you so maybe code version ??

I mean... 2020.2.6 is about three years old.  I'm not sure what would have changed in regard to the API, but that doesn't mean nothing has changed.  You might want to try and re-work your query using the Cirrus entities.  They are basically "aliases" for the NCM entities but using an older naming convention.  To be fair, they might return the same thing, but it's worth a test.

In my environment these two queries return the same results (in SWQL Studio):

-- Original Query from Above
-- BEGIN
SELECT Nodes.Displayname
FROM Orion.Nodes AS Nodes
JOIN NCM.Nodes AS n
      ON Nodes.NodeID = n.CoreNodeID
JOIN NCM.ConfigArchive AS c
      ON n.NodeID = c.NodeID
            -- I don't have any matching elements here, so I elected to just remove the filter
            --WHERE Nodes.Vendor LIKE '%uroam%'
            AND c.ConfigType = 'Running'
            AND ModifiedTime >= DATETRUNC('HOUR', ADDHOUR(- 72, GETUTCDATE()))
-- END


-- Modified to use the 'Cirrus' namespace/entities
-- BEGIN
SELECT [Nodes].DisplayName
FROM Orion.Nodes AS [Nodes]
JOIN Cirrus.Nodes AS [CN]
      ON [Nodes].NodeID = [CN].CoreNodeID
JOIN Cirrus.ConfigArchive AS [CA]
      ON [CN].NodeID = [CA].NodeID
            -- I don't have any matching elements here, so I elected to just remove the filter
            --WHERE Nodes.Vendor LIKE '%uroam%'
            AND [CA].ConfigType = 'Running'
            AND [CA].ModifiedTime >= DATETRUNC('HOUR', ADDHOUR(- 72, GETUTCDATE()))
-- END

What exactly are you trying to return in this query?  Include any filters you might think about using because that can only help things.

Hi KMSigma

Used the Cirrus entries and get the same result, running the query in SWQL returns the results I am after but running the query from python script on the Poller returns blank.

I am trying to get the devices that config has changed on so i can run the rest of the script to export the config backup file to a server, at the moment i am exporting it every night but not really efficient this way.

I am working on another way to pull this info from the device directly but the swql query would be the easier option.

Thanks

Python needs to point to the Primary Polling Engine because of the way that the authentication takes place.  If you point it to your Main Polling Engine, does it return results?

Python needs to point to the Primary Polling Engine because of the way that the authentication takes place.  If you point it to your Main Polling Engine, does it return results?