27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE PROC query_poweroff
AS
SET NOCOUNT ON
declare @sql varchar(500),@hostname nchar(128),@id INT
create table #ip(hostname nchar(16),a varchar(100),hostmachine VARCHAR(50))
declare tb cursor local for select [ip] from [dhcp] WHERE ([hostname] NOT LIKE '4%') AND (hostname NOT LIKE '6%') --这里是我的网络表情况,因为我要监控多台服务器,你可以改成你的表格
open tb
fetch next from tb into @hostname
while @@fetch_status=0
BEGIN
set @sql='ping '+@hostname+' -a -n 1 -l 1 -w 100'
insert #ip(a) exec master..xp_cmdshell @sql
update #ip set hostname=@hostname where hostname is NULL
DELETE FROM #ip WHERE CHARINDEX('request',a)<1 AND CHARINDEX('reply',a)<1 OR a IS NULL
fetch next from tb into @hostname
END
INSERT record (ip,result) SELECT hostname,
CASE WHEN CHARINDEX('request',a)<1 THEN 1 --0为没关机 , 1为关机
ELSE 0
END
FROM #ip
--SELECT * FROM #ip
DROP TABLE #ip
SET NOCOUNT OFF
GO