I am trying to set up a report that queries for data within five minutes of each other. This way, I can recognize that one of my machines is down. This is what I have so far:
我正在尝试设置一个报告,在五分钟内查询数据。这样,我就能认出我的一台机器坏了。这是我到目前为止:
SELECT A_M_DEVICE.M_Device_ID,
A_M_DEVICE.Machine_Name,
A_M_DEVICE.IP_Address,
A_M_DEVICE.Device_Type,
A_M_DEVICE.M_Device_Status_ID,
A_M_DEVICE.Status_Date,
S_M_DEVICE_STATUS.M_Device_Status_Desc,
A_M_DEVICE.Domain_Name FROM MConsole.dbo.A_M_DEVICE A_M_DEVICE
INNER JOIN
MConsole.dbo.S_M_DEVICE_STATUS S_M_DEVICE_STATUS
ON (A_M_DEVICE.M_Device_Status_ID =
S_M_DEVICE_STATUS.M_Device_Status_ID) WHERE (A_M_DEVICE.Machine_Name IN
('DeVA',
'DevB',
))
AND (A_M_DEVICE.Status_Date = DateDiff (Second) <= 300)
Image not allowed since I am a newbie. Else I would have posted one.
由于我是新手,因此不允许使用图像。否则我会张贴一个。
Alright - looks like I have enough reputations for a screenshot! Any help, as always, will be highly appreciated.
好吧 - 看起来我有足够的屏幕截图!任何帮助,一如既往,将受到高度赞赏。
Thank you in advance.
先感谢您。
2 个解决方案
#1
2
Here's an approximation - check for "< 5" vs. "> 5" etc.
这是一个近似值 - 检查“<5”与“> 5”等。
SELECT M_Device_ID,
Machine_Name,
IP_Address,
Device_Type,
M_Device_Status_ID,
M_Device_Status_Desc,
Domain_Name
FROM
( SELECT M_Device_ID,
Machine_Name,
IP_Address,
Device_Type,
M_Device_Status_ID,
M_Device_Status_Desc,
Domain_Name,
MAX(Status_Date)
FROM A_M_DEVICE
GROUP BY
M_Device_ID,
Machine_Name,
IP_Address,
Device_Type,
M_Device_Status_ID,
M_Device_Status_Desc,
Domain_Name
) AS a
JOIN A_M_DEVICE AS b
ON
a.Machine_Name = b.Machine_Name
AND a.IP_Address = b.IP_Address
AND a.Device_Type = b.Device_Type
AND a.M_Device_Status_ID = b.M_Device_Status_ID
AND a.M_Device_Status_Desc = b.M_Device_Status_Desc
AND a.Domain_Name = b.Domain_Name
WHERE
DATEDIFF(minute, a.Status_Date, b.Status_Date) < 5
#2
1
Select the rows that have a status date that doesn't match the MAX status date:
选择状态日期与MAX状态日期不匹配的行:
SELECT A_M_DEVICE.M_Device_ID,
A_M_DEVICE.Machine_Name,
A_M_DEVICE.IP_Address,
A_M_DEVICE.Device_Type,
A_M_DEVICE.M_Device_Status_ID,
A_M_DEVICE.Status_Date,
S_M_DEVICE_STATUS.M_Device_Status_Desc,
A_M_DEVICE.Domain_Name FROM MConsole.dbo.A_M_DEVICE A_M_DEVICE
INNER JOIN
MConsole.dbo.S_M_DEVICE_STATUS S_M_DEVICE_STATUS
ON (A_M_DEVICE.M_Device_Status_ID =
S_M_DEVICE_STATUS.M_Device_Status_ID) WHERE (A_M_DEVICE.Machine_Name IN
('DeVA',
'DevB',
))
AND (A_M_DEVICE.Status_Date NOT IN (SELECT MAX(A_M_DEVICE.Status_Date) FROM A_M_DEVICE)
The date will be identical for all rows that represent machines that are operational - so eliminating those that match the MAX date leave records representing machines that haven't had a status update.
对于表示可运行的计算机的所有行,日期将是相同的 - 因此,消除与MAX日期匹配的行会留下代表尚未进行状态更新的计算机的记录。
#1
2
Here's an approximation - check for "< 5" vs. "> 5" etc.
这是一个近似值 - 检查“<5”与“> 5”等。
SELECT M_Device_ID,
Machine_Name,
IP_Address,
Device_Type,
M_Device_Status_ID,
M_Device_Status_Desc,
Domain_Name
FROM
( SELECT M_Device_ID,
Machine_Name,
IP_Address,
Device_Type,
M_Device_Status_ID,
M_Device_Status_Desc,
Domain_Name,
MAX(Status_Date)
FROM A_M_DEVICE
GROUP BY
M_Device_ID,
Machine_Name,
IP_Address,
Device_Type,
M_Device_Status_ID,
M_Device_Status_Desc,
Domain_Name
) AS a
JOIN A_M_DEVICE AS b
ON
a.Machine_Name = b.Machine_Name
AND a.IP_Address = b.IP_Address
AND a.Device_Type = b.Device_Type
AND a.M_Device_Status_ID = b.M_Device_Status_ID
AND a.M_Device_Status_Desc = b.M_Device_Status_Desc
AND a.Domain_Name = b.Domain_Name
WHERE
DATEDIFF(minute, a.Status_Date, b.Status_Date) < 5
#2
1
Select the rows that have a status date that doesn't match the MAX status date:
选择状态日期与MAX状态日期不匹配的行:
SELECT A_M_DEVICE.M_Device_ID,
A_M_DEVICE.Machine_Name,
A_M_DEVICE.IP_Address,
A_M_DEVICE.Device_Type,
A_M_DEVICE.M_Device_Status_ID,
A_M_DEVICE.Status_Date,
S_M_DEVICE_STATUS.M_Device_Status_Desc,
A_M_DEVICE.Domain_Name FROM MConsole.dbo.A_M_DEVICE A_M_DEVICE
INNER JOIN
MConsole.dbo.S_M_DEVICE_STATUS S_M_DEVICE_STATUS
ON (A_M_DEVICE.M_Device_Status_ID =
S_M_DEVICE_STATUS.M_Device_Status_ID) WHERE (A_M_DEVICE.Machine_Name IN
('DeVA',
'DevB',
))
AND (A_M_DEVICE.Status_Date NOT IN (SELECT MAX(A_M_DEVICE.Status_Date) FROM A_M_DEVICE)
The date will be identical for all rows that represent machines that are operational - so eliminating those that match the MAX date leave records representing machines that haven't had a status update.
对于表示可运行的计算机的所有行,日期将是相同的 - 因此,消除与MAX日期匹配的行会留下代表尚未进行状态更新的计算机的记录。