CSV query not longer working, Message=Failed to start database.
You may see following error in localhost file:
13 Nov 2012 12:39:53,433- Thread: 40 INFO [com.vontu.lookup.csv.DerbyConnector] creating database jdbc:derby:CSV_LOOKUP_DATABASE; create=true using derby jdbc dbName: "jdbc:derby:CSV_LOOKUP_DATABASE; create=true"
13 Nov 2012 12:39:58,081- Thread: 40 SEVERE [com.vontu.lookup.csv.DerbyConnector] Message=Failed to start database 'CSV_LOOKUP_DATABASE', see the next exception for details. SQLState=XJ040 SQLError=40000
13 Nov 2012 12:39:58,083- Thread: 40 INFO [com.vontu.lookup.csv.DerbyConnector] creating database jdbc:derby:CSV_LOOKUP_DATABASE_1; create=true using derby jdbc dbName: "jdbc:derby:CSV_LOOKUP_DATABASE_1; create=true"
It turns out in 11.1.1 we were using a simple text based parser and since 11.6 we have changed the implementation of the CSV parsing to make use of a DERBY database and a new line is used as a row separator.
So customers may need to review their csv source file and also consider the use of the pipe as delimiter.
We also recommend in our Admin guide the following requirements for creating a CSV file:
- The CSV Lookup Plug-In requires a CSV file that is stored on the Enforce Server.
- The first data row of the CSV file must contain column headers.
- Column header fields cannot be blank.- Make sure that there are no white spaces at the end of the column header fields.
- Make sure that all rows have the same number of columns.- Each row of the file must be on a single, non-breaking line.
- One or more columns in the file are used as key-fields for data lookups. You specify in the attribute mapping which column heads are to be used as keyfields. You also specify the key field search order. Common key fields typically include email address, Domain\UserName (for Endpoint incidents), and username (for Storage incidents).
- The data values in the key field columns must be unique. If multiple columns are used as key fields (for example, EMP_EMAIL and USER_NAME), the combinationof values in each row must be unique.
- Fields in data rows (other than the column header row) can be empty, but at least one key field in each row should contain data.
- The same type of delimiter must be used for all values in the column header and data rows.
- If the CSV file is read-only, make sure that the CSV file has a new line at the end of the file. The system will attempt to add a new line to the file on executionof the plug-in, but if the file is read-only the system cannot do this and the plug-in will not load.
- For Discover scan incidents, the file-owner lookup parameter does not include a domain. To use file-owner as the key, the CSV file column that correspondsto file-owner should be in the format owner. The format DOMAIN\owner does not result in a successful lookup. This restriction only applies to Discoverincidents, other kinds of incidents can include a domain.For example, the column-header row and a data-row of a pipe-delimited CSV file might look like:email|first_name|last_name|domain_user_name|user_name|department|manager|[email protected]|John|Smith|CORP\jsmith1|jsmith1|Accounting|Mei Wong|[email protected]
- If more than 10% of the rows in the CSV file violate any of these requirements, the Plugin does not load.- For accuracy in the lookup, the CSV file needs to be kept up to date