Tuesday, October 1, 2013

SCCM report for Computers in a Specific subnet



Following SCCM report provide Machine name, IP Subnet, IP Address Default gateway

********************************************************************
SELECT Distinct SYS.Name0, IPSUB.IP_Subnets0, NETW.IPSubnet0,
NETW.IPAddress0, NETW.DefaultIPGateway0
FROM  v_RA_System_IPSubnets IPSUB, v_R_System SYS, 
v_Network_DATA_Serialized NETW
WHERE SYS.ResourceID = IPSUB.ResourceID AND
SYS.ResourceID = NETW.ResourceID AND
NETW.IPAddress0 IS NOT NULL AND
IP_Subnets0 LIKE @variable
Order by SYS.Name0

On prompt;
Name: variable
SQL Statement:
begin
 if (@__filterwildcard = '')
   Select Distinct IPSUB.IP_Subnets0 from v_RA_System_IPSubnets IPSUB  order by IPSUB.IP_Subnets0
 else
   Select Distinct IPSUB.IP_Subnets0 from v_RA_System_IPSubnets IPSUB
   WHERE IPSUB.IP_Subnets0 like @__filterwildcard
   order by IPSUB.IP_Subnets0
end
*********************************************************************
More SCCM custom reports can be found here

1 comment:

  1. One of the must have report.
    Thanks for sharing.

    ReplyDelete