Insert IP Ranges into iQSonar Database directly.

Owned by Daniel Stuart-Kelly
Last updated: Nov 03, 2017 by Conor McCann
2 min read

If there are a large number of IPRanges to be added to an iQSonar v3 configuration it can be very time consuming to do this via the UI. The following instruction should be used to populate the database directly and SQL scripts are included.

 

Step-by-step guide

  1. Add IPAddresses to the IPRange Table

    If targeting specific IPAddresses, the StartIP and EndIP should be the same, If targeting as IPRanges, the StartIP and EndIP should be the beginning and end IPAddresses of the range.
    Note: If using Locations - a LocationID will need to be set for each row inserted, if Not using Locations, LocationID should be NULL
    Please confirm the Location Name for the IPRanges by checking Locations in your UI, or using select * from Location in your iQSonar Database and use the appropriate LocationID with the script below.

    IPRange Insert
    INSERT INTO IPRange (StartIP, EndIP, IPRangeTypeEnumID, ServiceTypeENumID, LocationID, Description) VALUES 
	('192.168.1.1', '192.168.1.1', 
	(SELECT IPRangeTypeEnumID FROM IPRangeTypeEnum WHERE IPRangeTypeEnum = 'Range'), 
	(SELECT ServiceTypeEnumID FROM ServiceTypeEnum WHERE ServiceType = 'Discovery.Port'),
	(SELECT LocationID from Location where name = 'Location Name'),
	'Description')
  2. Update Map Table.
    After running the above SQL Script row for each IPRange you wish to add the two scripts below will need to be run and these will update the corresponding map tables.

These updates assume only one iQSonar scanning server in use, if you have more than one Scan Engine, the MapIPRange_Server script will need to be modified.

MapIPRangePortlist
INSERT INTO MapIPRangePortList (IPRangeID, PortListID)
SELECT ip.IPRangeID, pl.PortListID FROM IPRange AS ip
INNER JOIN PortList AS pl
ON pl.IsDefault = 1
WHERE IPRangeID NOT IN (SELECT IPRangeID FROM MapIPRangePortList)
MapIPRange_Server
INSERT INTO MapIPRange_Server (_ServerID, IPrangeID)
SELECT 1, IPRangeID FROM IPRange WHERE IPRangeID NOT IN (SELECT IPRangeID FROM MapIPRange_Server)

 

Attached Insert IP Ranges into iQSonar Database directly. can be used to create a bulk insert, below instructions should be used with this document and contain information on how to make changes. The resulting SQL code should be run against the IQData Database.

  1. Populate Column B with the required StartIP
  2. Populate Column D with the required EndIP
  3. IPRange Type is set to Range, if this is needed to be changed replace the word 'range' in Column E with 'subnet'
  4. Service Type is set to Port Discovery, if this is required to be changed replace 'Discovery.Port' in Column E with 'Discovery.ICMP'
  5. Populate Column F with the Location.Name appropriate to the IPRange from the Location table.
  6. Populate Column H with a description for the location, this is a free text field and can contain any string.

Once the above has been completed, copy column K and paste it into Sql Server Management Studion