Determining if a discovered resource is in Bounds and steps to make that eaiser.
This article details the methods necessary to make roaming boundaries readable from the SMS table by creating a View, a function to convert an IP address to Decimal, and a code snippet to identify machines boundaries based machine name from SMS.
Below is an easy way to read Site Boundaries; SQL View Definition:
USE [ENTER DATABASE NAME HERE]
GO
/****** Object: View [dbo].[v_SiteRoamingBoundary_IPRange_Readable] Script Date: 01/02/2008 11:56:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[v_SiteRoamingBoundary_IPRange_Readable]
AS
SELECT SiteCode, CONVERT(VARCHAR,
CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(4), CAST(StartingIP AS BIGINT)), 1, 1)))
+ '.' +
CONVERT(VARCHAR, CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(4), CAST(StartingIP AS BIGINT)), 2, 1)))
+ '.' +
CONVERT(VARCHAR, CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(4), CAST(StartingIP AS BIGINT)), 3, 1)))
+ '.' +
CONVERT(VARCHAR, CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(4), CAST(StartingIP AS BIGINT)), 4, 1))) AS StartingIP,
CONVERT(VARCHAR, CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(4), CAST(EndingIP AS BIGINT)), 1, 1)))
+ '.' +
CONVERT(VARCHAR, CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(4), CAST(EndingIP AS BIGINT)), 2, 1)))
+ '.' +
CONVERT(VARCHAR, CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(4), CAST(EndingIP AS BIGINT)), 3, 1)))
+ '.' +
CONVERT(VARCHAR, CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(4), CAST(EndingIP AS BIGINT)), 4, 1))) AS EndingIP, DownloadExecute
FROM dbo.v_SiteRoamingBoundary_IPRange
Below is an easy function to convert IP to Decimal. Some Boundaries in SMS are stored as Decimal
Private Function ConvertIPtoDecimal(oct1 As Integer, oct2 As Integer, oct3 As Integer, oct4 As Integer) As Variant
ConvertIPtoDecimal = (oct1 * (256 ^ 3)) + (oct2 * (256 ^ 2)) + (oct3 * 256) + oct4
End Function
Below is a code snippet to determine if a given workstation name is either Site Boundaries or Roaming site boundaries. (make sure you modify the SQL Query)
Function InBounds(IPAddress As String, wksname As String) As String
Set cn = New Connection
Set cmd = New ADODB.Command
'HARD CODED SERVER
ConnectReadOnlyString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=" & <SQLReaderIDHERE> & _
";Password=" & <PwdHERE> & ";Initial Catalog=SMS_" & <ServerDBHERE> & ";Data Source=" & <ServerHERE>
cn.ConnectionString = ConnectReadOnlyString
cn.Open
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.Prepared = True
'Site Code Query
strsql = " SELECT dbo.v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 AS SiteCode " & _
" FROM dbo.System_DISC INNER JOIN dbo.v_RA_System_SMSAssignedSites ON” & _
“ dbo.v_RA_System_SMSAssignedSites.ResourceID = dbo.System_DISC.MachineID " & _
" WHERE (dbo.System_DISC.Name0 LIKE '" & wksname & "') "
Set rs = CreateObject("ADODB.Recordset") 'Create the recordset object
rs.CursorType = 0 'adOpenForwardOnly
cmd.CommandText = strsql
Set rs = cmd.Execute
Do While Not rs.EOF
Debug.Print rs(0).Value
rs.MoveNext
Loop
If IPAddress <> "" Then
'Take full address and split it up
splitipaddress = Split(IPAddress, ".")
'Take first 3 octets
PartialIPAddress = splitipaddress(0) & "." & splitipaddress(1) & "." & splitipaddress(2) & "."
'Full Boundary Query
strsql = " SELECT SiteCode, IPSubnet FROM dbo.v_SiteBoundary_IPSubnet WHERE (IPSubnet LIKE '" & PartialIPAddress & "%') "
Set rs = CreateObject("ADODB.Recordset") 'Create the recordset object
rs.CursorType = 0 'adOpenForwardOnly
cmd.CommandText = strsql
Set rs = cmd.Execute
Do While Not rs.EOF
SplitIPSubnet = Split(rs(1).Value, ".")
'check to see if value returned
If rs(0).Value <> "" And CDec(splitipaddress(3)) > CDec(SplitIPSubnet(3)) Then
InBounds = rs(0).Value
End If
rs.MoveNext
Loop
'Check partial bounds
'check roaming boundry
If Len(InBounds) < 1 Then
strsql = " SELECT SiteCode, StartingIP, EndingIP FROM dbo.v_SiteRoamingBoundary_IPRange_Readable WHERE “ & _
(StartingIP LIKE '" & PartialIPAddress & "%') "
cmd.CommandText = strsql
Set rs = cmd.Execute
Do While Not rs.EOF
'split apart starting and ending points
SplitStartingIP = Split(rs(1).Value, ".")
SplitEndingIP = Split(rs(2).Value, ".")
If (rs(0).Value <> "") And (CDec(splitipaddress(3)) > CDec(SplitStartingIP(3))) And (CDec(splitipaddress(3)) < CDec(SplitEndingIP(3))) Then
InBounds = rs(0).Value
End If
rs.MoveNext
Loop
End If
If Len(InBounds) < 1 Then
InBounds = "Out Of Bounds"
End If
Wscript.Echo InBounds
Else
Wscript.Echo "???"
End If
cn.Close
Set cn = Nothing
Set cmd = Nothing
End Function
Note: You could easily modify the above function to only need a workstation name.
Using a query like (thanks Michael P Handley):
SELECT dbo.System_DISC.Name0, dbo.System_IP_Address_ARR.IP_Addresses0
FROM dbo.System_DISC INNER JOIN
dbo.System_IP_Address_ARR ON dbo.System_DISC.ItemKey = dbo.System_IP_Address_ARR.ItemKey
WHERE (dbo.System_DISC.SMS_Unique_Identifier0 IS NULL)