add localtime dan custom properties

rijalhidayat

Hi All,

I need help for my custom swql, I want to add or change from utc to local time (GMT+7) and also do a filter to display certain devices using custom properties (such as DeviceCategory) into swql as below:

SELECT ST.Nodes.Caption AS [Device]
	,'/Orion/images/StatusIcons/Small-' + ST.Nodes.StatusIcon AS [_IconFor_Device]
	,ST.nodes.detailsurl AS [_linkfor_Device]
	,TOLOCAL(ST.EventTime) AS [Down Event]
	,TOLOCAL(ET.EventTime) AS [Up Event]
	,CONCAT (
		--Downtime formatted
		(
			CASE 
				WHEN (SECONDDIFF(ST.EventTime, ISNULL(ET.EventTime, GETUTCDATE())) / 60 / 60 / 24) >= 1
					THEN CONCAT (
							SECONDDIFF(ST.EventTime, ISNULL(ET.EventTime, GETUTCDATE())) / 60 / 60 / 24
							,'d '
							)
				ELSE ''
				END
			)
		,(
			CASE 
				WHEN (SECONDDIFF(ST.EventTime, ISNULL(ET.EventTime, GETUTCDATE())) / 60 / 60) >= 1
					THEN CONCAT (
							SECONDDIFF(ST.EventTime, ISNULL(ET.EventTime, GETUTCDATE())) / 60 / 60 % 24
							,'h '
							)
				ELSE ''
				END
			)
		,(
			CASE 
				WHEN (SECONDDIFF(ST.EventTime, ISNULL(ET.EventTime, GETUTCDATE())) / 60) >= 1
					THEN CONCAT (
							SECONDDIFF(ST.EventTime, ISNULL(ET.EventTime, GETUTCDATE())) / 60 % 60
							,'m '
							)
				ELSE ''
				END
			)
		,(
			CASE 
				WHEN (SECONDDIFF(ST.EventTime, ISNULL(ET.EventTime, GETUTCDATE()))) >= 1
					THEN CONCAT (
							SECONDDIFF(ST.EventTime, ISNULL(ET.EventTime, GETUTCDATE())) % 60
							,'s '
							)
				ELSE ''
				END
			)
		) AS [Downtime]
FROM Orion.Events AS ST
LEFT JOIN Orion.Events AS ET ON ET.NetObjectID = ST.NetObjectID
	AND ET.EventTime = (
		SELECT TOP 1 x.EventTime
		FROM Orion.Events AS [x]
		WHERE x.EventTime >= ST.EventTime
			AND x.EventType = 5
			AND x.NetObjectType = 'N'
			AND x.NetObjectID = ST.NetObjectID
		ORDER BY x.EventTime
		)
WHERE ST.EventType = 1
	AND ST.NetObjectType = 'N'
	AND DAYDIFF(ST.eventtime, GETUTCDATE()) < 1
	AND ST.Nodes.STATUS = 2
ORDER BY [Down Event] DESC