Need help with the script to calculate the duration between 2 dates

Rohit8 · ‎01-05-2018

Hello,

Wish you all a very Happy New Year.

I am trying to build an Indicator to calculate the Summed age of Assets (to later find out the Average age). The asset table has the columns for Release Date and Retired Date and I need to calculate their difference. The screenshot (A) has the format of the dates.

The 2nd screenshot has the script screen and I am using the above variables. I am not proficient with scripts at all so I am pretty sure I am doing something totally wrong.

var diff=function(x,y){return y.dateNumericValue() - x.dateNumericValue();};

var years=function(x,y){return diff(x,y)/(365*24*60*60*1000);};

years(current.u_avg_hw_release_date, current.retired_date_at);

The job returns the error:

Error during JavaScript evaluation com.snc.pa.dc.ScriptException: JavaScript evaluation returned: NaN in script: var diff=function(x,y){return y.dateNumericValue() - x.dateNumericValue();};

var years=function(x,y){return diff(x,y)/(365*24*60*60*1000);};

years(current.u_avg_hw_release_date, current.retired_date_at);

at com.snc.pa.dc.Script.handleException(Script.java:215)

at com.snc.pa.dc.Script.evaluate(Script.java:179)

at com.snc.pa.dc.Script.evaluate(Script.java:156)

at com.snc.pa.dc.DataCollector.collect(DataCollector.java:261)

at com.snc.pa.dc.DataCollector.collect(DataCollector.java:214)

at com.snc.pa.dc.DataCollector.collect(DataCollector.java:172)

at com.snc.pa.dc.DataCollectorJob.collect(DataCollectorJob.java:246)

at com.snc.pa.dc.DataCollectorJob.collectWithMutex(DataCollectorJob.java:162)

at com.snc.pa.dc.DataCollectorJob.execute(DataCollectorJob.java:147)

at com.glide.schedule.JobExecutor.executeJob(JobExecutor.java:103)

at com.glide.schedule.JobExecutor.execute(JobExecutor.java:89)

at com.glide.schedule.GlideScheduleWorker.executeJob(GlideScheduleWorker.java:219)

at com.glide.schedule.GlideScheduleWorker.lambda$process$39(GlideScheduleWorker.java:161)

at com.glide.worker.TransactionalWorkerThread.executeInTransaction(TransactionalWorkerThread.java:35)

at com.glide.schedule.GlideScheduleWorker.process(GlideScheduleWorker.java:161)

at com.glide.schedule.GlideScheduleWorker.run(GlideScheduleWorker.java:72)

Caused by: java.lang.Exception: JavaScript evaluation returned: NaN

... 15 more

Can anyone help me correct the script or let me know what am I doing wrong.

Thanks a lot.

Thilo Grobe · ‎01-06-2018

Last try

var sgd1 = new GlideDate();   

var sgd2 = new GlideDate();   

sgd1.setDisplayValue(current.retired.getDisplayValue());   

sgd2.setDisplayValue(current.u_abg_hw_release_date.getDisplayValue());   

var duration = GlideDate.subtract(sgd1, sgd2);   

Math.floor(duration.getDayPart()/365);

Cheers,

Thilo

View solution in original post

Thilo Grobe · ‎01-06-2018

Last try

var sgd1 = new GlideDate();   

var sgd2 = new GlideDate();   

sgd1.setDisplayValue(current.retired.getDisplayValue());   

sgd2.setDisplayValue(current.u_abg_hw_release_date.getDisplayValue());   

var duration = GlideDate.subtract(sgd1, sgd2);   

Math.floor(duration.getDayPart()/365);

Cheers,

Thilo

Rohit8 · ‎01-06-2018

Thank you so much for your help so far.

So, I saved the script and ran the job and finally it ran without any errors (but it did have warnings) !!

When I checked the Indicator scorecard, it doesn't seem to have any score at all and has 0 instead (because of what I selected for Value when Nil).

Data Collector logs:

