Alerts and Events Trigger Rate (minute-by-minute)

Thanks Alex for the inspiration. Will be interesting to see the rest. 

Two things; I tested this on two installations. On one I got the "timing issue", on the other it just worked. Would be interested to hear your workaround.

The other thing, why use SQL and not SWQL? (Except that SQL might be easier to write in )

Nice one, glad it has worked for you. 

So, timing issue ... 

Let me illustrate what I believe is happening... 

First - let's run this exact same query in SQL Management Studio:

Screenshot #01

Now, let's do the same via PREVIEW in actual widget

Screenshot #02

And finally, just note down which exact hour is being displayed in your widget (in my case, now, it shows between 4:30 - 5:30am)

Screenshot #03

Notice there is 6 hour difference in actual time being recording in table and the time in preview window. Time in the first screenshot comes back as SQL system time (which happens to be CT in my case), but time in the actual widget preview comes back 6 hours ahead, which coincidentally happens to be my local time (which is GMT, I am in London). The actual widget UI element (screenshot #03) shows CT time, which is correct and expected.

So, what I have done is simply subtracted those 6 hours in SQL script. To do this you need to modify 2 lines (line #3 and line #7) as per below example. Your goal with this adjustment is to make sure that time which is returned in PREVIEW matches time recorded in database

SELECT TOP(60)
  'Count' AS 'Legend',
  DATEADD(hour,-6,DATEADD(minute, DATEDIFF(minute, 0, e.[EventTime]), 0)) AS 'SAMPLE',
  SUM(CASE WHEN e.EventType = 5000 THEN 1 ELSE 0 END) AS 'Alerts',
  COUNT(*) AS 'Events'
FROM Events e
GROUP BY DATEADD(hour,-6,DATEADD(minute, DATEDIFF(minute, 0, e.[EventTime]), 0))
ORDER BY 'SAMPLE' desc

This is very strange behavior and I hope someone from SolarWinds product team whoever happens to read this can explain this to me - I could never figure it out why it is doing it in such way. It may well be a bug, but I never reported it and just used this workaround instead

One obvious disadvantage is that twice per year this needs to be adjusted.

And, another peculiar fact - even though I have subtracted 6 hours in script, people who access this widget via different time zones with different offset hours (for example India, with almost 12 hour difference) they see data correctly. 

So, God knows what's going on here ... but it works I hope one day someone will shed light on this behavior

---------------

As for the second question - why SQL - I have 2 reason:

1. SQL knowledge is transferable, SWQL is not. I tend to learn a lot whilst tinkling with SolarWinds and I can the re-use this knowledge elsewhere when compiling scripts and working on queries in other systems
2. SQL is way more powerful than SWQL and has full spectrum of functions, access to system databases if needed and even access to other linked SQL servers, which is pretty awesome, as this has virtually no limit to the data I want to capture on screen

There is one drawback however - table structure may change and may invalidate scripts with future releases. But, out of hundreds of scripts that I have running, only few ever got broken. SolarWinds is pretty good in ensuring backwards compatibility, but, obviously this is not something they will support. In light of this - SWQL seems to be better option to ensure your scripts will not break after upgrade.

Thanks,

Agree - time is strange. I get correct time in SSMS, correct time in "test datasource" but the graph shows 2 hours wrong. 

Local time is 2 hours diff against UTC.

Subtracted 2 hours in  query and graph is correct. Feels wrong that Orion mess with my datasource like this... 

SQL vs SWQL - another drawback: SQL does not always work for non-admins and SQL as datasource does not take care of account limitations etc. SWQL might be more "future safe". Otherwise I agree with you. I try to use SWQL first, if I fail I use SQL.

Hmmm, so if we get the time in UTC time in the data source. The widget will get the browsers timezone and adjust the graph accordingly!?!!? In that case we should always have UTC time in graph data.

This fix does not correct my issue. 
I am getting the "Blank Widget" issue you describe above with the two lines on the left side of the widget.

When I check the SQL on the Widget and also run the SQL in SolarWinds to see the different times being output, there is no difference in the time.