Database view of vulnerable items

[Patrik Z]

Hey everyone,

I have a question about the database view. I need to use the DB view to:

• All the VIs, incl. the manually created ones
• All the Detections paired with the VIs (manually created VIs should have the detection fields empty)

But when I try to set it up the number of records in the DB view increases from 3k to 4k when I add the detection table in left join but I can't see the manually created VIs.

Does anybody have an idea how to join the two tables?

[joe_harvey]

Hey Patrik,

Is there any chance that you forgot to add VI fields to include in the view?  They are defined as a related list in the form where you enter the Table, Prefix, and Where Clause.

--Joe

[Patrik Z]

Hey Joe,

well, I didn't forget but I didn't do it on purpose this time around - it wasn't my first attempt. At this point, I'm trying to join the VI and Detection tables so that I can see all the VI records and I'll be adding the fields later on when I get past this issue.

So this is how it's configured.

The tables are left joined by the VI sys ID and the reference field from the detection table:

To see this working I created a VI:

That I can but still sort of can't see in the DB view. When I hover over the Info icon, I can see the VI number but the VI number field is empty:

The client wants to be able to click the VI and be redirected to it which is not possible for the manually created VIs since there's no reference to them.

The reason I'm joining VI and Detection tables is because they want a lot of fields from Detection but other tables as well and Detection has reference fields to 4 or 5 other tables I can use instead of joining those 4 or 5 tables.

[Fatih Karacaer · Accepted solution]

Hi Patrik,

Manually created VIs will not have associated detection records. So you will have blank columns for detections for them and it is expected behaviour. Left join is the right way to go.

Here is a good diagram which describes the joins.

The left join or the left outer join statement returns all the records from left table and also those records which satisfy a condition from the right table, also for the records having no matching values in right table, the output or resulting dataset will contain NULL values.

So in your case, left table should be the VI table and right table should be the Detections table and the type of join should be Left. And manually created VIs will have NULL values for associated detections.

Please mark it as helpful if it is!

Kind regards,

Fatih.

[Patrik Z]

Hi Fatih,

I expected empty columns but I didn't have the manually created VIs in the DB view at all.

Anyway, there was a cloneback from Prod to Dev and now it's working as expected (manually created VIs are visible in the DB view). Either the cloneback fixed it or I've been overlooking some tiny detail but Idk what it was to be honest.

Thanks everyone