Monday, January 24, 2011

SMS Report/Query to find the current IP Address of client machines

SELECT
[ComputerName],
[IP Address],
[Subnet Mask],
[Timestamp]
FROM
(
select
row_number() over (partition by cfg.IPAddress0 order by cfg.Timestamp desc) as rownum,
sys.netbios_name0 AS [ComputerName],
cfg.IPAddress0 AS [IP Address],
cfg.IPSubnet0 AS [Subnet Mask],
cfg.Timestamp AS [Timestamp]
FROM
dbo.v_R_System AS sys
INNER JOIN dbo.v_GS_Network_Adapter_Configur AS cfg
ON sys.resourceID = cfg.resourceID
AND cfg.IPEnabled0 = 1
WHERE
sys.obsolete0 = 0
AND sys.decommissioned0 = 0
AND sys.client0 = 1
AND sys.Obsolete0=0
AND cfg.IPaddress0 NOT LIKE '%,%'
AND cfg.IPAddress0 NOT LIKE '0.0%')as dt
where rownum=1
order by [Timestamp] desc

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home