Help with a csv file load and a transform map run only if update is new

giffordj · ‎11-17-2015

Hello,

New to Servicenow. I have a daily feed coming in of users. It was setup by our SericeNow partners, it was running in 1-3 hours now takes up to 10+ hours to run. We had a new field added to the csv called Last updated date. It has the date and time the record was last updated. Is there a way to create maybe a onbefore script that says if the date was yesterday or today load the record else don't. This way only newly updated records come in and this will save many hours or processing time. I did have ServiceNow support look at this and they don't know why the time jumped up to load the records from the staging table into the new table after the transform. Any help is greatly appreciated

thank you

TrevorK · ‎11-17-2015

I think you have the right idea - you can create an onBefore Transform Script (Transform Map Scripts - ServiceNow Wiki ) that will validate each row to decide whether to insert/update. You will see in the link above there is an ignore = true attribute that, when set in an onBefore Transform Script, will ignore the entire row. Therefore, your condition will evaluate the date and when it evaluates outside today/yesterday, set ignore = true; otherwise proceed.

I would be curious though as to why the CSV cannot be refined at the source? It would seem that would solve all the problems rather than relying on ServiceNow to parse through the spreadsheet for you. As well, the time your load takes seems odd. It only takes 15-20 minutes to go through our LDAP user load (over 180,000 users) when we run it so I really wonder how big this spreadsheet is you are using.

Also, you may want to look into Data Sources (http://wiki.servicenow.com/index.php?title=Data_Sources#gsc.tab=0). We use Data Sources to retrieve data directly from the data base, and this allows us to use a query so we can filter the data before it ever comes to ServiceNow. Perhaps this is an option?

There would be a couple other ways to address this (e.g.: through coalescing, perhaps a business rule to prevent insert of entries outside the date range into the table in the first place, etc.), but the above directly answers your question about how to do it from within your Transform Map.

View solution in original post

TrevorK · ‎11-17-2015

Can you post the date format your CSV has? Dates can definitely be a tricky one to do but there is no reason we cannot do a little javascript magic to make them comparable if need be.

(this is of course assuming your date format is consistent)

giffordj · ‎11-17-2015

Here is an example

11/16/2015 23:50

11/16/2015 23:45

TrevorK · ‎11-17-2015

You can do it with GlideDateTime, but I find Javascript easier to work with. Here is something I was running in "Scripts - Background" to test (you need to elevate permissions to see it):

var supplied_date = new Date("11/16/2015 23:00");



var today_date = new Date();



gs.print(supplied_date.getDate() + " " + today_date.getDate());

The gs.print will show you the days of each. In your case you would use the days of each to do your compare.

You can use GlideDateTime (GDT) as well, but you need to remember to make adjustments for the time zone.

Here is an example using GlideDateTime that shows how we ignore the time zone for the value we bring in, but we acknowledge the time zone when getting today's value:

var supplied = new GlideDateTime();

supplied.setValue("11/16/2015 23:00:00");



// No conversion to my time zone

gs.print(supplied.getValue());



var now = new GlideDateTime();

now.setTZ(gs.getSession().getTimeZone());



// Convert to my time zone

gs.print(now.getDisplayValue());



gs.print(gs.dateDiff(now.getDisplayValue(),supplied, true));

Sorry for the rushed response, I hope it makes sense. I just have to run off to a meeting but wanted to get it to you before I left!

TrevorK · ‎11-17-2015

I had a chance to do up an onBefore for you. I tested this out using a CSV and it works fine:

var supplied_datetime = new GlideDateTime();

supplied_datetime.setValue(source.u_date);


var now_datetime = new GlideDateTime(); 


var now_day = now_datetime.getDayOfMonthLocalTime();

var supplied_day = supplied_datetime.getDayOfMonthLocalTime();

var day_diff = now_day - supplied_day;


if (day_diff == 0 || day_diff == 1) {

       // Either today or yesterday

       // Can't do a <= 1 since it could be negative

       gs.log("TEST: Today or yesterday. " + source.u_date);

}

else {

       // Not today or yesterday

       gs.log("TEST: NOT today or yesterday. " + source.u_date);

}

This was my CSV:

Date, Name

11/16/2015 23:00:00, Test16

11/14/2015 23:00:00, Test14

Hopefully that gives you enough to get it going! Just remember to use the ignore = true; in whatever condition you want to ignore the action.

giffordj · ‎11-18-2015

Trevor,

Thank you for the help. I see where I was off on my script.