Using JSS API to POST new departments

blinvisible
Contributor

At the university, our list of departments is in routine flux -- departments get created, merged with other departments, renamed, etc. When I initially set up our JSS years ago, I did so with a dump of 280 existing LDAP departments at the time. Today there are 326 departments, and needless to say, the missing data is creating some gaps in information within the computer records. I need to update the list of departments in the JSS.

Sadly, there is no current mechanism in the JSS to get those updates directly from LDAP, so I'm attempting to script updates via the JSS API. I'm having no difficulty retrieving existing information via GET, but I've yet to have any success creating new records with POST. Each attempt is successful from an HTTP point of view (return code of 201), but the JSS itself doesn't seem to create anything new, returning only:

<?xml version="1.0" encoding="UTF-8"?><department><id>-1</id></department>

In each case, I'm using the URI '/departments/id/0' which should create a new department with the next available ID number. As I understand it, a successful POST via ID number should return the id number created. I don't know where '<id>-1</id>' is coming from, as that certainly isn't a valid ID number. I'm guessing it means the JSS rejected the information for some reason? Any ideas?

I've attempted in both bash and python with the same results. Below are some [sanitized] code examples. I'd appreciate any thoughts on why '-1' keeps coming back as my returned ID value.

Bash (well, sh, but same idea):

#!/bin/sh
user="username"
pass="password"

# department XML data to post
POSTxml="<department><name>General University</name></department>"

# submit POST to JSS and write out HTTP return code
curl https://jssurl.org/JSSResource/departments/id/0 --user "$user:$pass" -H "Content-Type: text/xml" -X POST -d "${POSTxml}" --write-out \
%{http_code} --output -

exit 0

Python:

import urllib2

# create a password manager and authenticated URL opener
password_mgr = urllib2.HTTPPasswordMgrWithDefaultRealm()

# Add the base URL to auth against, and the username and password.
top_url = 'https://jssurl.org/JSSResource'
password_mgr.add_password(None, top_url, "username", "password")

# create handler
pwhandler = urllib2.HTTPBasicAuthHandler(password_mgr)

# create "opener" (OpenerDirector instance)
opener = urllib2.build_opener(pwhandler)

# department XML data to post
POST_xml = '<department><name>General University</name></department>'

# HTTP request
POST_request = urllib2.Request(top_url + '/departments/id/0',POST_xml)
POST_request.add_header('Content-Type','text/xml')
POST_request.get_method = lambda: 'POST'
response = opener.open(POST_request)
print response.read()
18 REPLIES 18

BrysonTyrrell
Contributor II

Are you able to view the department in the JSS, or retrieve it via the API by name lookup, after creating it?

blinvisible
Contributor

No, it doesn't get created in the JSS at all (despite HTTP POST returning success). I'm guessing the returned ID of -1 means the creation failed for some reason, but I'm not able to figure out why.

BrysonTyrrell
Contributor II

Are you JAMF Cloud or on-prem? What's the JSS version? If on-prem what version of MySQL and Java are you using?

blinvisible
Contributor

On-prem (RHEL 6.7) host
JSS version 9.9
mysql version 5.1.73 Source distribution
java version "1.7.0_99"

millersc
Valued Contributor

Might be time to upgrade. This is the 9.9 suggested specs. Do you have a dev environment to test with? The MySQL might be missing something that 9.9 wants.
http://docs.jamfsoftware.com/9.9/casper-suite/release-notes/Installation.html

JSS Installer for Linux

To use the JSS Installer for Linux, you need a server with:

A 64-bit capable Intel processor
2 GB of RAM
400 MB of disk space available
One of the following operating systems:
Ubuntu 12.04 LTS Server (64-bit)
Ubuntu 14.04 LTS Server (64-bit)
Red Hat Enterprise Linux (RHEL) 6.4, 6.5, 6.6, or 7.0
Open Java Development Kit (OpenJDK) 7 or 8
For installation information, go to http://openjdk.java.net/install/.
MySQL Enterprise Edition 5.5.x or 5.6.x (recommended), or MySQL Community Server 5.5.x or 5.6.x, available at:https://www.mysql.com/downloads/
Ports 8443 and 8080 available

