In a very real sense, the SolarWinds Platform API doesn’t care about the data sets sizes when you make a query call. This is understood and is why when using the SWQL Studio tool to generate a query automatically, the TOP keyword is added to restrict the returned data size.
We’ve already covered ways you can restrict the returned data with WHERE clauses and filters on data, but one very specific scenario which is prevalent in monitoring data is based around time intervals.
Due to the long development history of the SolarWinds Platform, dates can be stored using one of two time zones: the time local to the polling engine/database server or stored in UTC time. Newer entities and properties are stored in UTC time, but there are still some legacy fields that are represented in local time. These local time stamps are being converted to UTC times as new updates are released, so it’s important that you test any queries relying upon date/time filters and outputs as you upgrade your system.
The SolarWinds development team knows that this can be a point of contention, so for many entities there in another property – frequently with regard to metric records – ObservationTimestamp. If your queried entity supports this field, it’s highly recommended that you use it as it is always stored and represented as UTC time.
However, human minds haven’t been trained to read and interpret UTC time as quickly as a local time. To remedy this issue, the developers of the API have added a ToLocal() function for date/time manipulation. We’ll cover this and others in more detail later in this Wiki, but it has some relevance to our current discussions.
Consider the following query:
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].Vendor AS [VendorName]
, [Nodes].LastBoot AS [Last Boot]
, ToLocal([Nodes].LastBoot) AS [Last Boot (Local)]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Vendor IN ( 'Linux', 'Windows' )
ORDER BY [Nodes].LastBoot DESC
This query uses some filters we’ve defined previously but add in the last system boot time as a UTC date and the same date “converted” to the local time.
They key differentiator here is that when the environment from which the query is executed understands times zones (like a web browser), the SolarWinds Platform will automatically convert the date for you to your local time. However, in an environment where the time zone information cannot be acquired (a script against the SolarWinds Information Service or SWQL Studio), they will appear to be two different values – one in UTC time and one in your local time.
We decided to point out the discrepancy in this document because we wanted to be as transparent as possible regarding date/time data within your SolarWinds solution. As stated, newer versions of the platform will automatically understand the request and convert on your behalf, but it’s always best to test these things out for yourself.
If you Imagine you work in Singapore, but your primary engine is in San Diego, CA. Pulling date information and converting it in your mind may not be an ideal situation, so consider leveraging the ToLocal() function when presented with such a scenario.