SCCM Integration Data Source LastHWScan

stevencerbie
Tera Contributor

‎02-25-2019 04:23 AM

We are using Microsoft SCCM 2012 integration and appear to be seeing some oddities in LastHWScan dates that is used to drive our SCCM extracts.

Our data source SCCM 2012 Computer Identity is using Last Run Datetime and Last run database field LastHWScan. The LastHWScan that drives our extracts is based of the maximum LastHWScan of the previous run.

i.e in the above example we ran our extracts at 02/24/2019 10:00 pm but we are seeing LastHWScan values of imported rows up to 12 hours in the future, i.e. 02/25/2019 10:27:42 am. According to our team that supports our SCCM product the LastHWScan in SCCM is actually in the local time of the system. The issue is that the LastHWScan for the subsequent run is being set to the max LastHWScan of the previous run and I believe we are dropping some due to time zone differences.

Has anyone else seen this behavior and if so how did you get around this.

 

Labels:
0 Helpfuls
  • 2,686 Views
7 REPLIES 7

MGanon
Tera Guru
In response to Robert McDonne1

‎09-21-2020 08:01 AM

I suspect that Nick Fajardo's solution is better than the workaround that we implemented. The workaround was a business rule to reset the integration LastHWScan date to match the date that the integration is run. Not perfect but better than missing 99% of computers.

0 Helpfuls

Nick Fajardo
Tera Expert

‎03-15-2021 07:39 AM

Ah ha! After tinkering on other things for a long while, we another solution.

Changing 

v_GS_WORKSTATION_STATUS.LastHWScan,

in the query to 

DATEADD(minute, ( v_GS_COMPUTER_SYSTEM.CurrentTimeZone0 * -1), v_GS_WORKSTATION_STATUS.LastHWScan ) AS LastHWScan,

This changes the date to UTC based on the timezone reported by the computer, and even takes into account the DST modifier.

Now, the downside is that whenever the "Last Run Datetime" is provided with the appropriate UTC value, the SQL server interprets the date using the timezone of the server, not UTC.

So for us, there's still some overlap since our server is at UTC-6, but it's a lot more manageable, and flexible if our job fails for some reason.

 

A note - if you want to import the fixed value into your CMDB table somewhere, make sure you are converting it back to your SCCM import user's timezone on entry

answer = (function transformEntry(source) {
 return (new GlideDateTime(source.getValue('u_lasthwscan')).getDisplayValue());
})(source);
0 Helpfuls

AndyLock
Mega Guru

‎02-17-2022 02:12 AM

SCCM LastHWScan is in the timezone of the local client, but there is a timezone offset field (in minutes) you can use.

I've not tried it, but I'd be inclined to create a version of v_GS_WORKSTATION_STATUS in a schema that only the ServiceNow integration user can see, e.g. sn.v_GS_WORKSTATION_STATUS.

Don't mess with the original one in case it's used elsewhere - unless all your other reports/processes would benefit. We can then change the field LastHWScan to DATEADD the TZ offset, e.g.  DATEADD(mi,-timezoneoffset,LastHWScan) as LastHWScan

You 'could' try editing the ServiceNow SQL (both for the Select and the Last Update field). But if you do this in the SCCM database rather than in the ServiceNow SQL statement, you will have a single place for LastHWScan date and you won't need to worry about field mapping and lastupdate in all of your other queries that use  sn.v_GS_WORKSTATION_STATUS

 

Further to @Nick Fajardo's answer (a nice idea) you could also DATEADD additional minus-minutes in the above SQL to allow for bad client dates, then re-tick the LastUpdate field, e.g. DATEADD(mi,-(timezoneoffset+1440),LastHWScan) as LastHWScan

You will also be able to Untick that checkbox to do a full load when required, rather than mess with the SN SQL

0 Helpfuls