Casper MySql query for inventory tracking

[cat123]

Hello, I am building an integration to Casper with our IT Asset Management database.

I am connecting directly to the MySql database and will need to run queries to retrieve asset data.

Where can I find a schema map of the Casper database? I would like to see a map of the tables and attributes that are in the jamfsoftware database.

Has anyone used Casper in this manner or have written any MySQL queries to pull asset data?

Thanks,

[alexjdale]

Direct database queries are discouraged. New versions of Casper will eventually introduce changes to the database that will break your integration. The API is recommended instead.

[emily · Accepted solution]

As @alexjdale mentioned, you're much better off working with the API then with the database directly. What information are you trying to get? You can see the API documentation at https://jss.yourcompany.com:8443/api/.

[cat123]

Thank you. I am looking to set up an integration with ServiceNow to populate the CMDB. We need to query information on laptops and desktops that are managed by Casper, such as: Machine name, mac address, IP address, last connection time, serial number, warranty expiration and software installed.

Would you happen to know of any documentation that covers integrating with the ServiceNow platform?

Thank you again for your help!

[cat123]

Also I have another question. I will be setting up the Rest API connection via GET request with Basic Authentication. What permissions/roles does that user account need to have to initiate the connection and query the database to pull the results?

[cholder]

I am looking for the same thing and coincidently to integrate it into Service Now as well. It would be nice to be able to do it in the API if I understood how and hand the time. Currently we have our SCCM integrated with SQL and am just looking to do the same.

I have got the computers going in and am looking for applications as we don't use all the applications just ones we need to worry about licenses.

Any one know the table for applications?

All that would be required is a view from Jamf so that any future releases wouldn't break the code.

[mm2270]

Speaking from some experience in using the API, my suggestion would be to start by setting up a saved Advanced Computer search in your JSS, adding in any and all data columns of information you may want to pull into your other database, and then using the API to do a GET on that saved search. All the columns you save under the display items for the search will appear in the resulting xml or json file for every computer. While you can add some criteria for filtering the Macs, if you just want data on all Macs in your JSS, simply leave the criteria section blank. This is equivalent to just clicking the Search button in the Search Inventory section - it pulls a full list of all Macs (or iOS devices) in your JSS.
From there, the xml/json file should be able to be sucked up into another database, but you'd need to work out the details on how to do that part, unless someone chimes in with specific experience on that.

And lastly, I 3rd using the API over direct MySQL queries. If you choose to use direct queries and something goes wrong, you may be on your own, or need to call in to JAMF to get it fixed. I just don't recommend it personally.

[cat123]

Thank you mm2270. that is exactly what I ended up doing. I created an inventory report. Then I can connect to that via the REST API and I can pull the computer records serial number, mac address, etc.

Do you know a good way to pull in Software information via this report technique? In other words, I would like a list of all software that is installed on each computer.

Thank you!

[mm2270]

Hi @cat123. I'm glad the suggestion was helpful, and I would love to offer a suggestion for your last question too. Unfortunately, I don't know a good way to pull all application data. Since you can't add in installed applications as a column to display in the saved advanced search, you can't get it this way. Each individual Mac record will have a full list of installed apps, just as you would see directly in the JSS when you click on a Mac's detailed view, but getting it in one shot via the API is kind of impossible.
The only way would be to gather a list of all Computer IDs (simple enough), then use a loop process to do a separate API GET request for each ID and grab the Application section from it. If you have even a modest amount of Macs, this can take a very long time to complete, not to mention it could put a load on your server hosting the JSS.
Believe me, I wish I knew a good way to do that as well. I'd be using it myself to generate reports from the API, but its not possible.

[merps]

I was messing around with this a few months ago. I have to admit that it's only a partial work, but it grabs the installed applications present as of the last inventory for all systems and writes them out to a CSV.

I haven't gone as far as parsing the individual entries to do anything useful, but it's a start. If I remember correctly it's only relying on the "requests" module, but like I said, it's been a few months since I gave it any attention. It took less than a minute to run on our ~200 machines.

Here's my python script:

# Import these resources into your script
import requests
import csv
import xml.etree.ElementTree as ET

usernameVar='apiuser'
passwordVar='password'
jss_url='https://jss.domain.com:8443'

c = csv.writer(open("Casper_JSSApplicationInfo.csv", "wb"))

def get_installed_software(id_num):
    software_xml = requests.get(jss_url + '/JSSResource/computers/id/' + id_num + '/subset/Software', auth=(usernameVar, passwordVar))

    root = ET.fromstring(software_xml.text)

    print "Installed Applications"
    installed_applications = []
    count = 0
    for application in root.iter('application'):
        name = application.find('name').text
        path = application.find('path').text
        version = application.find('version').text
        installed_applications.append((name, path, version))
#        print(name, path, version)     ## Testing to make sure you're grabbing what you wanted
        count += 1
    print count
    return installed_applications

def get_computers():
    computers_xml = requests.get(jss_url + '/JSSResource/computers', auth=(usernameVar, passwordVar))

    root = ET.fromstring(computers_xml.text)

#    tree = ET.parse('computers.xml')  ## This will read in from a file that lives in the same directory
#    root = tree.getroot()             ## This sets root for when you're reading in from a file

    print "All Computers"
    all_computers = []
    count = 0
    for computer in root.iter('computer'):
        jss_id = computer.find('id').text
        name = computer.find('name').text
        all_computers.append((jss_id, name))
        count += 1
    print count
    return all_computers


def main():
    jss_machines = get_computers()
    print jss_machines

    for i, (a, b) in enumerate(jss_machines):

        installed_apps = get_installed_software(a)
        c.writerow([a, b, installed_apps])

if __name__ == '__main__':
    main()

Maybe this will be a starting point for someone with more time and python skills...