Example: configuring geographical discovery by using location data from a file

One way to enrich devices with geographical data is by importing location data from a comma-separated values (.csv) file into the NCIM topology database.

1. Create a .csv file that contains location information for the devices you want to view in geographical views. For example, you can export the data from a database to file.

   The location information must be in the following format:

   ip,address,city,state,country,latitude,longitude

   The following example shows the top two lines of a .csv file.

   IP, ADDRESS, CITY, STATE, COUNTRY, LATITUDE, LONGITUDE
   192.168.0.1,"113620 Redwood Gulch Rd, Cupertino, CA 95014, USA",PLAL,CA,US,37.15458,-122.05561

   The sample ACMEDeviceLocationEnrich.stch stitcher expects the .csv file to be in this format. If you want to use a different format, you must modify the stitcher.

2. Create a database table in the NCIM topology database to store geographical data.

   The sample ACMEDeviceLocationEnrich.stch stitcher expects this table to be called ACMEGeoLocation. If you want to use a different table name, you must modify the stitcher.

   For Db2, the database table must contain fields of the following types and specifications:

   IP VARCHAR(255) NOT NULL,
   ADDRESS VARCHAR(255),
   CITY VARCHAR(255),
   STATE VARCHAR(255),
   COUNTRY VARCHAR(255),
   LATITUDE DECIMAL(10 , 8) NOT NULL DEFAULT 0,
   LONGITUDE DECIMAL(11 , 8) NOT NULL DEFAULT 0

   For Oracle, the database table must contain fields of the following types and specifications:

   IP VARCHAR(255) NOT NULL, 
   ADDRESS VARCHAR(255), 
   CITY VARCHAR(255), 
   STATE VARCHAR(255), 
   COUNTRY VARCHAR(255), 
   LATITUDE NUMBER(15,8) DEFAULT 0  NOT NULL,  
   LONGITUDE  NUMBER(15,8) DEFAULT 0  NOT NULL

3. Import the geographical data from the .csv file into the database table you created using your choice of database tool.

   For example, to load a file core_lat_long_all.csv into a table ACMELOOKUPGEOLOCATION in an NCIM database on Oracle by using Oracle loader V12.1, run the following command from the Oracle directory.

   sqlldr SYSTEM/PASSWORD@mySchema control=/opt/oracle/load.ctl

   Where the load.ctl file contains the following code.

   LOAD DATA
       infile 'core_lat_long_all.csv'
       REPLACE
       INTO TABLE NCIM.ACMELOOKUPGEOLOCATION
       fields terminated by ',' optionally enclosed by '"'
       (
    IP,
    ADDRESS,
    CITY,
    STATE,
    COUNTRY,
    LATITUDE,
    LONGITUDE
       )

   The commands for other versions or other tools are different.

4. Back up and edit the following stitcher: $NCHOME/precision/disco/stitchers/DNCIM/InferDNCIMObjects.stch.
5. Uncomment this line:

   ExecuteStitcher('ACMEDeviceLocationEnrich', domainId, isRediscovery, dynamicDiscoNode );

6. Run a discovery.