DataWarehouse

Hi All,

So I am trying to join these tables and my SQL query does not seem to work. Was wondering if I could get some help or guidance on making the correct JOIN.

SELECT da.asset_id, da.ip_address, favf.date, favi.proof, dv.title FROM dim_asset da JOIN fact_asset_vulnerability_finding favf USING (asset_id) JOIN fact_asset_vulnerability_instance favi USING (asset_id) JOIN dim_vulnerability dv USING (vulnerability_id)

Thanks in advance,
Jacob

Hi @jacob_horning!

Because vulnerability_id appears in more than one of the tables used, you cannot use the USING keyword for the join. Instead, you will need to use the ON keyword, which has a syntax like this:

JOIN dim_vulnerability dv ON dv.vulnerability_id = favf.vulnerability_id

Hi @tyler_schmidtke

Thank you so much for your help! I had one more question if I wanted to print out dim_tag(name), so all of the tags applied to an asset, and an asset ip_address in the data warehouse. I keep getting an ip_address with only one tag but I would like all of the tags on the same line separated with a ‘|’. A query like this would suffice.

“SELECT DISTINCT da.ip_address, dt.name FROM dim_asset da JOIN dim_asset_tag USING (asset_id) JOIN dim_tag dt USING (tag_id) ORDER BY ip_address”

Thanks again,

Jacob

You can use the string_agg function to facilitate this: https://www.postgresql.org/docs/12/functions-aggregate.html

Hey @jacob_horning,

I have some queries and reports up on my github, feel free to dig through and copy any of the code or use it as reference.

I have reports/queries up for both console and DW

Github Link

1 Like

@matt_wyen Thank you so much for the guidance. Still new building out our data warehouse.

1 Like

Let me know if you have any questions, I’m not fantastic at SQL but I’ve managed to get this far haha