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

gokulraj · ‎04-01-2019

Hi Chuck,

I don't see any updated field (sys_updated_on) in sys_audit table .

How to find find the last updated time of a field

Chuck Tomasi · ‎04-01-2019

Interesting, I didn't see that. This table doesn't need an updated date field because those records aren't updated. They are meant to record the time/date a change was made and the records are immutable. Use the sys_created_on field instead.

gokulraj · ‎04-01-2019

Yes, I agree and I got the recent updated value of a field by using(orderByDesc("sys_created_on");)

Chuck Tomasi · ‎04-01-2019

If you want the most recent, don't forget to use setLimit(1) to tell the database, you only need one record. It will make things faster than retrieving all records.