How can I use a data import rule to map IP addresses, but not the entire address, only part of it.

Version 1

    Question:


    By default Data Translation Services installs an  Import Data  rule called  IP Gateway to Address .  This rules looks at a table in the same database as Management Suite and has a series of columns that allow you to map an ip gateway to a physical location.  In the Managed Planet program group is a link to a web page called  Map IP Gateways To Locations .  At install time all gateway addresses are added to the table and you can manually add additional ones later.  However, say instead of using a specific gateway address, I want to use just part of the address, say the first three octets.  So instead of mapping to 10.10.10.1, I want to map to 10.10.10.  How can I modify this rule so the import will only look at the first three octets?  

     

     

    Answer:


    This can be done in the  Where Statement  of the rule.  Edit the rule and go the Where Statement.  Put in the following text (if you are using Microsoft SQL Server):  LEFT(IPGateway, LEN(IPGateway) - CHARINDEX('.', REVERSE(IPGateway))) = LEFT(!Computer.Network.TCPIP.Default Gateway Address!, LEN(!Computer.Network.TCPIP.Default Gateway Address!) - CHARINDEX('.', REVERSE(!Computer.Network.TCPIP.Default Gateway Address!)))  This will parse the first three octets of the IPGateway column in the MP_GatewayToAddress table, as well as only the first three octets of the Computer.Network.TCPIP.Default Gateway Address.  If you wanted to use Computer.Network.TCPIP.Address instead, simply replace all occurrences in the aboce SQL statement.