How to do a query with pagination, how do you know what the highest row number is

arsenius

Hello Everybody.

Im building a query to collect info from all the nodes in Orion.Nodes.

I think pagination would be in order to decrease the chance the reply will timeout.

I found that you can do pagination like this with SWQL:

SELECT NodeID, Status, Caption
FROM Orion.Nodes
ORDER BY NodeID
WITH ROWS 1 TO 1000

But how do you find out what the highest NodeID is that you need to query to.

arsenius

i got a orion database with 30k nodes. and i want to know all the nodes that are in there using the pythonsdk.
my thoiught was if i query all the nodes in one go i might get a timeout at somepoint, so i should do it in multiple batches.

i have come up with the following that seems to work:

def lookup_nodes_on_orion(swis):
    logger = logging.getLogger("my_logger")
    highest_row = 0
    nodes_per_query = 500
    page = 1
    completed_querys = False
    orion_nodes = []
    try:
        highest_row_query = 'SELECT COUNT(NodeID) AS NodeIDMax FROM Orion.Nodes' 
        highest_row_result = swis.query(highest_row_query)
        logger.debug("highest_row_result = %s",pformat(highest_row_result,indent=1,width=80))
        highest_row = highest_row_result['results'][0]['NodeIDMax']
    except Exception as e:
        logger.error(f'error while sending highest_row_query:\r\n{e}')
    if highest_row == 0:
        logger.error(f'highest node ID could not be retrieved from orion, highest_row is {highest_row}, exiting\r\n')
        sys.exit()
    else:
        while not completed_querys:
            querystring = (
            f"SELECT NodeID, DisplayName, IPAddress, I.CustomProperties.City, I.CustomProperties.Comments, "
            f"I.CustomProperties.SLA, I.CustomProperties.Uri AS customproperties_uri, "
            f"Description, SysName, Location, "
            f"EngineID, IP_Address, I.WorldMapPoint.Latitude, I.WorldMapPoint.Longitude, I.WorldMapPoint.Uri AS worldmap_uri "
            f"FROM Orion.Nodes I ORDER BY NodeID WITH ROWS {page} TO {page+nodes_per_query-1}"
            )
            logger.info(f"{page}/{highest_row} running swis query {querystring}")
            results = swis.query(querystring)
            # logger.debug("results = \n%s",pformat(results,indent=1,width=80))
            page += nodes_per_query
            if page >= highest_row:
                completed_querys = True
            for orion_node in results['results']:
                orion_nodes.append(orion_node)
    logger.debug("orion_nodes = \n%s",pformat(orion_nodes,indent=1,width=80))