Knowledge Base

Ask A Question

Questions

1

INFORMATIONAL: Exporting data and schema with SSIS/SSDT

Please stick/archive or move to knowledge base for others. Thank you. Rapid7 data warehouse extraction setup • Rapid7 data warehousing export only supports POSTGRES SQL; so install a new instance to use a staging/bucket location: o Download the flavor of POSTGRES you need.  https://www.postgresql.org/download/ o Install an instance calling it whatever you want.  If this is done on the Rapid7 host machine, you need to make sure to adjust the port address as the Rapid7 PG instance is using the default. o Make note of the admin login when you set it up. o After the install is complete, we need to make a new database inside it.  For reference: the DB was labeled as: nxp_bucket  If there is a need for UI tools; please see this link. • https://www.pgadmin.org/download/ • In order to get the data structure and data out of Rapid7, an export must be setup: o Log into Rapid7 and navigate to the “Administration” section. o Look for “Maintenance, Storage and Troubleshooting” section. o Locate the “Data Warehousing” description and click “Manage”. o This will bring you to configuration settings.  Place a check to: “Enable export”  DataModel: Dimensional  Server address: (localhost, 127.0.0.1, or name of the server)  Server port: (whatever port was assigned when the instance was installed)  Database name: nxp_bucket (or whatever it was labeled as)  User & Password  Test the connection before continuing o Next, select the schedule that works best. o Data retention was left blank. o Save your configuration. • To get SSIS/SSDT packages to work (I used SQLServer2016 and MS SSDT), we need a driver and a DSN connection: o Download the latest driver, x64 is recommended.  https://www.postgresql.org/ftp/odbc/versions/msi/ o Run through the install. o Next, open up ODBC Admin.  Start > run  ODBCAD32 o Once the ODBC Admin window is open:  Click the SystemDSN tab  Add new data source  Scroll down and find “PostgreSQL Unicode” and click finish  The next screen is the basic connection setup, all options were left default. Feel free to explore and tweak as needed.  Test the connection before saving o Close the ODBC admin window. • Now a new ODBC connection can be set up inside SSDT: o Open SSDT, setup a new project, and add a new SSIS package. o In the “Connection Managers” section of the package, right-click and add a “New Connection”.  In the list, scroll down to “ODBC” and click add  In the connection manager, click “New”  On the next window, use the drop down and the system DSN that was created earlier should be listed in the drop down.  Use the Postgres one and provide the login info.  Test the connection before clicking OK. o You should now have a new connection to work with in the package. • This setup exports **everything** into our staging/bucket. • Schema documentation can be found here: o https://help.rapid7.com/nexpose/en-us/warehouse/warehouse-schema.html • Using this documentation, code the package to pull as much or as little as needed.

Posted by Mark W 10 months ago