Strangely when I use this query below, it works perfectly in SWQL Studio, but plugged into an Orion Custom Query box for a dashboard exempts some entries for reasons I can't discern. Anyone able to explain?
Thanks - yes, UTC occurred to me, but I didn't understand that SWIS was adjusting values to UTC even when they were server local to begin with - however, this only makes sense with the SWQL query if 'getdate' is returning local server in server time with no adjustment, leading to my MinuteDiff taking four hours and thirty minutes instead of thirty minutes. I believe I have fixed the query with 'getUTCdate' - however, to account for the differences between the SWQL Studio and the Custom Query, it seems that 'getdate' would have to be pulling as UTC for Studio (resulting in correct results, but +4) but on Orion Custom Query pulling local server time for 'getdate' while still be doing +4 on the values from Orion.CPULoad.DateTime.
If there were no difference between SWQL Studio and the Custom Query, then the results certainly should have matched, regardless of the timezone formatting.
I've had some issues with group by in custom query resources. Try it with out the max and group by in the inner sub select to see if it works. Might need to group by both fields.
Thanks,
Christian
The only issue with that is that the MAX is absolutely required to return the latest DateTime instead of every datetime on every CPU poll. This is a modification on the technique that others use to measure SNMP polling failures by reporting on the last DateTime of a CPU poll and how far it differs from the current time.
This seems to work perfectly in SQL and Alert Manager (with mods for SQL vs SWQL), it seems to work perfectly in SWQL Studio, but then the same query in the Custom Query in the Orion dashboard just exempts certain entries - but the query works.
I've got three installs of SAM and can confirm that its doing the same on at least two of them - the third may just not have an example to point to.
Grouping by both fields seems to fail, to remove the group is to remove the MAX, which is to return far to many results.
I pulled it apart and put it back together, but again, it's leaving out an entry:
there is a distinct time difference between the database values when querying through SQL or SWQL. These are from the same server desktop session - what would account for a four hour difference in the times returned by the queries in the screencaps below?
This difference is due to time zone translation. SWIS attempts to present a consistent UTC format for all datetime values going in or out, even though some columns in the Orion database are stored in local time (generally the system time zone for the polling engine that stored them) and some are stored in UTC. The CPULoad.DateTime column is stored in local time. You must be on the US East Coast, so SWIS is adding four hours to convert to UTC before returning those values.
There's still something odd going on with the UTC timing - this alert showed itself as ahead of UTC somehow.
