First of all there the way SCSM handles dates is not really new but something you need to consider and be aware of.
Since this question comes along a lot especially when you start working with reporting I decided to write an article explaining the differences and pitfalls when dealing with SCSM and dates.
Time zone and understanding what is happening
Before you start diving into the databases be aware that the data in the SCSM databases is always UTC!
Data in the SCSM Databases is UTC
Although to an end user he couldn’t care less this is very crucial for troubleshooting and understanding SCSM!
This will become more and more crucial when you start designing reports or even more start using the SDK where you query directly to the databases.
Do not deviate from UTC or your life will become a living hell!
Why?
The reason for adding data into a database in one universal way is you are always sure you are in the “correct” Time zone.
So when a user logs in with the console and creates a request in Europe the user will see the request being created, CreatedDate, in his own time zone but in the background the data is actually translated to UTC and after this written to the Database.
Now when a user from the US opens the same request the data is being pulled from the DB to the console and translated from UTC into the US time zone.
This way you are sure if you start measuring requests for like created to resolved date you are measuring the actual time between createddate and resolveddate.
Running into this issue
Now if you would create your own solution which will automatically add the first response date and you forget to do this in UTC time in the DB. This will not be noticed until you start working with this date.
Let’s say you are creating a SLO based on CreatedDate and First Response date, now CreateDate will be in UTC and First Response date( where you created your custom solution for) will be in your local time zone.
Now depending on what time zone you are in this can lead to never reaching you SLA because your time zone deviate from UTC with a plus like +2 or leaving you at ease because you live in the minus zones like -4 which could even result in first response being prior to the create date! Unless you are wearing a cape with a big S on your shirt try explaining this to your manager
For reference:
| Type | Result in DB | Result in Console |
| CreateDate | 2014-04-10 08:09:50.033 | 2014-04-10 11:09:50.033 |
| First Response | 2014-04-10 11:16:50.033 | 2014-04-10 11:16:50.033 |
Notice the behavior, to the user this all looks OK since he is in the same time zone.
The incident is created at 11:09 and first responded 7 minutes later on 11:16!
A custom PowerShell script took the current time, [DateTime]$Now = get-date, and wrote it to the database. (no translation to UTC).
Now in the database it looks like this:
The incident is created at 08:09 and first responded 3 hours and 7 minutes later on 11:16!!!!
Because when creating SLO it will measure against the dates from the DB imagine how your SLA score will look like.
Because time difference from UTC can be from –12 to + 14 you can imagine how this will affect your measurements.
Running into this issue part 2
Another way even without having any custom solution writing date time to the databases directly would be when you start writing reports.
When you are reporting against the Data Warehouse be sure you are translating your date time data into the time zone of where the report is actually running.
For reference you can check the default reports.
There is “code behind” the reports which will make the translation for you if you are writing a new report based on an already existing one.
 |
 |
| Open the reporting website(default http://localhost/reports) |
Navigate to the incident list report SystemCenter\ServiceManager\ServiceManager.Console.Reporting.IncidentManagement |
 |
 |
| Right Click the incident details report and select edit in report builder. | Now report builder should open and select the cell for the create date as above and right click select expression. |
 |
 |
| Notice the expression is using code behind to do the translation! You can build your custom report based on this version and use the same code behind. |
If you want the details of this code just click on the report itself, right click and select report properties and select the code pane! |
Now when you want to add a column to this report be sure to use the same expression to translate from UTC into the time zone of the report to avoid time translation issues.
If you are building a report from scratch I would recommend to still use the default ones as base and delete all other stuff in the report to re-use the code.
Or if you don’t want to translate from UTC at least let your manager know you are running these reports in UTC!
Lessons learned
Data in SCSM Databases is in UTC
When writing custom solutions for SCSM (or any other product) be sure you are translating the data into UTC in order to avoid “Strange” behavior.
Remember this rule or you will find out the hard way
When reporting you are running into the same challenges always remember data in the DB is in UTC!
Cheers,
Oskar Landman
