Get Date User last updated a record using GlideAggregate

reginabautista · ‎09-05-2017

Hi guys,

I am trying to get the date a user has last updated a record. I was querying the sys_audit table using GlideRecord. However I was advised to use GlideAggregate as this is much efficient than GR. Here's my code but so far no luck. The errors seems to happen on line 15. Could you guys have a look and let me know what am I doing wrong? Thanks - Regina

var dtStart = new Date().getTime();
gs.log('Start Processing License Automatiion Scheduled Process   - Part 1 ' + dtStart);
//Retrieve all Users here
var dtLastUpdate;
var dtLastCreate;
var userID;
//Show when they last updated a ticket
var aggAudit = new GlideAggregate('sys_audit');
aggAudit.addEncodedQuery('sys_created_onONYesterday@javascript:gs.daysAgoStart(1)@javascript:gs.daysAgoEnd(1)^user!=system^fieldname!=DELETED');
aggAudit.groupBy('user');
//aggAudit.setLimit(1);
aggAudit.query();
while(aggAudit.next()){
//get the first record, this should be the latest
dtLastUpdate = aggAudit.getValue('sys_created_on');
gs.log('dtLastUpdate ' + dtLastUpdate);
}
var dtEnd = new Date().getTime();
gs.log('End Processing License Automatiion Scheduled Process   - Part 1 ' +   dtEnd);

These are the error I am getting:

Error getting record for sys_audit: java.lang.NullPointerException: com.glide.db.meta.Table.queryAggregate(Table.java:366)

com.glide.db.meta.Table.query(Table.java:187)

com.glide.script.GlideRecordITable.query(GlideRecordITable.java:83)

com.glide.script.GlideRecord.query0(GlideRecord.java:3106)

com.glide.script.GlideRecord.query(GlideRecord.java:2831)

com.glide.script.GlideRecord.jsFunction_query(GlideRecord.java:2714)

sun.reflect.GeneratedMethodAccessor425.invoke(Unknown Source)

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

java.lang.reflect.Method.invoke(Method.java:498)

org.mozilla.javascript.MemberBox.invoke(MemberBox.java:138)

org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:637)

org.mozilla.javascript.FunctionObject.call(FunctionObject.java:581)

org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2571)

org.mozilla.javascript.optimizer.OptRuntime.callProp0(OptRuntime.java:85)

org.mozilla.javascript.gen.null_null_script_2948._c_script_0(null.null.script:15)

org.mozilla.javascript.gen.null_null_script_2948.call(null.null.script)

org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:563)

org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3432)

org.mozilla.javascript.gen.null_null_script_2948.call(null.null.script)

org.mozilla.javascript.gen.null_null_script_2948.exec(null.null.script)

com.glide.script.ScriptEvaluator.execute(ScriptEvaluator.java:259)

com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:110)

com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:76)

com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:334)

com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:259)

com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:246)

com.glide.processors.ScriptProcessor.evaluateScript(ScriptProcessor.java:320)

com.glide.processors.ScriptProcessor.runScript(ScriptProcessor.java:215)

com.glide.processors.ScriptProcessor.process(ScriptProcessor.java:173)

com.glide.processors.AProcessor.runProcessor(AProcessor.java:415)

com.glide.processors.AProcessor.processTransaction(AProcessor.java:186)

com.glide.processors.ProcessorRegistry.process0(ProcessorRegistry.java:178)

com.glide.processors.ProcessorRegistry.process(ProcessorRegistry.java:167)

com.glide.ui.GlideServletTransaction.process(GlideServletTransaction.java:49)

com.glide.sys.Transaction.run(Transaction.java:1976)

java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

java.lang.Thread.run(Thread.java:748)

reginabautista · ‎09-05-2017

Hi Chuck I was able to accomplish this using the below code.

var dtStart = new Date().getTime();

gs.log('Start Processing License Automatiion Scheduled Process - Part 1 ' + dtStart);

//Retrieve all Users here

var dtLastUpdate;

var dtLastCreate;

var userID;

//Show when they last updated a ticket

var aggAudit = new GlideAggregate('sys_audit');

aggAudit.addEncodedQuery('sys_created_onONYesterday@javascript:gs.daysAgoStart(1)@javascript:gs.daysAgoEnd(1)^user!=system^fieldname!=DELETED');

aggAudit.addAggregate('MAX', 'sys_created_on');

aggAudit.groupBy('user');

aggAudit.query();

while(aggAudit.next()){

//get the first record, this should be the latest

dtLastUpdate = aggAudit.getAggregate('MAX','sys_created_on');

gs.log('user=' +aggAudit.user + ' dtLastUpdate ' + dtLastUpdate);

}

var dtEnd = new Date().getTime();

gs.log('End Processing License Automatiion Scheduled Process - Part 1 ' + dtEnd);

View solution in original post

Chuck Tomasi · ‎09-05-2017

Hi Regina,

Are you looking for the date that a record was last updated? If so, that's on the record as the sys_updated_on field and sys_updated_by tells you who did it.

If you are looking for a specific user in some historical reference, for example, "When is the last time Chuck updated the record?" (even if I wasn't the last person to update it... I wouldn't recommend using sys_audit. It's a big table and querying it can cause performance issues. To do what you seek, I recommend your own "last updated" table that goes along with the table you want to audit and store only the information you need with an after business rule. Now you can get your information from that table. Yeah, it's a bit of duplicate work from what the system is storing, but it's a specialized implementation that will run much quicker.

FWIW, GlideAggregate is used to get you COUNT, MIN, MAX, SUM, AVG values, it wont return the details of the record it found - only the metadata around the "aggregate" values found - e.g. how many records were updated today? Not the details of those records.

reginabautista · ‎09-05-2017

Hey Chuck for the quick reply!

Basically what we need is to retrieve the date when a user last made an update to any record within Task and all its extended tables. I like your idea of creating our own table to store the last updated as sys_audit is indeed a huge table. Thanks for the tip!

Thanks also for the info about the GlideAggregate, that made sense.

reginabautista · ‎09-05-2017