I am new to QuickSight. We have Vulnerability Management data in a one to many (several relational tables) SQL Server database. Each table has been brought over as a Dataset to be used in Analyses. Hosts table has the “one” data related to a scanned asset. Vulnerabilities (one of the “many” tables has a foreign key relationship to the Host and holds details about the vulnerabilities flagged on the host. BusinessApps is another “many” table that also has a relationship back to Host and holds details about the one to many business applications that are installed and managed on that host. I have not found the right Q&A or Documentation that describes how I might put a detailed list (Table visual) item onto an Analysis that is essentially an “inner join” of Hosts and Vulnerabilities (only want one instance of each finding on each host) … then filter Business Apps for a given application, have it give a list of host IDs then use that to filter the tabular visual to show only the vulnerabilities associated with all hosts tied to the Application. Is this even possible across datasets, and is there a more simplified “cookbook” styled documentation that explains the steps and has images of what the components look like to accomplish if it it possible?
Hi @jnkline,
If you have separate datasets for Vulnerabilities, Hosts and BusinessApps, you’ll have to join them to create a new child dataset.
You can then create your analysis using that child dataset.
Thanks, David - however, with a one to many relationship, doing that absolutely violates the intended need to have a table visual show only one record per host/vulnerability. In one example, we have 79 records of the Business App dataset with the reference back to a single host. SIGH. As an alternative, if I have to “join” datasets into a new dataset for filtering, is there an equivalent concept of a select distinct (perhaps by not showing the repetitive field that is being filtered on)??
Even though you have 79 records for the same combination of Business App and Host, depending on the columns you add to your table visual, you won’t see all 79. If you just add Business App and Host to the “Group by” field well, you should only see 1 record for that BusinessApp/Host pair. As you add more columns to the table visual, you may start seeing duplicates. The way that you handle those duplicates depends on what other columns you add. For example, if you have measures in your table visual, you can use aggregations to group the records.
hi @jnkline,
Did Davids suggestion work for you? If you do combine your datasets into one, it should work. Here’s a sample showing 3 separate tables joined into 1. Each table has a Host ID column that the join conditions use. The attached .gif also shows a working sample using the 1 consolidate dataset. Let us know if that works.
Working sample
Thanks Asem, for the example and screen view of how the table looks if I keep the filter field OFF that display - this makes sense to me in that world. I have not been able to drive to closure on my end if this will work for me, in that I don’t yet know for sure what the using community will want when they don’t filter for a value (historically they have been wanting the filter data on the table so they see it all when not filtered down to only one of them - or need to see which if they filter on a few). Meanwhile, as I learn more about QuickSight, we created a View in SQL, used that to synchronize over to a different SPICE dataset to get the initial report out the door per our deployment schedule. Getting a couple more things added for this initial dive into the solution, then I will be revisiting the whole relational, one-to-many data structure and the proper dataset(s) required to then utilize what you and David have shared with me here. Very much appreciate the examples and recommendations.
Sounds good. A bit more info for you- You can get similar functionality if you wanted to keep your tables separate and not create a larger consolidated table. As long as the Host info is in both tables (Bus Apps and Vulnerabilities), you can use parameters and filters to link visuals that have data from different tables. Include both datasets in your analysis and buidl the visuals using each.
When you click on a row in the Bus Apps visual, store the value of the Host for that row in a parameter. Then create a filter of type Custom Filter on Host using this parameter and set if to filter the Vulnerability visual.