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'])
