Automating the World

We have migrated servers, if you encounter any issues, please let us know on Slack, or post it.


We have also disabled registration as we migrate to the new forum systems. The slack (slack.labtechgeek.com) will always be accepting new users.
 
User avatar
LoneWolf
Geek
Topic Author
Posts: 102
Joined: Tue Jan 26, 2016 9:10 am
Current LT Agent Count: 1000+
Location: The Mitten State, USA

Modify offline locations monitor (SQL change help needed)

Thu Oct 05, 2017 7:03 am

Here's the scenario:

LabTech has an Offline Locations monitor. This monitor is meant to alert (if you enable it) if no systems at a location are online. This is very useful for us in many scenarios, but not when a client has no permanently-on systems.

Unfortunately, it's only at the top level, so rather than change from a group, it looks like I'll have to change this with a custom monitor (disabling the in-place monitor). Here's the Monitor condition:

(Select Count(*) from Computers Where Computers.locationid=locations.locationid)>0 and (Select Count(*) from Computers Where Computers.locationid=locations.locationid and Computers.LastContact>Date_Add(Now(),Interval -10 minute))=0 AND locations.LocationID NOT IN ('1','2')and (SELECT `Exclude Offline Check` <> 1 FROM v_extradatalocations WHERE v_extradatalocations.locationid=locations.locationid)

I want to add an "Except when Location is part of <MyExampleGroup>" so that I can use an EDF+autojoin search to add locations to <MyExampleGroup> such that those groups will not flag an alert, but everything else will. As I'm not an SQL wizard, what would I change?

Thanks for any help you can provide.
We are SysAdmins.
We walk in the wiring closets no others will enter.
We stand on the bridge, and no malware may pass.
We engage in tech support, we do not retreat.
We live for the LAN. We die for the LAN.
 
User avatar
DarrenWhite99
Geek
Posts: 513
Joined: Wed Jun 04, 2014 11:17 pm
Current LT Agent Count: 1500+
Location: Redding, California, US

Re: Modify offline locations monitor (SQL change help needed)

Tue Oct 10, 2017 12:17 am

Why not just use the Location Based EDF (Location -> Ignite) for "Exclude Offline Check" that is already built in? For any locations without permanent systems that you don't want to be monitored, you just check that box and the location will be ignored.
 
User avatar
LoneWolf
Geek
Topic Author
Posts: 102
Joined: Tue Jan 26, 2016 9:10 am
Current LT Agent Count: 1000+
Location: The Mitten State, USA

Re: Modify offline locations monitor (SQL change help needed)

Tue Oct 10, 2017 7:06 am

DarrenWhite99 wrote:
Why not just use the Location Based EDF (Location -> Ignite) for "Exclude Offline Check" that is already built in? For any locations without permanent systems that you don't want to be monitored, you just check that box and the location will be ignored.


Hi Darren,

Yes, complete brain fart on my part. Thank you for the assist.
We are SysAdmins.
We walk in the wiring closets no others will enter.
We stand on the bridge, and no malware may pass.
We engage in tech support, we do not retreat.
We live for the LAN. We die for the LAN.