Jump to content
[[Template core/front/profile/profileHeader is throwing an error. This theme may be out of date. Run the support tool in the AdminCP to restore the default theme.]]

Slartibartfast last won the day on August 31 2018

Slartibartfast had the most liked content!

Community Reputation

4 Neutral

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. We've all come to the forums at some point looking for a way to get more specific results from a monitor than is available with the default tools and options in CWA, and have stumbled across posts detailing the complex and arcane process of making RAWSQL monitors. These are basically where you take all the builtin logic that monitors do behind the scenes and recreate it manually with a query. The advantages of this are that you can get much more specific with your queries to a degree simply not possible in a regular monitor. There are downsides to RAWSQL monitors though. They require a lot more work up front. Regular monitors do a lot in the background, like returning a bunch of info that ties the results to the computers detected, and CWA assumes this data is returned so a monitor that doesn't do this will not alert properly. This can be done manually, mostly by joining the agentcomputerdata table in the query and returning values from that. Even if you do this properly, some features like ignoring agents or group targeting (according to Darren) have to be done manually in the query rather than from the simple GUI. There are also issues with supportability, if you ask a support person to help you with anything even somewhat relating to a RAWSQL monitor they will laugh you right off the phone. So, what if you want to do something a little more complex than a regular monitor can handle, but don't want to deal with the atrocity that is RAWSQL? I would use what I like to call a LazySQL monitor. A LazySQL monitor is one in which you let the builtin monitor functions handle most of the busywork, and you use the additional conditions field to limit the results to computers returned from an SQL query or queries that do all the complex selections and limiting and whatnot. Basically, you make a monitor that by default catches every computer (I make it look for "computerid notequals 0"), I will attach an example of a monitor where I do just that so you can visualize it more easily. As you can see, the basic monitor configuration is very simple and would match all computers, then I do the more specific stuff, like returning computers that have patches missing and an EDF checked, as subqueries in the additional conditions field. This field basically just tacks on whatever is in it to the end of the SQL query put together by the monitor. For a simpler use case, let's take one I just finished explaining to a user in the slack channel. He wanted to put together a RAWSQL monitor to return all computers that didn't have a specific software installed, for our purposes let's say firefox. Instead of making a complex RAWSQL monitor to do this somewhat simple thing, he could use a LazySQL monitor. Using the same basic settings as the above picture, simply adding "AND computerid NOT IN(SELECT DISTINCT c.computerid FROM computers c JOIN software s ON c.computerid = s.computerid AND s.name LIKE "%firefox%")" as the additional condition field made the monitor limit it's results from all computers, to only those whose IDs were NOT returned by the subquery, which returns all those that do have firefox installed. This monitor will have greater supportability, greater functionality because it fits into CWA better, and took about 5 minutes to make and deploy. Please note that I am aware the last example returning computers without firefox could be accomplished easily with a regular monitor by using the invert check function. LazySQL monitors shine when you need to match a bunch of disparate criteria because it's easy to gather the computerids that match in a subquery and just check for "computerid not in (subquery)". Try not to nest a bunch of subqueries inside each other, if you can, because that can be slow. If you have any questions, you can always try asking me in the slack channel -Slartibartfast of Magrathea
  2. You can do that, but these are just simple SQL queries so if they are failing your server likely has a serious problem that will very quickly become apparent. I've been running these for a year+ and never had any of them fail.
  3. I know they reported that the issue with the windowsupdateetlfiles table was fixed but I think people still encountered the issue. You can certainly try turning off this step and seeing if the table remains reasonably sized.
  4. I often find myself giving out several SQL Execute steps I run in a daily script and explaining what they do, so this is mainly to save me time and typing. I am simply going to list the SQL Statements and an explanation of what each one does. These all go into a script that I schedule against the CWA server 1 time a day, but you can schedule it however often you like. 1. UPDATE agents SET lastscan = now() WHERE lastscan > now() This resets all disabled internal monitors 2. UPDATE hotfix SET pushed='0' WHERE installed=0 AND pushed=1 AND approved=2 This resets the "pushed" flag for deployed patches, so CWA doesn't just give up after they fail a few times. I'm not sure if this is still needed but it doesn't hurt. If you are still using pre version 11 patching then I envy you, but change the value of "approved" to 1. 3. DELETE FROM drives WHERE missing=1 I'm not even going to explain this one 4. DELETE FROM agents WHERE agents.name LIKE "%drive space critical%" AND agents.name NOT LIKE "%Disk - C:%" AND agents.computerid NOT IN (SELECT computerid FROM computers WHERE `os` LIKE "%server%") This removes all those pesky disk space remote monitors on PCs for everything other than the C drive, because on workstations I could not care less about those drives. 5. DELETE FROM ScriptState WHERE LOWER(Variable) LIKE '%ticketid' AND VALUE NOT IN (SELECT ticketid FROM tickets) This is something from Chris Taylor, so you know it's good 6. DELETE FROM eventlogs WHERE eventid IN (2280,5139) This just deletes some events that seemed to take up a lot of space and that I didn't find useful 7. DELETE FROM eventlogs WHERE message LIKE "%software protection%" OR message LIKE "%uninitializing automatic updates%" OR message LIKE "%Security ID:S-1-0-0%" OR source="Microsoft-Windows-Security-Auditing"AND blacklistid=0 Same reason as step 6 8. DELETE FROM windowsupdateetlfiles Deletes everything from this table, as this table is known for it's insane bloat and uselessness, and I seem to recall some potential issues with truncate that delete doesn't suffer from 9. DELETE FROM computerroledefinitions WHERE currentlydetected=0 AND `type`=0 This removes roles that were detected and are now missing from agents 10. DELETE FROM plugin_screenconnect_scinstalled WHERE computerid NOT IN (SELECT computerid FROM computers) This removes old CWC sessions from the database, as retired computers don't get removed from this table.
  5. On the IRC channel so far viewtopic.php?f=5&t=1009 <- in case anyone is bored, or has ran into this before Sorry Katty, not something _I've_ seen. [12:30] No clue [12:30] okie dokie [12:30] Is there anything on the DC that Robin SHOULD be accessing? [12:31] Does robin even exist? [12:31] only thing i can think of is maybe they have something installed that's looking for shares but doesn't have all the cred info. [12:32] i'd lean towards a peristent mapped drive with stored expired creds [12:32] yea
  • Create New...