Knowledge Base

Ask A Question

Questions

3

SQL Query failing, Error column does not exist

I am trying to build a basic automated report for server review self service with the following query. Borrowing from another post and adding my own SQL on top, my error states: Error:column css.credential_status_description does not exist Character:946 I only get an error when adding in the SELECT for creds_status. All documentation web and PDF states that column does indeed exist. I have returned results for that column in a straightforward query. Help. My Query is: WITH all_tags AS ( SELECT asset_id, tag_name AS all_tags FROM dim_tag JOIN dim_tag_asset dta USING (tag_id) GROUP BY asset_id, tag_name ), policy_set AS ( SELECT asset_id, description AS policy_set FROM fact_asset_policy_rule JOIN dim_policy_result_status dprs USING (status_id) GROUP BY asset_id, description ), policy_rules AS ( SELECT asset_id, title AS policy_rules FROM fact_asset_policy_rule JOIN dim_policy_rule USING (rule_id) GROUP BY asset_id, title ), creds_status AS ( SELECT asset_id, credential_status_description AS creds_status FROM dim_asset_service_credential JOIN dim_credential_status USING (credential_status_id) GROUP BY asset_id, credential_status_description ) SELECT ip_address as "IP Address", host_name as "Hostname", dos.description AS "OS", alt.all_tags as "Tags", ps.policy_set as "Policy Status", pr.policy_rules as "Policy Rule", css.credential_status_description as "Credential Status" FROM dim_asset JOIN dim_operating_system dos USING (operating_system_id) LEFT OUTER JOIN all_tags alt USING (asset_id) LEFT OUTER JOIN policy_set ps USING (asset_id) LEFT OUTER JOIN policy_rules pr USING (asset_id) LEFT OUTER JOIN creds_status css USING (asset_id) Matt

Posted by Matthew Swenk 6 months ago

0

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 6 months ago