Some SQL Server network properties
DECLARE @Stage TABLE
(
RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
Data VARCHAR(90),
Section INT
)
INSERT @Stage
(
Data
)
EXEC xp_cmdshell 'ipconfig /all'
DECLARE @Section INT
SET @Section = 0
UPDATE @Stage
SET @Section = Section = CASE
WHEN ASCII(LEFT(Data, 1)) > 32 THEN @Section + 1
ELSE @Section
END
SELECT MAX(CASE WHEN x.minRowID IS NULL THEN NULL ELSE s.Data END) AS Header,
MAX(CASE WHEN s.Data LIKE '%Host Name%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS HostName,
MAX(CASE WHEN s.Data LIKE '%Media State%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS MediaState,
MAX(CASE WHEN s.Data LIKE '%Description%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS [Description],
MAX(CASE WHEN s.Data LIKE '%IP Address%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS IPaddress,
MAX(CASE WHEN s.Data LIKE '%Physical Address%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS PhysicalAddress
FROM @Stage AS s
LEFT JOIN (
SELECT MIN(RowID) AS minRowID
FROM @Stage
GROUP BY Section
) AS x ON x.minRowID = s.RowID
WHERE s.Section > 0
GROUP BY s.Section
Legacy Comments
|
maja
2008-06-26 |
re: Some SQL Server network properties only if xp_cmdshell is enabled... |