 Rank: Advanced Member Medals:  Joined: 2/6/2014(UTC) Posts: 316   Thanks: 6 times
Was thanked: 33 time(s) in 29 post(s)
|
Originally Posted by: KatyComputer  @ShawnKHill: What do you mean by "Purging the database"? Hi, @KatyComputer, The database used by ScreenConnect/ConnectWise Control is an SQLite DB located at "%ProgramFiles(x86)%\ScreenConnect\App_Data\Session.db". This database holds all your device information, notes, logs, commands, and so on. Using a database editor ( DB Browser for SQLite, for example) will allow you to open the Session.db file and run SQL commands against the database. The structure is a little funky, but you can purge (remove) commands from the command history, which effectively prevents them from being re-enqueued ad nauseum (aka, "command loop from hell"). I have been able to resolve 8 separate instances of "command loop from hell" now using this method. Caveats:
- It's your entire SC database. If you don't know what you're doing, leave it alone. The risk of significant damage (including crippling your entire platform) does exist. Back it up first:
Code:robocopy "%ProgramFiles(x86)%\ScreenConnect\App_Data\Session.*" "c:\backup" /r:1 /w:1 /zb
- Using any interactive editor, such as DB Browser for SQLite, locks your database. This means that while you're fiddling with it any commands, logs, notes and so on that would otherwise occur from the ScreenConnect/Control functionality will fail. Be brief! Open, run, save, close. Practice with a copy. Practice with two copies. Don't practice with your live database. :)
- The EventType values are magic numbers, not descriptive terms. This means that you won't see "enqueued command" as an EventType directly in the Session*Event table, but will instead see 44. You won't see "ran command", but will instead see 70. Incomplete/dated list here.
- Make sure you replace "MyBroken-PC" with the name of your device. It won't work unless you do. You could also flag devices with a CustomProperty or Note that indicates that it is suffering from command-loop-from-hell syndrome and use that field to select the device instead. Samples further down for those.
- You may not want/need to remove the EventType=70 (ran command) if there is important data in one or more of the command outputs from previous commands. In my experience, however, where there were 131k copies of the same looped command that ran on one device, you would be well advised to do it anyway. The amount of wasted space could be enormous. And, of course, you got your backup copy first, right?
- Your mileage may vary. While this has worked consistently for me to resolve this (and several other problems), it will not necessarily address every conceivable situation.
Here again is the code I use. This deletes the RanCommand events (70) then the QueuedCommand events (44). This effectively removes any trace of the command that's responsible for the loop. Code:Delete FROM SessionConnectionEvent
Where SessionID = (Select Session.SessionID FROM Session WHERE Session.Name='MyBroken-PC')
AND EventType = 70;
Delete FROM SessionEvent
Where SessionID = (Select Session.SessionID FROM Session WHERE Session.Name='MyBroken-PC')
AND EventType = 44;
To effect every single device with a CustomProperty8 with a value of "commandLoopFromHell" use: Code:Delete FROM SessionConnectionEvent
Where SessionID = (Select Session.SessionID FROM Session WHERE Session.CustomProperty8='commandLoopFromHell')
AND EventType = 70;
Delete FROM SessionEvent
Where SessionID = (Select Session.SessionID FROM Session WHERE Session.CustomProperty8='commandLoopFromHell')
AND EventType = 44;
You could even add the following line at the end to empty CustomProperty8 to ensure that it doesn't get run again against the same machine: Code:UPDATE Session SET CustomProperty8='' WHERE Session.CustomProperty8='commandLoopFromHell';
To effect every single device with a Note that has been added with a value of "commandLoopFromHell" use: Code:Delete FROM SessionConnectionEvent
Where SessionID = (Select SessionEvent.SessionID FROM SessionEvent WHERE SessionEvent.Data='commandLoopFromHell')
AND EventType = 70;
Delete FROM SessionEvent
Where SessionID = (Select SessionEvent.SessionID FROM SessionEvent WHERE SessionEvent.Data='commandLoopFromHell')
AND EventType = 44;
Delete FROM SessionEvent WHERE SessionEvent.Data='commandLoopFromHell';
Then close the database. Finally, there are command-line SQLite engines or you could use VBScript, asp.net or PHP to get the same result as using an interactive editor. In this way you could automate the process to have the least possible downtime (less than 1 second per run). Then, simply adding commandLoopFromHell to a note would then trigger a command purge on that device when your process ran every hour/day or so. It could even check for the note first to ensure that it wasn't wasting an expensive (and blocking) DELETE when a SELECT showed it wasn't necessary. :)
|
 2 users thanked shawnkhall for this useful post.
|
|
