Database view and query BR question

[mpaskov]

Hello,

I have a database view which combines the tables - kb_knowledge, metric_instance, problem and sys_user. I need to filter the entries in the table based on metric_sys_created_on and present just the entry with the oldest date for each metric_id=kb_source. Is there a way that can be done with query BR or some other way?

Regards

[Brad Bowman]

I'm assuming your Database View is showing the expected records without this filtering?  If so, the script on your before Query Business Rule that uses the Database View Name as the Table would look like this:

(function executeRule(current, previous /*null when async*/) {

	var prevMet = '';
	var ansArr = [];
	var met = new GlideRecord('u_bkb_metric'); //Database View Name
	met.orderBy('metric_sys_created_on');
	met.query();
	while (met.next()) {
		if (met.metric_id == met.kb_source) {
			if (prevMet != met.metric_id.toString()) {
				prevMet = met.metric_id.toString();
				ansArr.push(met.metric_sys_id.toString());
			}
		}
	}
	current.addQuery('metric_sys_id', 'IN', ansArr.join(','));

})(current, previous);

I tested this with only the metric and kb tables in the Database View, and my first if condition used kb_sys_id since I don't have any KB records with a source in my instance, but I was able to see that it filtered the records to only show KBs, and only the earliest metric record.

[mpaskov]

Hi, I like your implementation. I already used a different approach described here https://www.servicenow.com/community/developer-forum/is-there-a-way-to-report-on-audits-in-a-way-tha...

In a few words - I have created a BR in the problem table which under certain conditions creates entry in the metric_instance table using a certain metric definition. And this is the only entry that is created there which perfectly suits my needs.

Thanks for your reply.