def test_query_cond_not_equals(self): # Make sure type checking works q1 = pysnow.QueryBuilder().field('test') self.assertRaises(pysnow.QueryTypeError, q1.not_equals, dt) # Make sure a valid operation works q2 = pysnow.QueryBuilder().field('test').not_equals('test') self.assertEquals(str(q2), 'test!=test')
def test_query_cond_starts_with(self): # Make sure type checking works q1 = pysnow.QueryBuilder().field("test") self.assertRaises(QueryTypeError, q1.starts_with, 1) # Make sure a valid operation works q2 = pysnow.QueryBuilder().field("test").starts_with("val") self.assertEqual(str(q2), "testSTARTSWITHval")
def test_query_cond_not_contains(self): # Make sure type checking works q1 = pysnow.QueryBuilder().field("test") self.assertRaises(QueryTypeError, q1.not_contains, 1) # Make sure a valid operation works q2 = pysnow.QueryBuilder().field("test").not_contains("val") self.assertEqual(str(q2), "testNOT LIKEval")
def test_query_cond_contains(self): # Make sure type checking works q1 = pysnow.QueryBuilder().field('test') self.assertRaises(pysnow.QueryTypeError, q1.contains, 1) # Make sure a valid operation works q2 = pysnow.QueryBuilder().field('test').contains('val') self.assertEqual(str(q2), 'testLIKEval')
def test_query_cond_ends_with(self): # Make sure type checking works q1 = pysnow.QueryBuilder().field('test') self.assertRaises(pysnow.QueryTypeError, q1.ends_with, 1) # Make sure a valid operation works q2 = pysnow.QueryBuilder().field('test').ends_with('val') self.assertEqual(str(q2), 'testENDSWITHval')
def test_query_cond_less_than(self): # Make sure type checking works q1 = pysnow.QueryBuilder().field('test') self.assertRaises(pysnow.QueryTypeError, q1.less_than, 'a') # Make sure a valid operation works q2 = pysnow.QueryBuilder().field('test').less_than(1) self.assertEqual(str(q2), 'test<1') q3 = pysnow.QueryBuilder().field('test').less_than(dt(2016, 2, 1)) self.assertEqual(str(q3), 'test<2016-02-01 00:00:00')
def test_query_cond_ends_with(self): # Make sure type checking works try: q = pysnow.QueryBuilder().field('test').ends_with(1) self.assertFalse(q) except pysnow.QueryTypeError: pass # Make sure a valid operation works q = pysnow.QueryBuilder().field('test').ends_with('val') self.assertEquals(str(q), 'testENDSWITHval')
def test_query_cond_not_equals(self): # Make sure type checking works try: q = pysnow.QueryBuilder().field('test').not_equals(dt) self.assertFalse(q) except pysnow.QueryTypeError: pass # Make sure a valid operation works q = pysnow.QueryBuilder().field('test').not_equals('test') self.assertEquals(str(q), 'test!=test')
def test_query_cond_not_equals(self): # Make sure type checking works q1 = pysnow.QueryBuilder().field('test') self.assertRaises(pysnow.QueryTypeError, q1.not_equals, dt) # Make sure a valid operation works (str) q2 = pysnow.QueryBuilder().field('test').not_equals('test') self.assertEqual(str(q2), 'test!=test') # Make sure a valid operation works (list) q3 = pysnow.QueryBuilder().field('test').not_equals(['foo', 'bar']) self.assertEqual(str(q3), 'testNOT INfoo,bar')
def test_query_cond_not_equals(self): # Make sure type checking works q1 = pysnow.QueryBuilder().field("test") self.assertRaises(QueryTypeError, q1.not_equals, dt) # Make sure a valid operation works (str) q2 = pysnow.QueryBuilder().field("test").not_equals("test") self.assertEqual(str(q2), "test!=test") # Make sure a valid operation works (list) q3 = pysnow.QueryBuilder().field("test").not_equals(["foo", "bar"]) self.assertEqual(str(q3), "testNOT INfoo,bar")
def test_query_cond_less_than(self): # Make sure type checking works try: q = pysnow.QueryBuilder().field('test').less_than('a') self.assertFalse(q) except pysnow.QueryTypeError: pass # Make sure a valid operation works q = pysnow.QueryBuilder().field('test').less_than(1) self.assertEquals(str(q), 'test<1') q = pysnow.QueryBuilder().field('test').less_than(dt(2016, 2, 1)) self.assertEquals(str(q), 'test<2016-02-01 00:00:00')
def test_query_multiple_expressions(self): try: q = str(pysnow.QueryBuilder().field('test').equals('test').between( 1, 2)) self.assertFalse(q) except pysnow.QueryMultipleExpressions: pass
def getAssignedIncidentCount(self): global snow_instance, snow_username, snow_password, snow_team, debug, display_name if debug: print(" def getAssignedIncidentCount(self):") c = pysnow.client.Client(instance=snow_instance, user=snow_username, password=snow_password) qb = ( pysnow.QueryBuilder().field('assigned_to.name').equals( display_name).AND().field('active').equals('true').AND().field( 'state').equals('2') # work in progress ) incident = c.resource(api_path='/table/incident') response = incident.get(query=qb) if debug: for record in response.all(): print(record['number']) count = len(response.all()) if debug: print(" " + str(count)) return count
def get_ctasks(): qb1 = (pysnow.QueryBuilder().field('assignment_group').equals( group_id).AND().field('sys_created_on').between( fourteen_days_ago, today)) response = c_task_request.get(query=qb1, stream=True) return response
def test_query_cond_between(self): # Make sure between with str arguments fails q1 = pysnow.QueryBuilder().field('test') self.assertRaises(QueryTypeError, q1.between, 'test', 'test') # Make sure between with int arguments works q2 = str(pysnow.QueryBuilder().field('test').between(1, 2)) self.assertEqual(str(q2), 'testBETWEEN1@2') start = dt(1970, 1, 1) end = dt(1970, 1, 2) # Make sure between with dates works q2 = str(pysnow.QueryBuilder().field('test').between(start, end)) self.assertEqual(str(q2), 'testBETWEENjavascript:gs.dateGenerate("1970-01-01 00:00:00")' '@javascript:gs.dateGenerate("1970-01-02 00:00:00")')
def _reset_query(self): """ Used to reset the current query object in case multiple queries are required during a single test case. Intended to be used after a query is executed. """ self.query = pysnow.QueryBuilder() self.desired_response_fields = list()
def test_query_cond_less_than(self): # Make sure type checking works q1 = pysnow.QueryBuilder().field('test') self.assertRaises(QueryTypeError, q1.less_than, 'a') # Make sure a valid operation works q2 = pysnow.QueryBuilder().field('test').less_than(1) self.assertEqual(str(q2), 'test<1') # Make sure naive dates are assumed as UTC q3 = pysnow.QueryBuilder().field('test').less_than(dt(2016, 2, 1)) self.assertEqual(str(q3), 'test<2016-02-01 00:00:00') # Make sure tz-aware dates are converted to UTC (UTC+1) q3 = pysnow.QueryBuilder().field('test').less_than(dt(2016, 2, 1, 3, tzinfo=pytz.FixedOffset(60))) self.assertEqual(str(q3), 'test<2016-02-01 02:00:00')
def test_query_cond_greater_than(self): # Make sure type checking works q1 = pysnow.QueryBuilder().field("test") self.assertRaises(QueryTypeError, q1.greater_than, "a") # Make sure a valid operation works q2 = pysnow.QueryBuilder().field("test").greater_than(1) self.assertEqual(str(q2), "test>1") # Make sure naive dates are assumed as UTC q3 = pysnow.QueryBuilder().field("test").greater_than(dt(2016, 2, 1)) self.assertEqual(str(q3), "test>2016-02-01 00:00:00") # Make sure tz-aware dates are converted to UTC (UTC+1) q4 = (pysnow.QueryBuilder().field("test").greater_than( dt(2016, 2, 1, 3, tzinfo=pytz.FixedOffset(60)))) self.assertEqual(str(q4), "test>2016-02-01 02:00:00")
def test_complex_query(self): start = dt(2016, 2, 1) end = dt(2016, 2, 10) q = (pysnow.QueryBuilder().field("f1").equals("val1").AND().field( "f2").between(start, end).NQ().field("f3").equals("val3")) self.assertEqual( str(q), 'f1=val1^f2BETWEENjavascript:gs.dateGenerate("2016-02-01 00:00:00")' '@javascript:gs.dateGenerate("2016-02-10 00:00:00")^NQf3=val3', )
def test_complex_query(self): start = dt(2016, 2, 1) end = dt(2016, 2, 10) q = pysnow.QueryBuilder()\ .field('f1').equals('val1')\ .AND()\ .field('f2').between(start, end)\ .NQ()\ .field('f3').equals('val3') self.assertEqual(str(q), 'f1=val1^f2BETWEENjavascript:gs.dateGenerate("2016-02-01 00:00:00")' '@javascript:gs.dateGenerate("2016-02-10 00:00:00")^NQf3=val3')
def getactivecitasks(ci): c = pysnow.Client(instance=theconfig['snowinstance'], user=theconfig['snowusername'], password=theconfig['snowpassword']) changetasks = c.resource(api_path='/table/task_ci') qb = ( pysnow.QueryBuilder() .field('ci_item.sys_id').equals(ci) .AND() .field('task.sys_class_name').equals('change_request') .AND() .field('task.state').equals('4') ) response = changetasks.get(query=qb, fields=['sys_id','task.number', 'ci_item']) return response.all()
def run(self): '''Main logic routine''' host = self.url user = self.username pwd = self.password api = self.api_path # Create ServiceNow client object c = pysnow.Client(host=host, user=user, password=pwd) c.parameters.display_value = True c.parameters.exclude_reference_link = True cmdb = c.resource(api_path=api) # Query qb = ( pysnow.QueryBuilder() .field('subcategory').contains('Windows Server') .AND() .field('u_status').equals('Deployed') .AND() .field('host_name').order_ascending() ) try: response = cmdb.get(query=qb) except pysnow.exceptions.ResponseError as e: sys.stderr.write(str(e)) sys.exit(EXIT_FAILURE) # Map SNOW record to something more convenient results = [ ] for record_raw in response.all(): record_refined = { "u_ci_id":record_raw['u_ci_id'], "classification":record_raw['classification'], "sys_class_name":record_raw['sys_class_name'], "u_technicalinfraapproval":record_raw['u_technicalinfraapproval'], "u_ccbapprover":record_raw['u_ccbapprover'], "host_name":record_raw['host_name'], "sys_domain":record_raw['sys_domain'], "category":record_raw['category'], "sys_class_name":record_raw['sys_class_name'], "fqdn":record_raw['fqdn'], "ip_address":record_raw['ip_address'], "os":record_raw['os'], "name":record_raw['name'], "company":record_raw['company'] } self.all_records = results
def getAllIncidentsOlderThan(username, passwd, daysago): today = datetime.today() days_ago_time = today - timedelta(days=int(daysago)) # Create client object c = pysnow.Client(instance=config.servicenow['instance'], user=username, password=passwd, use_ssl=False) # Define a resource, here we'll use the incident table API incident = c.resource(api_path='/table/incident') qb = pysnow.QueryBuilder().field('assignment_group').equals('').OR().field( 'assignment_group').equals('6230ba480f872500d7f84b9ce1050ec7').AND( ).field('assigned_to').is_empty().AND().field('active').equals( 'true').AND().field('state').not_equals('6').AND().field( 'sys_created_on').less_than(days_ago_time) # Query for incidents with state 1 response = incident.get(query=qb, limit=50, stream=True, fields=[ 'sys_id', 'opened_at', 'assignment_group', 'number', 'state', 'assigned_to', 'short_description', 'caller_id', 'contact_type', 'priority', 'subcategory', 'u_kick_back_counter' ]) x = PrettyTable() x.field_names = [ 'Number', 'Caller', 'Description', 'Category', 'Assignment Group', 'Opened At' ] # Iterate over the result and print out `sys_id` of the matching records. for record in response.all(): x.add_row([ record['number'], getUser(c, record), record['short_description'], record['subcategory'], getGroup(c, record), record['opened_at'] ]) x.sortby = "Opened At" x.reversesort = True return x
def getincidentbyci(ci, subcat): c = pysnow.Client(instance=theconfig['snowinstance'], user=theconfig['snowusername'], password=theconfig['snowpassword']) incidents = c.resource(api_path='/table/incident') qb = ( pysnow.QueryBuilder() .field('cmdb_ci.sys_id').equals(ci) .AND() .field('subcategory').equals(subcat) .AND() .field('incident_state').equals('1') .OR() .field('incident_state').equals('2') ) response = incidents.get(query=qb, fields=['sys_id','number']) return response.all()
def work(self): '''Main application logic''' #Query 2 months back. ANSIBLE test ticket was created on 5-18 minutes = self._module.params['minutes'] snow_group = self._module.params['group'] host = self._module.params['host'] user = self._module.params['user'] password = self._module.params['password'] now = datetime.today() minutes_delta = now - timedelta(minutes=minutes) # Create ServiceNow client object c = pysnow.Client(host=host, user=user, password=password) c.parameters.display_value = True c.parameters.exclude_reference_link = True # Query qb = ( pysnow.QueryBuilder() .field('sys_created_on').between(minutes_delta, now) .AND() .field('assignment_group').starts_with(snow_group) .AND() .field('state').equals('true') ) incident = c.resource(api_path='/table/incident') try: response = incident.get(query=qb) except pysnow.exceptions.ResponseError as e: self._module.fail_json(msg="Query failure: ({0})".format(e)) data = defaultdict(list) # Iterate over the matching records and print out number for record in response.all(): ticket_details = self.parse_description(record['description']) data['results'].append({"number":record['number'], "sys_id":record['sys_id'], "openTime":record['opened_at'], "assignmentGroup":record['assignment_group'],\ "shortDesc":record['short_description'], "desc":record['description'], "server":record['u_host_name'],\ "serviceName":ticket_details['service_name'], "escalationGroup":ticket_details['escalation_group'], "remediation":ticket_details['remediation'], \ "incident_state":record['incident_state'], "state":record['state'], "active":record['active'], "assigned_to":record['assigned_to'], \ "cmdb_ci":record['cmdb_ci'], "u_host_name":record['u_host_name'], \ }) return json.dumps(data['results'])
def __init__(self, module): self.module = module # Define query parameters self.data = module.params['query'] self.max_records = self.module.params['max_records'] self.order_by = self.module.params['order_by'] self.return_fields = self.module.params['return_fields'] # Define sort criteria self.reverse = False if self.order_by is not None: if self.order_by[0] == '-': self.reverse = True if self.order_by[0] in ['-', '+']: self.order_by = self.order_by[1:] # Define table parameters self.table = module.connection.resource(api_path='/table/' + self.module.params['table']) self.table.parameters.display_value = self.module.params[ 'display_value'] self.table.parameters.exclude_reference_link = self.module.params[ 'exclude_reference_link'] self.table.parameters.suppress_pagination_header = self.module.params[ 'suppress_pagination_header'] # Define query expression operators self.logic_operators = ["AND", "OR", "NQ"] self.condition_operator = { 'equals': self._condition_closure, 'not_equals': self._condition_closure, 'contains': self._condition_closure, 'not_contains': self._condition_closure, 'starts_with': self._condition_closure, 'ends_with': self._condition_closure, 'greater_than': self._condition_closure, 'less_than': self._condition_closure, } self.accepted_cond_ops = self.condition_operator.keys() self.append_operator = False self.simple_query = True # Build the query self.query = pysnow.QueryBuilder() self._iterate_operators(self.data)
def __init__(self, host=None, user=None, password=None, query_table=None, response=None): """ The following arguments can be optionally provided when importing this library: - ``host``: The URL to your target ServiceNow instance (e.g. https://iceuat.service-now.com/). If none is provided, the library will attempt to use the ``SNOW_TEST_URL`` environment variable. - ``user``: The username to use when authenticating the ServiceNow REST client. This can, and *should*, be set using the ``SNOW_REST_USER`` environment variable. - ``password``: The password to use when authenticating the ServiceNow REST client. This can, and *should*, be set using the ``SNOW_REST_PASS`` environment variable. - ``query_table``: The table to query. This can be changed or set at any time with the `Query Table Is` keyword. - ``response``: Set the response object from the ServiceNow REST API (intended to be used for testing). """ if host is None: self.host = os.environ.get("SNOW_TEST_URL").strip() else: self.host = host.strip() if user is None: self.user = os.environ.get("SNOW_REST_USER") else: self.user = user if password is None: self.password = os.environ.get("SNOW_REST_PASS") else: self.password = password if "http" not in self.host: self.instance = urlparse(self.host).path.split(".")[0] else: self.instance = urlparse(self.host).netloc.split(".")[0] if self.instance == "": raise AssertionError( "Unable to determine SNOW Instance. Verify that the SNOW_TEST_URL environment variable been set." ) self.client = pysnow.Client(instance=self.instance, user=self.user, password=self.password) self.query_table = query_table self.query = pysnow.QueryBuilder() self.response = response self.record_count = None self.desired_response_fields = list()
def get_ticket(ticket_id): # Create client object c = pysnow.Client(instance=INSTANCE, user=USER, password=PASSWORD) # Query incident records with number starting with 'INC0123', created between 60 days ago and today. qb = (pysnow.QueryBuilder().field('number').starts_with(ticket_id[:6])) incident = c.resource(api_path='/table/incident') response = incident.get(query=qb) # Iterate over the matching records and print out number for record in response.all(): # print(record['number']) if record['number'] == ticket_id: return record
def get_lab_owner_by_ip(snow_client, ip_address): # First three octet's are stored now under the variable name class_c_address class_c_address = ".".join(ip_address.split(".")[0:3]) # Defining a `Resource` for the incident API. lab_owner_resource = snow_client.resource(api_path='/table/u_lab_owners') # Query incident records based on subnets in the lab owner table in SNOW qb = (pysnow.QueryBuilder().field('u_subnet').contains(class_c_address)) response = lab_owner_resource.get(query=qb, stream=True) # Check all (filtered) lab owner ranges for IP address for potential_lab_owner_row in response.all(): print(potential_lab_owner_row) print(potential_lab_owner_row["u_subnet"]) try: # ip_addr is a class. ip_addr = IPAddress( ip_address) #tells you the ip range and does the calculations if "u_subnet" in potential_lab_owner_row and potential_lab_owner_row[ "u_subnet"]: subnet = IPNetwork(potential_lab_owner_row["u_subnet"]) if ip_addr in subnet: return potential_lab_owner_row else: print("{0} is not in subnet {1}".format( ip_address, potential_lab_owner_row["u_subnet"])) continue else: print('no u_subnet key in potential_lab_owner_row or blank') except Exception as e: print("ERR: {0}".format(potential_lab_owner_row)) # If IP address was not in any owner subnets, return None return None
def test_get_incident_by_qb(self): """ Make sure fetching by dict type query works """ json_body = json.dumps( {'result': [{ 'number': self.mock_incident['number'] }]}) httpretty.register_uri( httpretty.GET, "http://%s/%s" % (self.mock_connection['host'], self.mock_incident['path']), body=json_body, status=200, content_type="application/json") q = pysnow.QueryBuilder().field('number').equals( self.mock_incident['number']) r = self.client.query(table='incident', query=q) # Make sure we got an incident back with the expected number self.assertEqual(r.get_one()['number'], self.mock_incident['number'])