Is it possible to include all devices that has the alarming device as parent? I’m assuming it is possible but I can’t figure out how. Actually I would also like to include other information from the dependant devices like their location and such.
Something like this, just off the top of my head, so might be wrong. Only includes the first level of children.
<ul>
@foreach (Device::find($item['device_id'])->children as $child)
<li>$child->name()</li>
@endforeach
</ul>Thank you for your reply but I’m not that familiar with the syntax yet and your code does not work in my template. Not sure what part that is not working but it won’t let me update the template unless I correct the faulty syntax. Obviously, I’d need to have it nested in some way to get all the affected devices though. Is it possible to set multiple parents for a device? That would solve the problem with nested loops to find all affected devices but it would be a nightmare to maintain…
In mysql, I can use the following query to get all children (recursive):
with recursive descendants as
(
select parent_device_id as parent, child_device_id as device_id, 1 as level from device_relationships where parent_device_id = 1177
union all
select d.parent, s.child_device_id, d.level + 1 from descendants as d
inner join device_relationships s on d.device_id = s.parent_device_id
)
select hostname, sysname from devices
join descendants using (device_id);
Is it possible to use that sql query in an alert, to include that information in the alert template?
Since I never got a reply, I was able to work it out myself but it’s not in the way I want it yet. I don’t want to have the password for the database in clear text in the configuration. I’m attaching a masked piece of code that I used to accomplish this. Also attaching an example of an alert message, masked also of course.
1 Like
DB connection is already available…
Check syntax here: https://laravel.com/docs/queries (Also LibreNMS does use Eloquent)
1 Like
On the back of Murrant’s advice, I’ve cobbled together the following which works for immediate children. I’ll work on a recursive version when I have more time.
Severity: {{ $alert->severity }}
Rule: @if ($alert->name)
{{ $alert->name }}
@else
{{ $alert->rule }}
@endif
@if ($alert->state == 0)
Time elapsed: {{ $alert->elapsed }}
@endif
Timestamp: {{ $alert->timestamp }}
Unique-ID: {{ $alert->uid }}
Hostname: {{ $alert->hostname }}
IP address: {{ $alert->ip }}
Location: {{ $alert->location }}
Uptime: {{ $alert->uptime_long }}
@if ($alert->faults)
@foreach ($alert->faults as $key => $value)
Hardware: {{ $value['hardware'] }}
Software: {{ $value['version'] }}
Serial number: {{ $value['serial'] }}
@php
$children = DB::table('devices')
->join('device_relationships', 'devices.device_id', '=', 'device_relationships.child_device_id')
->select('devices.hostname', 'device_relationships.child_device_id')
->where('device_relationships.parent_device_id', $alert->device_id)
->orderBy('devices.hostname')
->get();
@endphp
@if ($children)
Children:
@foreach ($children as $key => $value)
{{$value->hostname}}
@endforeach
@endif
@endforeach
@endif
1 Like
Thank you. I think you could use the sql query that I posted above in your code and it will accomplish the recursive check. The only problem is that you need to upgrade MariaDB to at least 10.4 for RECURSIVE to work.
Yes, I ran into that problem already - Ubuntu 18.04 with the default 10.1 MariaDB install 
1 Like
How do I refer to the DB connection in my code above? Or would I need to completely rewrite it?
I think your best bet is to use the DB::raw method described at https://laravel.com/docs/6.x/queries#raw-expressions. Others with proper Laravel-fu may have a more elegant solution…