Extracted Information
Data is extracted into CSV files with ‘I’ (pipe) character used as field separator instead of comma. String values are enclosed in double quotes. Date/time values will be represented as UTC dates using 'yyyy-mm-dd hh24:mi:ss' format.
The first line in each extract file in the header containing column names.
Names of the generated data files have the following format
<nms instance>.<extract type>.<extract date and time>[.rst].dat[.gz][.gpg]
where
• <nms instance> is NMS instance identifier.
• <extract type> is one of outages, outage_customers, storms, zones, customers.
• <extract date and time> is the timestamp when extract file was generated in format 'yyyymmddhh24miss'.
• For outage extract files, the .rst suffix indicates that the file contains restored outages in addition to the active ones
• Compressed extract files have a .gz extension.
• Encrypted extract files have a .gpg extension.
The script extracts the data from NMS database by executing SQL scripts. Projects can customize these SQL scripts, but the format of the generated data must remain unaltered.
Control Zones
Information about all NMS control zones for the hierarchy type specified in the configuration file.
The extract type for this information is zones.
Name of the SQL extract script is <project>_stormert_zones.sql.
Extract Column
Data Type
Description
ZONE1
VARCHAR2(64)
Level 1 control zone name.
ZONE2
VARCHAR2(64)
Level 2 control zone name.
ZONE3
VARCHAR2(64)
Level 3 control zone name.
ZONE4
VARCHAR2(64)
Level 4 control zone name.
ZONE5
VARCHAR2(64)
Level 5 control zone name.
ZONE6
VARCHAR2(64)
Level 6 control zone name.
ZONE7
VARCHAR2(64)
Level 7 control zone name.
ZONE8
VARCHAR2(64)
Level 8 control zone name.
ZONE9
VARCHAR2(64)
Level 9 control zone name.
ZONE10
VARCHAR2(64)
Level 10 control zone name.
Storms
Information about all completed storms from the NMS Storm Management application.
The extract type for this information is storms.
The SQL extract script is named: <project>_stormert_storms.sql.
Extract Column
Data Type
Description
STORM_NAME
VARCHAR2(65)
Storm name.
STORM_TYPE
VARCHAR2(32)
Storm type.
STORM_LEVEL
VARCHAR2(32)
Storm level/severity.
STORM_START
DATE
Storm start date and time.
STORM_END
DATE
Storm end date and time.
ZONE1
VARCHAR2(64)
Level 1 control zone for the storm.
ZONE2
VARCHAR2(64)
Level 2 control zone for the storm.
ZONE3
VARCHAR2(64)
Level 3 control zone for the storm.
ZONE4
VARCHAR2(64)
Level 4 control zone for the storm.
ZONE5
VARCHAR2(64)
Level 5 control zone for the storm.
ZONE6
VARCHAR2(64)
Level 6 control zone for the storm.
ZONE7
VARCHAR2(64)
Level 7 control zone for the storm.
ZONE8
VARCHAR2(64)
Level 8 control zone for the storm.
ZONE9
VARCHAR2(64)
Level 9 control zone for the storm.
ZONE10
VARCHAR2(64)
Level 10 control zone for the storm.
 
Customer Information
Information about all customers currently active in NMS.
The extract type for this information is customers.
The SQL extract script is named: <project>_stormert_customers.sql.
Extract Column
Data Type
Description
CID
VARCHAR2(64)
Unique customer identifier.
ACCOUNT_TYPE
VARCHAR2(255)
Customer account type.
LATITUDE
NUMBER
Latitude of the customer device location.
LONGITUDE
NUMBER
Longitude of the customer device location.
ADDRESS
VARCHAR2(200)
Customer address.
CITY_STATE
VARCHAR2(50)
City, state part of the customer address.
ZIP_CODE
VARCHAR2(10)
Zip/postal code part of the customer address.
DEVICE_CLS
NUMBER
Class portion of the customer device handle.
DEVICE_IDX
NUMBER
Index portion of the customer device handle.
DEVICE_NAME
VARCHAR2(64)
Customer device name.
METER_NUMBER
VARCHAR2(20)
Customer meter number.
FEEDER
VARCHAR2(64)
Customer feeder name/id.
RPT_LEVEL_1
VARCHAR2
Reporting level 1.
RPT_LEVEL_1_TYPE
VARCHAR2
Label for reporting level 1.
RPT_LEVEL_2
VARCHAR2
Reporting level 2.
RPT_LEVEL_2_TYPE
VARCHAR2
Label for reporting level 2.
RPT_LEVEL_3
VARCHAR2
Reporting level 3.
RPT_LEVEL_3_TYPE
VARCHAR2
Label for reporting level 3.
RPT_LEVEL_5
VARCHAR2
Reporting level 4.
RPT_LEVEL_4_TYPE
VARCHAR2
Label for reporting level 4.
RPT_LEVEL_5
VARCHAR2
Reporting level 5.
RPT_LEVEL_5_TYPE
VARCHAR2
Label for reporting level 5.
 
Outage Information
Outage information is extracted into two files. The first file contains information about outage steps; the second file contains lists of affected customers for each outage step present in the first file.
The SQL extract script is named: <project>_stormert_outages.sql.
Note: This script extracts the information about outages and affected customers, which is then split into separate extract files described below.
Outages
List of outage steps.
The extract type for this information is outages.
Extract Column
Data Type
Description
OUTAGE_ID
NUMBER
Outage identifier.
Outage step is a set of customers affected by the outage that have identical outage start and restoration times.
STEP_ID
NUMBER
Outage step identifier (starts at 1).
OUTAGE_TIME
DATE
Outage step start time.
RESTORE_TIME
DATE
Outage step restoration time.
NUM_CUST_OUT
NUMBER
Number of affected customers
DEVICE_TYPE
VARCHAR2(32)
Outage device type (device class name).
LATITUDE
NUMBER
Latitude of the outage device location.
LONGITUDE
NUMBER
Longitude of the outage device location.
CREWS
VARCHAR2(4000)
Comma-separated list of crews that worked on this outage.
ENROUTE_TIME
DATE
Time when the first crew was placed en-route to this outage.
ONSITE_TIME
DATE
Time when the first crew arrived onsite for this outage.
ZONE1
VARCHAR2(64)
Level 1 control zone for the outage.
ZONE2
VARCHAR2(64)
Level 2 control zone for the outage.
ZONE3
VARCHAR2(64)
Level 3 control zone for the outage.
ZONE4
VARCHAR2(64)
Level 4 control zone for the outage.
ZONE5
VARCHAR2(64)
Level 5 control zone for the outage.
ZONE6
VARCHAR2(64)
Level 6 control zone for the outage.
ZONE7
VARCHAR2(64)
Level 7 control zone for the outage.
ZONE8
VARCHAR2(64)
Level 8 control zone for the outage.
ZONE9
VARCHAR2(64)
Level 9 control zone for the outage.
ZONE10
VARCHAR2(64)
Level 10 control zone for the outage.
 
Outage Customers
List of customers associated with a row in the outages extract file.
Extract type for this type of information is ‘outage_customers’.
Extract Column
Data Type
Description
CID
VARCHAR2(64)
Unique customer identifier.
OUTAGE_ID
NUMBER
Outage identifier.
STEP_ID
NUMBER
Outage step identifier.