Filter table rows, removing rows when a field is NULL or empty.

Greg Jewett
Kilo Contributor

‎11-19-2019 12:25 PM

I am trying to query the table "cmdb_ci_server", utilizing the table API call.  There are 10K results in the table, and the only filtering that I have working is the number of results to return.

I get results like this:

[
   {
      "install_status": "1",
      "u_billing_contact": "",
      "operational_status": "1",
      "cpu_name": "",
      "vendor": "",
      "asset_tag": "876921",
      "host_name": "",
      "serial_number": "NEC4BFBAAA",
      "short_description": "",
      "owned_by": "",
      "cpu_manufacturer": "",
      "ip_address": "",
      "manufacturer": "0d4e40ab370b6a003952de7543990e45"
   }
]

I want to initially filter (exclude) when "host_name" when the field is empty or null.  I am using Python with the Requests module.

I took to Google, API documentation, Product Documentation, Stack Overflow, etc.. with so many different thoughts on how one might go about this, nothing has worked:

  • "&sysparm_query=host_name!="  -- return a null set.
  • "&sysparm_query=host_nameISNOTEMPTY"
  • "host_name!=''"
  • "host_name<>''"
  • "host_name=NOT:''"

I have not been able to find the correct syntax for filtering when a field is empty or how to use a negate in my logic.  

cpURL = "https://ut.service-now.com/api/now/table/cmdb_ci_server?sysparm_exclude_reference_link=true"
cpURL += "&sysparm_query=host_name!="
cpURL += "&sysparm_fields=host_name%2Cu_billing_contact%2Cip_address%2Coperational_status%2Ccpu_manufacturer"
cpURL += "%2Ccpu_name%2Cowned_by%2Cshort_description%2Cmanufacturer%2Cvendor%2Cserial_number"
cpURL += "%2Casset_tag%2Cinstall_status&sysparm_limit=1&sysparm_query_no_domain=true"

I have already found and tried the following articles:

  • https://community.servicenow.com/community?id=community_question&sys_id=f68f3ae9db58dbc01dcaf3231f961943&view_source=searchResult
  • https://community.servicenow.com/community?id=community_question&sys_id=7377cf69db1cdbc01dcaf3231f9619b9
  • https://community.servicenow.com/community?id=community_question&sys_id=6017cbe5db1cdbc01dcaf3231f961991

..and many others. 

I can not figure out how to do this via the URL.  Is it even possible?   Is there another method?  Can I pass some parameters using the "POST" action but pass values via JSON object via Requests "params"?   HELP!

Here is my exact code

cpURL = "https://ut.service-now.com/api/now/table/cmdb_ci_server?sysparm_exclude_reference_link=true"
cpURL += "&sysparm_query=host_name!%3D''"
cpURL += "&sysparm_fields=host_name%2Cu_billing_contact%2Cip_address%2Coperational_status%2Ccpu_manufacturer"
cpURL += "%2Ccpu_name%2Cowned_by%2Cshort_description%2Cmanufacturer%2Cvendor%2Cserial_number"
cpURL += "%2Casset_tag%2Cinstall_status&sysparm_limit=1&sysparm_query_no_domain=true"

headers = {"accept":"application/json", "content-type":"application/json"}
apiResponse = requests.get(cpURL, headers=headers, auth=(apiUser, apiPassword))
apiData = json.loads(apiResponse.text)

if (apiData.get("result"), "null") == "null":
	print(apiData)
else:
	theAlerts = apiData["result"]
	print(json.dumps(theAlerts, indent=3))
0 Helpfuls
  • 3,105 Views
2 REPLIES 2

Giles Lewis
Giga Guru

‎11-19-2019 01:51 PM

It makes me think you have some corruption in your data. Suppose the host_name is not empty, but rather contains one or more spaces. You would probably not see this in your REST payload as the data is probably getting trimmed. 

Try running the following background script. It should print "0" because there is no printable character less than "!" except for a space. If it prints anything other than "0" then you have spaces (or other non-printable junk) in your host names.

var ci = new GlideRecord('cmdb_ci_server');
ci.addQuery('host_nameISNOTEMPTY');
ci.addQuery('host_name<!');
ci.query();
gs.print(ci.getRowCount());

 

0 Helpfuls

Greg Jewett
Kilo Contributor

‎11-19-2019 02:47 PM

@Giles Lewis It is not really corruption in my data.. but NOT knowing my data.  Since I was going at this blind and making assumptions, I assumed there would be data in a specific field, and that is when the maddening realization kicked in.. the query part was working just fine, and the result was perfectly on point.. no results.

I have since found ways to better look at the data, so I can query it better.  I am brand new to SNOW CMDB, REST API calls, and making beginning mistakes.

SO, lesson learned, and the following web page is very helpful!

https://docs.servicenow.com/bundle/newyork-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html#r_OpAvailableFiltersQueries

0 Helpfuls