There are 0 records found when specifying "month", but specifying timeperiod as day, week, or all works fine and produces thousands of results. I'm guessing the issue is with this being the first month of the year, as this same script worked fine with the "month" timeperiod last year without any changes to Horizon or SQL (Horizon 7.10, SQL Server 2017), and the testing I did below suggests as such.
After looking deeper at the module, I can confirm that the following SQL query is being built:
SELECT UserSID.StrValue AS UserName, Severity, FORMAT(Time, 'MM/dd/yyyy HH:mm:ss.fff') as EventTime, Module, ModuleAndEventText AS Message FROM ROSP_event LEFT OUTER JOIN (SELECT EventID, StrValue FROM ROSP_event_data WITH (NOLOCK) WHERE (Name = 'UserDisplayName')) UserSID ON ROSP_event.EventID = UserSID.EventID WHERE FORMAT(Time, 'MM/dd/yyyy HH:mm:ss.fff') BETWEEN '12/14/2020 11:55:18' AND '01/13/2021 11:55:18' ORDER BY EventTime DESC
The dates look formatted correctly, yet running that query directly against the database in SSMS returns 0 results. However, if I change the first date from 12/14/2020 to, say, 01/01/2021, or anything from the current year, suddenly the query returns results. Any idea why this is?
EDIT: I figured it out. FORMAT(Time, 'MM/dd/yyyy HH:mm:ss.fff') returns an nvarchar string, and the two comparison dates provided are then interpreted as strings as well. So, it is looking for dates that have a month greater than 1 and less than 0, which is impossible. This code should be modified so it isn't converting the value in the Time column to a string, as it is already in DateTime format, like so (casting the comparison dates to DateTime is optional as there is an implicit conversion if it isn't specified, but I like to include it for peace of mind):
SELECT UserSID.StrValue AS UserName, Severity, FORMAT(Time, 'MM/dd/yyyy HH:mm:ss.fff') as EventTime, Module, ModuleAndEventText AS Message FROM ROSP_event LEFT OUTER JOIN (SELECT EventID, StrValue FROM ROSP_event_data WITH (NOLOCK) WHERE (Name = 'UserDisplayName')) UserSID ON ROSP_event.EventID = UserSID.EventID WHERE Time BETWEEN CAST('12/14/2020 11:55:18' As DateTime) AND CAST('01/13/2021 11:55:18' AS DateTime) ORDER BY EventTime DESC
get-hvevent -hvdbserver $eventdb -timeperiod month
There are 0 records found when specifying "month", but specifying timeperiod as day, week, or all works fine and produces thousands of results. I'm guessing the issue is with this being the first month of the year, as this same script worked fine with the "month" timeperiod last year without any changes to Horizon or SQL (Horizon 7.10, SQL Server 2017), and the testing I did below suggests as such.
After looking deeper at the module, I can confirm that the following SQL query is being built:
SELECT UserSID.StrValue AS UserName, Severity, FORMAT(Time, 'MM/dd/yyyy HH:mm:ss.fff') as EventTime, Module, ModuleAndEventText AS Message FROM ROSP_event LEFT OUTER JOIN (SELECT EventID, StrValue FROM ROSP_event_data WITH (NOLOCK) WHERE (Name = 'UserDisplayName')) UserSID ON ROSP_event.EventID = UserSID.EventID WHERE FORMAT(Time, 'MM/dd/yyyy HH:mm:ss.fff') BETWEEN '12/14/2020 11:55:18' AND '01/13/2021 11:55:18' ORDER BY EventTime DESCThe dates look formatted correctly, yet running that query directly against the database in SSMS returns 0 results. However, if I change the first date from 12/14/2020 to, say, 01/01/2021, or anything from the current year, suddenly the query returns results. Any idea why this is?
EDIT: I figured it out. FORMAT(Time, 'MM/dd/yyyy HH:mm:ss.fff') returns an nvarchar string, and the two comparison dates provided are then interpreted as strings as well. So, it is looking for dates that have a month greater than 1 and less than 0, which is impossible. This code should be modified so it isn't converting the value in the Time column to a string, as it is already in DateTime format, like so (casting the comparison dates to DateTime is optional as there is an implicit conversion if it isn't specified, but I like to include it for peace of mind):
SELECT UserSID.StrValue AS UserName, Severity, FORMAT(Time, 'MM/dd/yyyy HH:mm:ss.fff') as EventTime, Module, ModuleAndEventText AS Message FROM ROSP_event LEFT OUTER JOIN (SELECT EventID, StrValue FROM ROSP_event_data WITH (NOLOCK) WHERE (Name = 'UserDisplayName')) UserSID ON ROSP_event.EventID = UserSID.EventID WHERE Time BETWEEN CAST('12/14/2020 11:55:18' As DateTime) AND CAST('01/13/2021 11:55:18' AS DateTime) ORDER BY EventTime DESC