SequenceSort	LevelSort	MessageSort	SourceSort
Select record for action	125	07-Jan-18 01:32:31	Warning	No data was collected between 01-Jan-08 and 31-Dec-08 for indicator "Summed age of Assets (Desktops)" in the indicator source "Assets.Workstations.Yearly". Data within this range is older than the maximum age, based on the indicator frequency and collection periods.	DataCollector
Select record for action	88	07-Jan-18 01:32:29	Warning	No list of records (snapshot) was collected between 01-Jan-04 and 01-Jan-11 for indicator source "Assets.Workstations.Yearly". Snapshot within this range is older than the maximum age, based on indicator frequency and snapshot periods, for all indicators associated with this indicator source.	DataCollector
Select record for action	126	07-Jan-18 01:32:31	Warning	No data was collected between 01-Jan-04 and 01-Jan-08 for indicator source "Assets.Workstations.Yearly". Data within this range is older than the maximum age, based on indicator frequency and collection periods, for all indicators associated with this indicator source.	DataCollector
Select record for action	87	07-Jan-18 01:32:29	Warning	No list of records (snapshot) was collected between 01-Jan-11 and 31-Dec-11 for indicator "Summed age of Assets (Desktops)" in the indicator source "Assets.Workstations.Yearly". Snapshot within this range is older than the maximum age, based on the indicator frequency and snapshot periods.	DataCollector
Select record for action	122	07-Jan-18 01:32:31	Information	Stored 'Value when nil': 0.0 as score for indicator: Summed age of Assets (Desktops)	DataCollector
Select record for action	22	07-Jan-18 01:32:10	Information	Deleted previous results 0	DataCollector
Select record for action	61	07-Jan-18 01:32:24	Information	Stored collected results	DataCollector
Select record for action	6	07-Jan-18 01:31:58	Information	Fetched 20,538 rows from alm_hardware	DataCollector
Select record for action	130	07-Jan-18 01:32:31	Information	Analyzed table pa_snapshots successfully	DataCollector
Select record for action	50	07-Jan-18 01:32:21	Information	Collecting for 20150101 finished	DataCollector
Select record for action	8	07-Jan-18 01:32:04	Information	Applied map/reduce function	DataCollector
Select record for action	67	07-Jan-18 01:32:24	Information	Applying map/reduce function for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	103	07-Jan-18 01:32:30	Information	SELECT alm_hardware0.`sys_id` FROM ((alm_hardware alm_hardware0 INNER JOIN alm_asset alm_asset0 ON alm_hardware0.`sys_id` = alm_asset0.`sys_id` ) LEFT JOIN cmdb_model cmdb_model1 ON alm_asset0.`model` = cmdb_model1.`sys_id` ) WHERE alm_asset0.`install_status` IN (6 , 1) AND cmdb_model1.`u_subcategory` IN ('Desktops' , 'Laptop' , 'Notebooks') AND alm_asset0.`u_avg_hw_release_date` <= '2010-12-31'	DataCollector
Select record for action	82	07-Jan-18 01:32:29	Information	Deleted previous results 0	DataCollector
Select record for action	100	07-Jan-18 01:32:30	Information	Collecting for 20110101 finished	DataCollector
Select record for action	109	07-Jan-18 01:32:31	Information	Storing collected results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	23	07-Jan-18 01:32:10	Information	Storing collected results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	110	07-Jan-18 01:32:31	Information	Stored 'Value when nil': 0.0 as score for indicator: Summed age of Assets (Desktops)	DataCollector
Select record for action	38	07-Jan-18 01:32:16	Information	Collecting for 20160101 finished	DataCollector
Select record for action	107	07-Jan-18 01:32:31	Information	Deleting previous results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	132	07-Jan-18 01:32:31	Information	Collection ended	DataCollector
Select record for action	51	07-Jan-18 01:32:21	Information	Collecting for 20140101	DataCollector
Select record for action	9	07-Jan-18 01:32:04	Information	Deleting previous results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	78	07-Jan-18 01:32:27	Information	Fetched 7,255 rows from alm_hardware	DataCollector
Select record for action	108	07-Jan-18 01:32:31	Information	Deleted previous results 0	DataCollector
Select record for action	91	07-Jan-18 01:32:29	Information	SELECT alm_hardware0.`sys_id` FROM ((alm_hardware alm_hardware0 INNER JOIN alm_asset alm_asset0 ON alm_hardware0.`sys_id` = alm_asset0.`sys_id` ) LEFT JOIN cmdb_model cmdb_model1 ON alm_asset0.`model` = cmdb_model1.`sys_id` ) WHERE alm_asset0.`install_status` IN (6 , 1) AND cmdb_model1.`u_subcategory` IN ('Desktops' , 'Laptop' , 'Notebooks') AND alm_asset0.`u_avg_hw_release_date` <= '2011-12-31'	DataCollector
Select record for action	113	07-Jan-18 01:32:31	Information	Collecting for 20090101	DataCollector
Select record for action	117	07-Jan-18 01:32:31	Information	Applying map/reduce function for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	106	07-Jan-18 01:32:31	Information	Applied map/reduce function	DataCollector
Select record for action	114	07-Jan-18 01:32:31	Information	Fetching "model.u_subcategory,sys_id,u_avg_hw_release_date,install_status,retired" from "alm_hardware"	DataCollector
Select record for action	111	07-Jan-18 01:32:31	Information	Stored collected results	DataCollector
Select record for action	13	07-Jan-18 01:32:04	Information	Stored collected results	DataCollector
Select record for action	1	07-Jan-18 01:31:56	Information	Starting collection	DataCollector
Select record for action	10	07-Jan-18 01:32:04	Information	Deleted previous results 0	DataCollector
Select record for action	42	07-Jan-18 01:32:16	Information	Fetched 16,790 rows from alm_hardware	DataCollector
Select record for action	116	07-Jan-18 01:32:31	Information	Fetched 319 rows from alm_hardware	DataCollector
Select record for action	92	07-Jan-18 01:32:30	Information	Fetched 3,355 rows from alm_hardware	DataCollector
Select record for action	30	07-Jan-18 01:32:11	Information	Fetched 19,485 rows from alm_hardware	DataCollector
Select record for action	97	07-Jan-18 01:32:30	Information	Storing collected results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	115	07-Jan-18 01:32:31	Information	SELECT alm_hardware0.`sys_id` FROM ((alm_hardware alm_hardware0 INNER JOIN alm_asset alm_asset0 ON alm_hardware0.`sys_id` = alm_asset0.`sys_id` ) LEFT JOIN cmdb_model cmdb_model1 ON alm_asset0.`model` = cmdb_model1.`sys_id` ) WHERE alm_asset0.`install_status` IN (6 , 1) AND cmdb_model1.`u_subcategory` IN ('Desktops' , 'Laptop' , 'Notebooks') AND alm_asset0.`u_avg_hw_release_date` <= '2009-12-31'	DataCollector
Select record for action	31	07-Jan-18 01:32:11	Information	Applying map/reduce function for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	129	07-Jan-18 01:32:31	Information	Starting to analyze table pa_snapshots	DataCollector
Select record for action	120	07-Jan-18 01:32:31	Information	Deleted previous results 0	DataCollector
Select record for action	54	07-Jan-18 01:32:21	Information	Fetched 12,346 rows from alm_hardware	DataCollector
Select record for action	12	07-Jan-18 01:32:04	Information	Stored 'Value when nil': 0.0 as score for indicator: Summed age of Assets (Desktops)	DataCollector
Select record for action	17	07-Jan-18 01:32:04	Information	SELECT alm_hardware0.`sys_id` FROM ((alm_hardware alm_hardware0 INNER JOIN alm_asset alm_asset0 ON alm_hardware0.`sys_id` = alm_asset0.`sys_id` ) LEFT JOIN cmdb_model cmdb_model1 ON alm_asset0.`model` = cmdb_model1.`sys_id` ) WHERE alm_asset0.`install_status` IN (6 , 1) AND cmdb_model1.`u_subcategory` IN ('Desktops' , 'Laptop' , 'Notebooks') AND alm_asset0.`u_avg_hw_release_date` <= '2017-12-31'	DataCollector
Select record for action	55	07-Jan-18 01:32:21	Information	Applying map/reduce function for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	96	07-Jan-18 01:32:30	Information	Deleted previous results 0	DataCollector
Select record for action	74	07-Jan-18 01:32:27	Information	Collecting for 20130101 finished	DataCollector
Select record for action	101	07-Jan-18 01:32:30	Information	Collecting for 20100101	DataCollector
Select record for action	105	07-Jan-18 01:32:31	Information	Applying map/reduce function for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	60	07-Jan-18 01:32:24	Information	Stored 'Value when nil': 0.0 as score for indicator: Summed age of Assets (Desktops)	DataCollector
Select record for action	98	07-Jan-18 01:32:30	Information	Stored 'Value when nil': 0.0 as score for indicator: Summed age of Assets (Desktops)	DataCollector
Select record for action	59	07-Jan-18 01:32:24	Information	Storing collected results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	56	07-Jan-18 01:32:24	Information	Applied map/reduce function	DataCollector
Select record for action	2	07-Jan-18 01:31:56	Information	Processing Indicator Source Assets.Workstations.Yearly	DataCollector
Select record for action	90	07-Jan-18 01:32:29	Information	Fetching "model.u_subcategory,sys_id,u_avg_hw_release_date,install_status,retired" from "alm_hardware"	DataCollector
Select record for action	45	07-Jan-18 01:32:20	Information	Deleting previous results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	19	07-Jan-18 01:32:04	Information	Applying map/reduce function for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	104	07-Jan-18 01:32:31	Information	Fetched 1,427 rows from alm_hardware	DataCollector
Select record for action	3	07-Jan-18 01:31:58	Information	Collecting for 20180101	DataCollector
Select record for action	69	07-Jan-18 01:32:27	Information	Deleting previous results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	26	07-Jan-18 01:32:10	Information	Collecting for 20170101 finished	DataCollector
Select record for action	64	07-Jan-18 01:32:24	Information	Fetching "model.u_subcategory,sys_id,u_avg_hw_release_date,install_status,retired" from "alm_hardware"	DataCollector
Select record for action	102	07-Jan-18 01:32:30	Information	Fetching "model.u_subcategory,sys_id,u_avg_hw_release_date,install_status,retired" from "alm_hardware"	DataCollector
Select record for action	39	07-Jan-18 01:32:16	Information	Collecting for 20150101	DataCollector
Select record for action	65	07-Jan-18 01:32:24	Information	SELECT alm_hardware0.`sys_id` FROM ((alm_hardware alm_hardware0 INNER JOIN alm_asset alm_asset0 ON alm_hardware0.`sys_id` = alm_asset0.`sys_id` ) LEFT JOIN cmdb_model cmdb_model1 ON alm_asset0.`model` = cmdb_model1.`sys_id` ) WHERE alm_asset0.`install_status` IN (6 , 1) AND cmdb_model1.`u_subcategory` IN ('Desktops' , 'Laptop' , 'Notebooks') AND alm_asset0.`u_avg_hw_release_date` <= '2013-12-31'	DataCollector
Select record for action	81	07-Jan-18 01:32:29	Information	Deleting previous results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	84	07-Jan-18 01:32:29	Information	Stored 'Value when nil': 0.0 as score for indicator: Summed age of Assets (Desktops)	DataCollector
Select record for action	49	07-Jan-18 01:32:20	Information	Stored collected results	DataCollector
Select record for action	16	07-Jan-18 01:32:04	Information	Fetching "model.u_subcategory,sys_id,u_avg_hw_release_date,install_status,retired" from "alm_hardware"	DataCollector
Select record for action	21	07-Jan-18 01:32:10	Information	Deleting previous results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	124	07-Jan-18 01:32:31	Information	Collecting for 20090101 finished	DataCollector
Select record for action	73	07-Jan-18 01:32:27	Information	Stored collected results	DataCollector
Select record for action	68	07-Jan-18 01:32:27	Information	Applied map/reduce function	DataCollector
Select record for action	32	07-Jan-18 01:32:16	Information	Applied map/reduce function	DataCollector
Select record for action	62	07-Jan-18 01:32:24	Information	Collecting for 20140101 finished	DataCollector
Select record for action	33	07-Jan-18 01:32:16	Information	Deleting previous results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	93	07-Jan-18 01:32:30	Information	Applying map/reduce function for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	14	07-Jan-18 01:32:04	Information	Collecting for 20180101 finished	DataCollector
Select record for action	24	07-Jan-18 01:32:10	Information	Stored 'Value when nil': 0.0 as score for indicator: Summed age of Assets (Desktops)	DataCollector
Select record for action	89	07-Jan-18 01:32:29	Information	Collecting for 20110101	DataCollector
Select record for action	44	07-Jan-18 01:32:20	Information	Applied map/reduce function	DataCollector
Select record for action	25	07-Jan-18 01:32:10	Information	Stored collected results	DataCollector
Select record for action	20	07-Jan-18 01:32:10	Information	Applied map/reduce function	DataCollector
Select record for action	119	07-Jan-18 01:32:31	Information	Deleting previous results for indicator source Assets.Workstations.Yearly	DataCollector
Select record for action	77	07-Jan-18 01:32:27	Information	SELECT alm_hardware0.`sys_id` FROM ((alm_hardware alm_hardware0 INNER JOIN alm_asset alm_asset0 ON alm_hardware0.`sys_id` = alm_asset0.`sys_id` ) LEFT JOIN cmdb_model cmdb_model1 ON alm_asset0.`model` = cmdb_model1.`sys_id` ) WHERE alm_asset0.`install_status` IN (6 , 1) AND cmdb_model1.`u_subcategory` IN ('Desktops' , 'Laptop' , 'Notebooks') AND alm_asset0.`u_avg_hw_release_date` <= '2012-12-31'	DataCollector
Select record for action	41	07-Jan-18 01:32:16	Information	SELECT alm_hardware0.`sys_id` FROM ((alm_hardware alm_hardware0 INNER JOIN alm_asset alm_asset0 ON alm_hardware0.`sys_id` = alm_asset0.`sys_id` ) LEFT JOIN cmdb_model cmdb_model1 ON alm_asset0.`model` = cmdb_model1.`sys_id` ) WHERE alm_asset0.`install_status` IN (6 , 1) AND cmdb_model1.`u_subcategory` IN ('Desktops' , 'Laptop' , 'Notebooks') AND alm_asset0.`u_avg_hw_release_date` <= '2015-12-31'	DataCollector
Select record for action	63	07-Jan-18 01:32:24	Information	Collecting for 20130101	DataCollector
Select record for action	127	07-Jan-18 01:32:31	Information	Starting to analyze table pa_scores	DataCollector
Select record for action	37	07-Jan-18 01:32:16	Information	Stored collected results	DataCollector
Select record for action	85	07-Jan-18 01:32:29	Information	Stored collected results	DataCollector
Select record for action	18	07-Jan-18 01:32:04	Information	Fetched 20,538 rows from alm_hardware	DataCollector
Select record for action	28	07-Jan-18 01:32:10	Information	Fetching "model.u_subcategory,sys_id,u_avg_hw_release_date,install_status,retired" from "alm_hardware"	DataCollector
Select record for action	86	07-Jan-18 01:32:29	Information	Collecting for 20120101 finished	DataCollector
Select record for action	53	07-Jan-18 01:32:21	Information	SELECT alm_hardware0.`sys_id` FROM ((alm_hardware alm_hardware0 INNER JOIN alm_asset alm_asset0 ON alm_hardware0.`sys_id` = alm_asset0.`sys_id` ) LEFT JOIN cmdb_model cmdb_model1 ON alm_asset0.`model` = cmdb_model1.`sys_id` ) WHERE alm_asset0.`install_status` IN (6 , 1) AND cmdb_model1.`u_subcategory` IN ('Desktops' , 'Laptop' , 'Notebooks') AND alm_asset0.`u_avg_hw_release_date` <= '2014-12-31'	DataCollector
Select record for action	72	07-Jan-18 01:32:27	Information	Stored 'Value when nil': 0.0 as score for indicator: Summed age of Assets (Desktops)	DataCollector
Select record for action	29	07-Jan-18 01:32:10	Information	SELECT alm_hardware0.`sys_id` FROM ((alm_hardware alm_hardware0 INNER JOIN alm_asset alm_asset0 ON alm_hardware0.`sys_id` = alm_asset0.`sys_id` ) LEFT JOIN cmdb_model cmdb_model1 ON alm_asset0.`model` = cmdb_model1.`sys_id` ) WHERE alm_asset0.`install_status` IN (6 , 1) AND cmdb_model1.`u_subcategory` IN ('Desktops' , 'Laptop' , 'Notebooks') AND alm_asset0.`u_avg_hw_release_date` <= '2016-12-31'	DataCollector
Select record for action	123	07-Jan-18 01:32:31	Information	Stored collected results	DataCollector
Select record for action	118	07-Jan-18 01:32:31	Information	Applied map/reduce function	DataCollector

Bottom of table

I have been getting the errors about Maximum age when I try to go further back than 2010 with other indicators, but they do return the latest results from last 6-7 years. And there are Assets in the table with the Release and Retired Data both from last 6-7 years,

Thank you.

Rohit8 · ‎01-08-2018

Thilo,

I figured it out. I checked my Indicator trail and it turned out that the 0 result was due to the Indicator source not configured to include the "Retired" machines. It was only set up to include the "In stock" or "In use" machines. Hence, it was not able to use the Retired machines in the calculation thus had nothing to calculate.

After correcting that, your script worked like a charm !!

In fact, the script I provided initially worked fine too after correcting the field names.

var diff=function(x,y){return y.dateNumericValue() - x.dateNumericValue();};

var years=function(x,y){return diff(x,y)/(365*24*60*60*1000);};

years(current.u_avg_hw_release_date,current.retired);

I can't thank you enough for hanging through this, with me.

Sorry for all the headache I might've caused you.