Hast anyone successfully used "Use last run datetime:" on Import Data Source

[hartr]

I'm trying to query a large Oracle database using the Use last run datetime: function to get incremental data. The problem I have is that if I manually populate the Last Run datetime field with "to_date('2011-11-02','yyyy-mm-dd')" then the query works (although only if I query all records, not if I specify the SQL), however the Last Run datetime field doesn't then update with the date of the latest record in the previous run as it's supposed to.

Has anyone got this feature working ? Or know what controls how the Last run datetime: field is updated ?

Thanks

[Brian Arndt1]

Hi Chris,

Running into the same issue with a WHERE clause at the end of my current query but not sure what a subquery looks like. Could you provide an example?

Thanks!

[rlatorre]

I also had the need to add a WHERE clause as well as use the 'Last run datetime' filter for our SCCM Software import.

I decided to calculate the SELECT query myself and include the 'Last run datetime' filter. I still wanted to use the fields in the record.

I changed the UI policy to display the 'Last run datetime' field on that specific Data Source even though the 'Use last run datetime' was un-checked (prevent the system from appending the filter).

I created a Business Rule on update to calculate the select query:

current.sql_statement = "SELECT\n\ndisplayname0 as DisplayName,\nversion0 as Version,\ninstalldate0 as InstallDate,\nprodID0 as ProdID,\npublisher0 as Publisher,\ntimestamp as TimeStamp,\nrevisionid,\nresourceID\n\nFROM V_Add_Remove_Programs_Data_Staging\n\nWHERE DisplayName0 NOT LIKE '%update%'\nAND DisplayName0 NOT LIKE '%service pack%'\nAND DisplayName0 NOT LIKE '%plugin%'\nAND DisplayName0 NOT LIKE '%patch%'\nAND DisplayName0 NOT LIKE '%sp1%'\nAND DisplayName0 NOT LIKE '%sp2%'\nAND DisplayName0 NOT LIKE '%sp3%'\nAND DisplayName0 NOT LIKE '%sp4%'\nAND DisplayName0 IS NOT NULL\nAND " + current.last_run_database_field + " >= " + current.last_run_datetime;

I added a function to the onStart script in the transform map to update the 'Last run datetime' field in the Data Source since this will not be system updated with 'Use last run datetime' un-checked:

function updateLastRunDatetime() {

      var today = new GlideDateTime();

      var ds = new GlideRecord("sys_data_source");

      if (ds.get("import_set_table_name", "imp_sccm2012_software")){

              ds.last_run_datetime = today;

              ds.update();

      } else {

              gs.log('Could not find "SCCM 2012 Software" data source to update last_run_datetime', 'SCCM Import');

      }

}

I changed the condition on the Business Rule 'Format last run datetime' to run on this Data Source:

(current.use_last_run_datetime == true || current.import_set_table_name == 'imp_sccm2012_software') && !current.last_run_datetime.nil()

This works very well so far through testing.

[Brian Arndt1]

I ended up abandoning the idea of specifying a query. Instead I'm now importing all records and running my filter in an onbefore script. This accomplished my requirements in a very clean and simple manner.

[garyopela]

I am using a where clause and it works fine. Where it breaks is if I add an ORDER BY. I'm guessing they've updated it to be a bit smarter when appending the lastrundatetime check to the end, but it doesn't quite work if you use ORDER BY because you can no longer have anymore where pieces after that, or course.

[ayush_saxena]

Hi,

I am still struggling to use Last run database field while creating JDBC. Can you advise which field should I populate from Oracle database so that SN filters out the records which were identified during last import. I am using select * from table name only as query.