database views and not exists clause

mark_sonter
Mega Contributor

‎02-10-2015 02:09 PM

Hi

One of our business groups would like to perform a quality check on incident requests to report on a list of incidents assigned to the group that don't have an entry in the affected ci related list. I have looked through the database view wiki article

as to see if a not exists clause could be used to create a view over the incident table but cannot find anything describing if and how this could be done. Has anyone done this before and if so, how?

Thanks in advance

Labels:
0 Helpfuls
  • 1,299 Views
1 REPLY 1

Michael Ritchie
ServiceNow Employee
ServiceNow Employee

‎02-10-2015 03:19 PM

Out of the box there is a business rule that runs on the task table called "Sync CI with Affected CIs" that creates task_ci records for the CI (cmdb_ci) associated to the task.   So if you require the CI on all your incidents you shouldn't have an incident with out an Affected CI.   Just wanted to bring this up in case this solves your challenge.   So in this case you can just search for incidents that don't have a CI.



Now if for some reason this doesn't work for you...


It will be difficult to do this with a database view.   You can certainly run a background script to produce the results, but it seems like this may be something that needs to be validated on a regular basis.   If so my recommendation would be to use the Compliance Application:


http://wiki.servicenow.com/index.php?title=Compliance



Specifically you will want to create a Scripted Audit and create a script that looks for incidents without an affected CI:


http://wiki.servicenow.com/index.php?title=Scripted_Audits



Then you schedule it and it will create an audit task for any incident that doesn't meet the criteria of having


0 Helpfuls