AlertHistoryView returns different results to the same query when using SWIS OrionReporting.ExecuteSQL versus via SQL Studio

Running this either via SWIS call or Database Manager:

SELECT [TimeStamp], [EventTypeWord], [Name], [ObjectType], [Message], [EntityCaption], [Severity]
FROM [dbo].[AlertHistoryView]
WHERE [TimeStamp] >= '01/08/2021' AND [TimeStamp] <= '02/08/2021'
ORDER by [TimeStamp] DESC</pre>returns:04/02/2021 13:16	Acknowledged	BCM - CPU Utilisation	LA	2	Node	******
04/02/2021 13:05	ActionSucceeded	BCM - CPU Utilisation	 Node Event (File)	2	Node	*****
04/02/2021 13:05	ActionSucceeded	BCM - CPU Utilisation	NetPerfMon Event Log - CPU Utilisation	2	Node	******
04/02/2021 13:05	ActionSucceeded	BCM - CPU Utilisation	Email - CPU Utilisation	2	Node	*****
04/02/2021 13:04	Triggered	BCM - CPU Utilisation	CPU load is 92 % on node ******	2	Node	*****

however running the same query directly on the sql server in sql studio 104 results a sample of which are below, but none of which are the entries returned above (which incidentally fall out of the date time range specified):

TimeStamp	EventTypeWord	Name	ObjectType	Message	EntityCaption	Severity
2021-08-01 23:02:31.480	ActionSucceeded	***** - CPU Utilisation	Node	Node Event (File) Cleared	******	1
2021-08-01 23:02:31.277	ActionSucceeded	***** - CPU Utilisation	Node	Email - CPU Utilisation - Cleared	******	1
2021-08-01 23:02:30.840	ActionSucceeded	***** - CPU Utilisation	Node	NetPerfMon Event Log - CPU Utilisation - Cleared	*******	1
2021-08-01 23:02:30.510	Reset	***** - CPU Utilisation	Node	NULL	*******	1

I can't for the life of me understand why this is.

context should it be required is that I am attempting to automate daily checks that are performed whereby someone manually eyeballs for critical alerts out of hours to ensure they were all picked up and raised in a ticketing system.

I already have a SWIS call to OrionReporting.ExecuteSQL with a far more complex (for me) SQL statement that works without issue, so this being odd for such a simple request is frustrating to say the least.

any help / guidance would be greatly appreciated.

Find more posts tagged with

swis

sql

swis sdk

sql troubleshooting

Alerts

Accepted answers

KMSigma.SWI

I'm reading this as "show me entries with dates (timestamps) between 01-AUG-2021 and 02-AUG-2021."

Is that correct? Because I'm not 100% sure that the SDK honors MM/dd/yyyy formatting. I've always used yyyy-MM-dd when I define entries. This is a legacy thing for me because I know that's how SQL does it, so I just do it now out of habit.

All comments

KMSigma.SWI

I'm reading this as "show me entries with dates (timestamps) between 01-AUG-2021 and 02-AUG-2021."

KMSigma.SWI

For example, this works fine for me in direct SQL

SELECT [TimeStamp]
     , [EventTypeWord]
	 , [Name]
	 , [ObjectType]
	 , [Message]
	 , [EntityCaption]
	 , [Severity]
FROM [dbo].[AlertHistoryView]
WHERE [TimeStamp] BETWEEN '2021-08-02' AND '2021-08-03'
ORDER by [TimeStamp] DESC

(I moved the dates because I don't have data for Aug 1.

CalasTyph0n

Hi,

you are correct in your assumption

l’ll give it a try tomorrow.

im based in the UK so write my dates as dd/MM/yyyy, inspecting the data in the table pre query has it in the above format so I just assumed that should work given the locale settings and the fact it is fine when run from sql studio 2018.

thanks for replying.

CalasTyph0n

yep, this was exactly the issue.

thank you very much.