I am creating a space for risk assessment of an IT infrastructure.
The infrastructure is a hierarchical database, so I will have for example:
Lan x → rack xy → router xy1
Lan x → rack xz → router xz1
Lan x → rack xz → router xz2
Each layer has specific risks and threats.
For example Lan x has its own risks, rack xy has its own risks etc…
What I want is for each level of the infrastructure examined to be visible:
- the layer’s own risks (e.g., risks related to router xy1)
- the risks of all hierarchically higher levels, in the case of router xy1 the specific risks of rack xy that hosts router xy1 and those of Lan X.
I was only able to display the risks of the next higher level, by means of a lookup field, but I could not trace them back to the hierarchical levels even higher.
A formula can be used, but I no longer have the availability of entities, but have text values.
Do you have any idea how I can do this?
Until Fibery supports a union function in formulas, I don’t think it is possible to write a recursive formula that returns a collection of entities.
As you correctly point out, it is possible to return a text string of all risks’ names, for example, but not a to-many relation.
Note: it can theoretically be solved with automations, but it is difficult to make it perform reliably (what if things get added/deleted, linked/unlinked, etc.?) and the results can be fragile: since automations operate on editable fields, any user could unwittingly introduce errors.
Is “Equipment Risks” a Collection of “Risk” entities ?
Yes. I want for each hierarchical level of the infrastructure, the risks related to that level and those related to higher hierarchical levels to be visible.
This is only possible for the next higher hierarchical level with a lookup field.
You cannot go beyond that. With a ricorsive function field using the join() function, you can, but you don’t have entities; you just get the data in text format.
It’s absolutely not ideal, but you could likely use some formula fields with join(), filter() and matchregex() functions in combination with some automation rules that leverage the values in those formula fields.
This is what I have to do and I hate it, which is why I created the feature request for a union() function!
I agree with you, the solution is to implement the union() function.
Based on the fact you were using a lookup field I think you’re okay with having multiple risks fields, (i.e. Racks will have two risk fields and Routers will have three).
Im assuming you have Lan, Rack and Router in different tables. The way I use the above is actually having all of the entities in the same table using hyphens to join them together (e.g. x, x-xz, x-xz-xz1, etc) and a regex to generate the rest of the fields. But far simpler if you have them in separate tables which is what I’ve done below:
Create the following calculated fields in your infrastructure tables
Rack: LanText is [Rack].Name
Router: RackText is [Rack].Name
Router: LanText is [Rack].LanText
Assuming your risks table has 3x relationships to the infrastructure tables already
Risks: LanText is [Lan].Name
Risks: RackText is [Rack].Name
In your Rack table, create a second relationship to Risk automatically linking based on LanText = LanText
In your Router table, do the same for both LanText and RackText
Should wind up looking like this:
I thank you because you have given me an idea.
Without doing multiple tables as you do, but one hardware infrastructure table (for risk assessment purposes we don’t need to detail the hardware in a sophisticated way, although your way is definitely preferable when you want to detail each hardware with its specific fields).
I proceeded as follows.
Instead of doing a one-to-many relationship on the infrastructure table to allow me to create a hierarchy, I created a many-to-many relationship again on the infrastructure table. Naming the fields created by the relationship “Influenced Infrastructure” and “Related Infrastructure.”
Then reporting in “related infrastructures” all those infrastructures that influence the one being considered, both those that are hierarchically above it in reality (all those hierarchically above, not just those immediately above) and those that are not hierarchically related. Then creating a lookup field on the relationship to report on threats, risks, mitigations, etc…
Here is a minimized example.
Related infrastructures [infrastrutture correlate] are those from which the current infrastructure inherits threats (in this case just the threats, then I will add more data).
Influenced infrastructures [infrastrutture influenzate] are those that inherit threats from the current infrastructure.
In the simple example, router xy1 inherits threats from rac xy (which hosts it), and from backbone x
Router xy1 then has its specific threats in the threat field (in Italian “Minacce”) and in the inherited threat field (“Minacce ereditate”) those threats that are specific to other infrastructures, but affect the examined infrastructure (router xy1).
Example a threat of fiber optic backbone cable breakage will affect all infrastructure connected to it, e.g., directly connected routers. So it is fair for the router to expose this risk.
In the end I managed to do what I wanted, in terms of data structure. The limitation of this approach is based on a many-to-many relationship, it does not allow a hierarchical visualization with the powerful visual tools that Fibery makes available.
Have a nice day!
In reality, if you create a parallel one-to-many relationship on the infrastructure table, you can use this to imprint the hierarchy which can be viewed as such, for example in a list.
I’m doing some tests, I don’t want to create problems of structural instability.
At first glance it seems to work.