Technical overview > API > Use case: Windows SQL server list with REST API

In this use-case, we learn how to use the Windows service inventory to filter the list of devices running the Microsoft SQL Server software. This powerful data management software is used by a lot of Microsoft products as stable persistent data repository, even remotely by other network devices. Keeping good watch over devices having it installed is key to keeping your IT landscape healthy. Learn the basic approach by reading the following program.

Requirements

•Python 3+

•Requests library (https://docs.python-requests.org/en/latest/)

•API key

•frontend URL

Program content

import requests

APIKEY = "PUT API KEY HERE"
FRONTEND = "PUT FRONTEND URL HERE"

HEADERS = { "ApiKey": APIKEY }

def obtain_paged(EP):
    P = {}
    P["count"] = 1000

    items = []

    while True:
        result = requests.get(EP, params=P, headers=HEADERS)

        if result.ok == False:
            break

        objs = result.json()

        if objs == None:
            return

        if len(objs) == 0:
            return

        items.extend(objs)

        lastobj = objs[len(objs)-1]

        P["LastId"] = lastobj["id"]

    return items

def main():
    devs = obtain_paged(FRONTEND + "/api/v1/Devices")
    sf = obtain_paged(FRONTEND + "/api/v1/WindowsServices")

    if devs == None or sf == None:
        print( "invalid request data received from frontend (" + FRONTEND + ")" )
        return

    print( "Windows MSSQLSERVER list" )
    print( "=========================" )

    any_sql = False

    for D in devs:
        has_sql = False

        for SF in sf:
            if "deviceId" in SF and D["id"] == SF["deviceId"]:
                if "name" in SF and SF["name"] == "MSSQLSERVER":
                    has_sql = True
                    break

        if has_sql:
            if "name" in D:
                print("* " + D["name"])
            else:
                print("- " + D["id"])

            any_sql = True

    if any_sql == False:
        print( "? none" )