blinvisible
Contributor

I upgraded mysql to version 5.6.29-76.2, but sadly this did not change the behavior I'm seeing.

cvgs
Contributor II

Hi, your shell code works for me (on Windows Server and JSS 9.9) and is able to successfully create a new department.

I guess you need to check the JSS privileges of $user, they might be insufficient. Sadly there is no documentation about the specific rights needed, so you might have to experiment a bit (see also https://jamfnation.jamfsoftware.com/discussion.html?id=17846).

Christoph

blinvisible
Contributor

Thanks for the suggestion @cvgs, but even with granting $user full admin rights I'm getting the same return. I started looking in the JAMFSoftwareServer.log file and found this:

2016-04-08 14:08:16,182 [ERROR] [Tomcat-217 ] [DepartmentHelper         ] - com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value for column 'department_id' at row 1

...but if I'm specifying a department id of "0" (which is supposed to find the next available id to use) I'm not sure why it says "out of range value." Doubly strange if the same code works fine on your end.

Does the JSS have a limit on the number of departments you can have? Looks like the highest ID number in my departments table is 299 -- is the JSS unable to go higher than that?

mysql> select * from departments;
+---------------+-------------------------------------------+
| department_id | department_name                           |
+---------------+-------------------------------------------+
|            21 | Admitted                                  |
|            20 | ALANA Student Center                      |
|            22 | Anesthesiology                            |
|            23 | Animal Care Management                    |
|            24 | Anthropology                              |
|            25 | Athletics                                 |
|            26 | Biochemistry                              |
|            27 | Biology                                   |
|            28 | CESS Student Services                     |
|            29 | CIT Client Services                       |
|            30 | Career Center                             |
|            31 | Certificate Program                       |
|            32 | Chemistry                                 |
|            33 | Classics                                  |
|            34 | Communication Sciences                    |
|            35 | Computer Operations                       |
|            36 | Computer Science                          |
|            37 | Department of Psychiatry                  |
|            38 | Economics                                 |
|            39 | Emeriti Faculty                           |
|            40 | English                                   |
|            41 | Environmental Program                     |
|            42 | Faculty Senate                            |
|            43 | Family Medicine                           |
|            44 | First Year                                |
|            45 | Fleming Museum                            |
|            46 | Fraternity                                |
|            47 | Geography                                 |
|            48 | Geology                                   |
|            49 | Graduate                                  |
|            50 | History                                   |
|            51 | Honors College                            |
|            52 | Human Resources                           |
|            53 | Junior                                    |
|            54 | Medical Biostatistics                     |
|            55 | Medical Photography                       |
|            56 | Medicine                                  |
|            57 | Military Studies                          |
|            58 | Neurology                                 |
|            59 | Nursing                                   |
|            60 | Pathology                                 |
|            61 | Pediatrics                                |
|            62 | Pharmacology                              |
|            63 | Physics                                   |
|            64 | Police Services                           |
|            65 | Political Science                         |
|            66 | Psychiatry                                |
|            67 | Psychology                                |
|            68 | Radiation Safety                          |
|            69 | Radiology                                 |
|            70 | Religion                                  |
|            71 | Residential Life                          |
|            72 | Second Yr Medical Student                 |
|            73 | Senior                                    |
|            74 | Social Work                               |
|            75 | Sociology                                 |
|            76 | Sophomore                                 |
|            77 | Staff Council                             |
|            78 | Student Life                              |
|            79 | Surgery                                   |
|            80 | Theatre                                   |
|            81 | Third Yr Medical Student                  |
|            82 | UVM Board of Trustees                     |
|            83 | UVM Bookstore                             |
|            84 | University Communications                 |
|            85 | Vermont Cancer Center                     |
|            86 | Women's Center                            |
|            87 | Continuing Education Student              |
|            88 | Procurement Services                      |
|            89 | Animal Science                            |
|            90 | Audit Services                            |
|            91 | Bailey Howe Library                       |
|            92 | CALS Dean's Office                        |
|            93 | CEM Dean's Ofc                            |
|            94 | CESS Dean's Office                        |
|            95 | CNHS Dean's Office                        |
|            96 | COM Admissions                            |
|            97 | COM Finance and HR                        |
|            98 | COM GCRC                                  |
|            99 | COM General                               |
|           100 | COM Information Systems                   |
|           101 | COM Ofc of Clin Trials Rsch               |
|           102 | COM Ofc of Med Ed                         |
|           103 | COM Ofc of Primary Care                   |
|           104 | COM Office of the Dean                    |
|           105 | COM Operations                            |
|           106 | COM Ptnt Oriented Rsch                    |
|           107 | COM Public Relations                      |
|           108 | Campus Planning                           |
|           109 | CatCard Service Center                    |
|           110 | Continuing Med Education                  |
|           111 | Dana Medical Library                      |
|           112 | Dean of Students Ofc                      |
|           113 | Education                                 |
|           114 | Graduate College                          |
|           115 | Gund Institute                            |
|           116 | International Educational Svcs            |
|           117 | Learning and Info Tech                    |
|           118 | Libraries - Deans Ofc                     |
|           119 | Med-Cardiology                            |
|           120 | Med-Dept Admin                            |
|           121 | Med-Dermatology                           |
|           122 | Med-Endocrinology                         |
|           123 | Med-Gastroenterology                      |
|           124 | Med-Gen Internal Med                      |
|           125 | Med-General                               |
|           126 | Med-Geriontology Geriatrics               |
|           127 | Med-Hematology Oncology                   |
|           128 | Med-Immunobiology                         |
|           129 | Med-Infectious Disease                    |
|           130 | Med-Nephrology                            |
|           131 | Med-Pulmonary                             |
|           132 | Med-Rheumatology                          |
|           133 | Med-Vascular Biology                      |
|           134 | ObGyn-General                             |
|           135 | ObGyn-Gynecologics Oncology               |
|           136 | ObGyn-Maternal Fetal                      |
|           137 | Ofc of Health Promo Research              |
|           138 | Pathology - Anatomic                      |
|           139 | Pathology - Clinical                      |
|           140 | Pathology - General                       |
|           141 | Peds-Endocrinology                        |
|           142 | Peds-Gastroenterology                     |
|           143 | Peds-General                              |
|           144 | Peds-Genetics                             |
|           145 | Peds-Hematology Oncology                  |
|           146 | Peds-Infectious Disease                   |
|           147 | Peds-Neonatology                          |
|           148 | Peds-Nephrology                           |
|           149 | Peds-Pulmonary                            |
|           150 | Presidents Ofc                            |
|           151 | RSENR Dean's Ofc                          |
|           152 | Registrar                                 |
|           153 | Sch of Business Administration            |
|           154 | Student Financial Svcs                    |
|           155 | Surg-Emergency Med                        |
|           156 | Surg-General                              |
|           157 | Surg-Neurosurgery                         |
|           158 | Surg-Opthamology                          |
|           159 | Surg-Otolaryngology                       |
|           160 | Surg-Pediatric                            |
|           161 | Surg-Plastic                              |
|           162 | Surg-Thoracic Cardiovascular              |
|           163 | Surg-Transplant                           |
|           164 | Surg-Urology                              |
|           165 | Surg-Vascular                             |
|           166 | Treasury and Tax Svcs                     |
|           167 | Ext - Sustainable Agricltr Ctr            |
|           168 | COM Educational Tools                     |
|           169 | Continuing Ed - Administration            |
|           170 | Continuing Ed - Lane Series               |
|           171 | Continuing Ed - Operations                |
|           172 | Bailey Howe-Collectn Mgmt Svcs            |
|           173 | Bailey Howe-Research Collectns            |
|           174 | Davis Center                              |
|           175 | Ext - State Ofc Staff                     |
|           176 | College Computing Svcs                    |
|           177 | Business Proc Re-Engnrg Team              |
|           178 | Ext - EFNEP                               |
|           179 | Ext - Statewide 4-H                       |
|           180 | Language Resource Center                  |
|           181 | Plant Biology                             |
|           182 | School of Engineering                     |
|           183 | Academic Success Prg                      |
|           184 | Student Govt Association                  |
|           185 | Ext - SARE                                |
|           186 | CNHS Student Services                     |
|           187 | Consulting Archaeology Program            |
|           188 | Philosophy                                |
|           189 | Custodial Services                        |
|           190 | Computer Depot                            |
|           191 | ETS Client Services                       |
|           192 | VP Research Admin Office                  |
|           193 | VP of Enrollment Mgmt                     |
|           194 | Residential Learning Cmty                 |
|           195 | EPSCoR                                    |
|           196 | Center for Rural Studies                  |
|           197 | COM Med AV                                |
|           198 | VT Advanced Computing Ctr                 |
|           199 | Transportation Research Ctr               |
|           200 | Cost Accounting Services                  |
|           201 | Database Administration                   |
|           202 | Enterprise Application Service            |
|           203 | Retired Faculty                           |
|           204 | Retired Staff                             |
|           205 | Vermont Genetics                          |
|           206 | CEM Computer Facility                     |
|           207 | CEM Student Services                      |
|           208 | Controllers Office                        |
|           209 | COM Executive Office                      |
|           210 | Physical Plant Dept                       |
|           211 | Writing in the Disciplines                |
|           212 | HRS Learning Services                     |
|           213 | CUPS                                      |
|           214 | Technology Commercialization              |
|           215 | Ofc of Institutional Research             |
|           216 | Center on Aging                           |
|           217 | LGBTQA Center                             |
|           218 | Admin Business Service Ctr                |
|           219 | Off Campus Agencies                       |
|           220 | Office of Sustainability                  |
|           221 | Chief Diversity Office                    |
|           222 | Continuing Ed - Technology                |
|           223 | Radiation-Oncology                        |
|           224 | Cultural Pluralism                        |
|           225 | Information Security Office               |
|           226 | Leadership and Development Sci            |
|           227 | Risk Management and Safety                |
|           228 | Research Protections Office               |
|           229 | Sponsored Project Admin                   |
|           230 | Peds-Cardiology                           |
|           231 | Payroll Svcs                              |
|           232 | Senior - 2 Year                           |
|           233 | UVM Foundation                            |
|           234 | Mail Services                             |
|           235 | ETS Information Security                  |
|           236 | Distance Education                        |
|           237 | First Yr Medical Student                  |
|           238 | Surg-Oncology                             |
|           239 | Surg-Trauma                               |
|           240 | Miller Ctr for Holocaust Stdy             |
|           241 | University Relations                      |
|           242 | Compliance                                |
|           243 | Integrated Biology                        |
|           244 | Fourth Yr Medical Student                 |
|           245 | Neurological Sciences                     |
|           246 | Global Gateway                            |
|           247 | *Counseling&Testing Serv*                 |
|           248 | Computer Science & Electrical Engineering |
|           249 | German & Russian                          |
|           250 | Mathematics & Statistics                  |
|           251 | Nutrition & Food Sciences                 |
|           252 | Plant & Soil Science                      |
|           253 | Print & Mail Center                       |
|           254 | Women's & Gender Studies                  |
|           255 | A&S Dean' s Ofc                           |
|           256 | Admin & Facil Services                    |
|           257 | Admissions & Enrollment Mgmnt             |
|           258 | Bailey Howe-Access & Tech Svcs            |
|           259 | COM Microbio & Molec Genetics             |
|           260 | Com Dev & Applied Economics               |
|           261 | Conference & Event Services               |
|           262 | Ctr for Health & Well Being               |
|           263 | Ctr for Stdnt Ethics & Stnrds             |
|           264 | Ctr for Teaching & Learning               |
|           265 | Fin Rptng & Acct Svcs                     |
|           266 | Financial Analysis & Budgeting            |
|           267 | Living & Learning Center                  |
|           268 | Medical Lab & Radiation Sci               |
|           269 | Molecular Physlgy & Biophysics            |
|           270 | Orthopaedics & Rehabilitation             |
|           271 | Plant & Animal Biology Fclty              |
|           272 | Senior VP & Provost                       |
|           273 | Student & Community Relations             |
|           274 | VP Legal Affrs & Gen Counsel              |
|           275 | Rehab & Movement Sci                      |
|           276 | Bailey Howe-Info & Instruction            |
|           277 | Ext - Operations & Staff Sup              |
|           278 | Ext - Programming & Fac Sup               |
|           279 | Ctr on Disability & Community             |
|           280 | Facilities Design & Constrctn             |
|           281 | Capital Planning & Mgmt                   |
|           282 | Systems Architecture & Admin              |
|           283 | Telecomm & Network Services               |
|           284 | Asian Languages & Literatures             |
|           285 | VP Finance & Enterprise Svcs              |
|           286 | Transportation & Parking Admn             |
|           287 | Global & Regional Studies                 |
|           288 | ObGyn-Reprod Endocrn&Infertil             |
|           289 | Obstetrics Gynecology&Reprod              |
|           290 | Ctr Clinical & Translatnl Sci             |
|           291 | Continuing Ed - Prog&Enrol Mgt            |
|           292 | Art & Art History                         |
|           293 | Music & Dance                             |
|           294 | Romance Languages&Linguistics             |
|           295 | VP Univ Reltns & Campus Life              |
|           296 | Rubenstein Sch Env & Nat Res              |
|           297 | IMF/TSP                                   |
|           298 | Affirm Action / Equal Opp                 |
|           299 | Anatomy/Neurobiology                      |
+---------------+-------------------------------------------+
280 rows in set (0.00 sec)

BrysonTyrrell
Contributor II

@blimvisible

Interesting. I don't have that setup, but I'm going to try blasting a test instance of mine with more than 300 departments. Be back in a few.

guidotti
Contributor II

This is close to something I've wanted to do for some time (even though our LDAP [eDirectory central identity] is a mess). I need to put our cost centers into our inventory lookup tables. So I will give this a shot in my DEV JSS as well.

BrysonTyrrell
Contributor II

I was able to generate 500 without issue on a 9.9 instance. I would recommend reaching our to your TAM.

blinvisible
Contributor

Thanks for testing, I'll start a support case.

blinvisible
Contributor

Haven't heard back from the TAM yet, but as it turns out, this isn't a problem specific to the API; attempting to add a department via the JSS GUI is having the same problem. Clicking "Save" after entering a new department name returns a 404 "The page you requested could not be found" error at the URL:

https://[the.jss.url]/departments.html?id=-1&o=r

Again, that -1 department ID gets returned, and no new department gets created.

blinvisible
Contributor

After spending two hours on the phone with JAMF Support, scouring log files and trying various mysql repair techniques and Tomcat rebuilds, we ultimately ended up just dropping the whole 'departments' table in mysql and rebuilding it from scratch. To do so I used an enhanced version of the above Python script, which iterated through a list of all our departments and added them each to the JSS via the API.

I think this has the unfortunate side effect of now having to redo any Smart Groups or Advanced Searches that use department names (my understanding is they reference the department ID numbers, which have now all changed), but at least I'm able to add new ones again.

cdenesha
Valued Contributor III

wow. I wonder if it was because the first row has an ID of 21, then it dropped to 20, and then to 22. Maybe deleting just the ID of 21 would have worked. Or should I not say that now?

blinvisible
Contributor

We actually did try doing that before blowing up the whole thing, but it didn't solve the "-1" return problem. We also tried removing department names containing unusual characters like asterisks (don't ask me why we have department names with asterisks, I don't know) and ampersands but that didn't help either. But 'drop table departments;' sure did!

<insert favorite "kablooie!" noise>

mm2270
Legendary Contributor III
Or should I not say that now?

Probably shouldn't say that now, but... ouch?

cdenesha
Valued Contributor III

@blimvisible

If the SGs and queries do indeed use the ID, then how about this for a solution instead of rebuilding them all?

You have a report with all the old IDs. Delete the table again. Create 19 fake departments and delete them, since your old ones start with an ID of 20. Then create them one by one in the same order as they were before, so they'll get the same IDs. May not be able to do it automatically. Possibly you could use a text editor and quickly create an INSERT query for each department and add them a little quicker with SQL.