Whilst the following is an sp and not a function, its use would be similar, it will return in the following formats:
--Usage:
usp_ping '192.168.49.16'
Status Details
------ --------------------------------------------
online Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Status Details
------ --------------------------------------------
offline Packets: Sent = 1, Received = 0, Lost = 1 (100% loss),
CREATE PROCEDURE dbo.usp_ping @ServerIP VARCHAR(15)
AS
DECLARE @Command VARCHAR(1000)
DECLARE @ResponseLineCount INT
SET NOCOUNT ON
CREATE TABLE #tmpPingResult
(
[Result] [varchar](1000) NULL
)
SELECT @Command = 'ping ' + @ServerIP + ' -n 1'
INSERT INTO #tmpPingResult
( Result )
EXEC Master..xp_cmdShell @Command
UPDATE #tmpPingResult
SET Result = LTRIM(RTRIM(Result))
DELETE FROM #tmpPingResult
WHERE REPLACE(REPLACE([Result], CHAR(10), ''), CHAR(13), '') = ''
OR Result IS NULL
SELECT @ResponseLineCount = COUNT(*)
FROM #tmpPingResult
IF @ResponseLineCount = 4
BEGIN
SELECT 'offline' AS [Status] ,
( SELECT Result
FROM #tmpPingResult
WHERE Result LIKE 'Packets: Sent = %'
) AS Details
END
ELSE
IF @ResponseLineCount = 6
BEGIN
SELECT 'online' AS [Status] ,
( SELECT Result
FROM #tmpPingResult
WHERE Result LIKE 'Packets: Sent = %'
) AS Details
END
--SELECT * FROM #tmpPingResult
DROP TABLE #tmpPingResult
GO
Regards
J.
Edited by admin on 24-08-2010 10:04
Jordon Pilling | Heavencore Administrator
|
