Jump to content

Internal Monitor to Compare EDF's

Recommended Posts

Hey all,

I've got a situation where I need an internal monitor to compare a Computer EDF to a Client EDF and raise a ticket if the values differ. Specifically, it's a monitor to ensure the AV agent is registered to the correct client.

I can create a monitor to check a single EDF against an arbitrary value but I'm struggling with the SQL query to have the monitor check against the other EDF. The monitor is currently as in the screenshot. I know I need a SQL query in the result text box but I'm striking out on what to put in there. The table is v_extradataclients and the field is named the same as shown in the screenshot (Sophos Central Client Registration). When I copy pasta the generated query into SQLYog, I keep getting either syntax errors or complaints about the subquery returning more than one row.

An example of what I've tried that has returned results (though not the correct results) is:

(Select v_extradataclients.`Sophos Central Client Registration` as ID FROM v_extradataclients, WHERE Computers.ClientID=v_extradataclients.clientid)

Anybody have an idea what I'm doing wrong?


Share this post

Link to post
Share on other sites

You'll have to use a RAWSQL monitor to get this done, but it absolutely can be. There are a couple posts out there about RAWSQL monitors, I'd also recommend firing up SQLyog and working out the syntax there. 

ProTip: I stole the SQL syntax needed to search for EDFs from the 'Show SQL' button of 'Searches'.

Share this post

Link to post
Share on other sites

Thanks for the tip clutch70! I had completely forgotten you could do that with the searches. I will be making use of that.

On the original question, it turns out, I'm just having a stupid week. The subquery was returning results as it should. The problem was that I was expecting it to return results for computers and clients where the EDF's haven't been populated. Surprise though, null and null are a match... so I was only getting the small handful of test computers with differing EDF values. I ended up with:

(Select v_extradataclients.`Sophos Central Customer ID` FROM v_extradataclients WHERE Computers.ClientID=v_extradataclients.clientid)

as the subquery in the Result field. While it didn't hurt anything the "AS ID" part in the original was not necessary. All in all, it's working as it's supposed to so I just need to deploy it and populate the EDF's.

Good times.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...