Jump to content
DarrenWhite99

Manage Power Profiles Using an Internal Monitor

Recommended Posts

This SQL code is designed to manage agent power profiles using an internal RAWSQL monitor, automatically issuing commands as needed to switch the profile to the desired settings. The first two lines provide two options for defining the targeted power role. First is a list of 24 numbers (After some function stuff), where the values 1,2,3 correspond to the profile position in the list. As shown, 1='High Performance', 2='Balanced', 3='Power saver'. The 24 numbers correspond to the 24 hours in a day. Simply designate which profile you want to be targeted during that hour. As shown, 'High Performance' would be selected until 10:00AM (no sleeping), then from 10:00AM - 8:00PM, 'Balanced' would be used, from 8:00PM - 11:00PM 'Power saver' would be targeted, and then from 11:00PM to midnight, 'High Performance' would again be targeted. The commented line right below is a simpler approach, just defining the targeted profile to always be 'High Performance'.

NOTE - This SQL has been updated as of 2019-05-15. If you are using this, please review your monitor.

The first SQL statement block issues a command to the agent to run powercfg.exe and set the active profile (only for online agents, only if a powercfg.exe OR Resend System Info command is not already pending or executing). The second SQL statement block issues a command to Resend System Info, so that LabTech can learn the current active power profile. This also is only sent to online agents that to not have a Resend System Info command pending. (The powercfg.exe command should execute quickly enough that the new state will be reflected in the results of the System Info data). The third SQL statement is what returns results to the monitor. This is all agents (on or offline) that have incorrect power profiles. Any detected agent will be returned the first time, and by the next time the monitor runs the agent profile should have been corrected and returned in updated results. If it is still incorrect and no commands are pending, new ones will be issued until it is corrected. Since the agent commands are being issued during the monitor execution (before the results are returned) you can still use the monitor's alert action to run your own intervention script or alert template (create a ticket?).

 

SET @TargetProfile=ELT(ELT(ROUND(MOD(HOUR(NOW()),24)+1,0),1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,3,3,3,1),'High Performance', 'Balanced', 'Power saver');
#SET @TargetProfile='High Performance';
SET @TestAgent=-1;
SET @InternalMonitorIDNumber=-1;
INSERT IGNORE INTO commands (ComputerID, Command, Parameters, Output, FastTalk) SELECT DISTINCT Result.computerid, '2', CONCAT('cmd!!!/c "%Windir%\\System32\\PowerCfg.exe" /S ',result.TargetPowerGuid,' && REM Enforced'),'','0' 
FROM computers LEFT JOIN commands ON commands.computerid=computers.computerid AND commands.command=2 AND commands.parameters LIKE '%powercfg.exe%' AND commands.status NOT IN (3,4) LEFT JOIN commands AS c2 ON c2.computerid=computers.computerid AND c2.command=123 AND c2.status NOT IN (3,4)
JOIN (SELECT DISTINCT computers.computerid AS computerid, SUBSTRING_INDEX(LEFT(PowerProfiles,LOCATE(@TargetProfile,PowerProfiles)-2),'|',-1) AS TargetPowerGuid FROM computers WHERE LOCATE(@TargetProfile,PowerProfiles)>0) AS result ON result.computerid=computers.computerid 
WHERE (computers.CurrentPwrProfile NOT LIKE result.TargetPowerGuid) AND commands.cmdid IS NULL AND c2.cmdid IS NULL AND computers.lastcontact>DATE_SUB(NOW(), INTERVAL 15 MINUTE) 
AND computers.ComputerID IN (@TestAgent);
#AND Computers.ComputerID IN (SELECT computerID FROM TCOMP) AND Computers.ComputerID NOT IN (SELECT ComputerID FROM AgentIgnore WHERE AgentID=@InternalMonitorIDNumber);

INSERT IGNORE INTO commands (ComputerID, Command, Parameters, Output, FastTalk) SELECT DISTINCT Result.computerid, '123', '', '', '0' FROM computers LEFT JOIN commands AS c2 ON c2.computerid=computers.computerid AND c2.command=123 AND c2.status NOT IN (3,4) 
JOIN (SELECT DISTINCT computers.computerid AS computerid, SUBSTRING_INDEX(LEFT(PowerProfiles,LOCATE(@TargetProfile,PowerProfiles)-2),'|',-1) AS TargetPowerGuid FROM computers WHERE LOCATE(@TargetProfile,PowerProfiles)>0) AS result ON result.computerid=computers.computerid
WHERE computers.lastcontact>DATE_SUB(NOW(), INTERVAL 15 MINUTE) AND computers.CurrentPwrProfile NOT LIKE result.TargetPowerGuid AND c2.cmdid IS NULL
AND computers.ComputerID IN (@TestAgent);
#AND Computers.ComputerID IN (SELECT computerID FROM TCOMP) AND Computers.ComputerID NOT IN (SELECT ComputerID FROM AgentIgnore WHERE AgentID=@InternalMonitorIDNumber);

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING(PowerProfiles,1+LOCATE(',',PowerProfiles,LOCATE(CurrentPwrProfile,PowerProfiles))),'|',1) AS CurrentProfileName, computers.CurrentPwrProfile AS Identity, computers.ComputerID, computers.Name AS `ComputerName`, CONVERT(CONCAT(clients.name,' ',locations.name) USING utf8) AS Location, acd.NoAlerts, acd.UpTimeStart, acd.UpTimeEnd
FROM computers JOIN commands ON commands.computerid=computers.computerid JOIN locations ON locations.locationid=computers.locationid JOIN clients ON clients.clientid=computers.clientid JOIN AgentComputerData AS acd ON Computers.ComputerID=acd.ComputerID 
JOIN (SELECT computers.computerid AS computerid, SUBSTRING_INDEX(LEFT(PowerProfiles,LOCATE(@TargetProfile,PowerProfiles)-2),'|',-1) AS TargetPowerGuid FROM computers WHERE LOCATE(@TargetProfile,PowerProfiles)>0) AS result ON result.computerid=computers.computerid
WHERE (computers.CurrentPwrProfile NOT LIKE result.TargetPowerGuid)
AND computers.ComputerID IN (@TestAgent);
#AND Computers.ComputerID IN (SELECT computerID FROM TCOMP) AND Computers.ComputerID NOT IN (Select ComputerID from AgentIgnore Where AgentID=@InternalMonitorIDNumber)
Each SQL statement ends with the following:

AND computers.ComputerID IN (@TestAgent);

#AND Computers.ComputerID IN (SELECT computerID FROM TCOMP) AND Computers.ComputerID NOT IN (SELECT ComputerID FROM AgentIgnore WHERE AgentID=@InternalMonitorIDNumber);

 

To test the monitor, define @TestAgent and run as is..

For production use, you must:

  • Select at least one group target (so that the table TCOMP will be created)
  • Edit the statements, the last two lines for each statement should be changed to:
    • #AND computers.ComputerID IN (@TestAgent);
    • AND Computers.ComputerID IN (SELECT computerID FROM TCOMP) AND Computers.ComputerID NOT IN (SELECT ComputerID FROM AgentIgnore WHERE AgentID=@InternalMonitorIDNumber);

    (And/Or just entirely remove the line: AND computers.ComputerID IN (@TestAgent);)

    [*]Define @InternalMonitorIDNumber to the monitor id assigned in your system. This is necessary for the Exclusions feature of the monitor to work.

    [*]Make sure that the first two SQL statements properly terminate with a semicolon, but ensure that the last statement does NOT end with a semicolon.

 

For more information on RAWSQL Monitors, see http://www.labtechgeek.com/forum/viewtopic.php?f=7&t=293#p21022

Edited by DarrenWhite99
Updated Second SQL section

Share this post


Link to post
Share on other sites

After editing the last block

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING(PowerProfiles,1+LOCATE(',',PowerProfiles,LOCATE(CurrentPwrProfile,PowerProfiles))),'|',1) AS CurrentProfileName, computers.CurrentPwrProfile AS Identity, computers.ComputerID, computers.Name AS `ComputerName`, CONVERT(CONCAT(clients.name,' ',locations.name) USING utf8) AS Location, acd.NoAlerts, acd.UpTimeStart, acd.UpTimeEnd
FROM computers JOIN commands ON commands.computerid=computers.computerid JOIN locations ON locations.locationid=computers.locationid JOIN clients ON clients.clientid=computers.clientid JOIN AgentComputerData AS acd ON Computers.ComputerID=acd.ComputerID 
JOIN (SELECT computers.computerid AS computerid, SUBSTRING_INDEX(LEFT(PowerProfiles,LOCATE(@TargetProfile,PowerProfiles)-2),'|',-1) AS TargetPowerGuid FROM computers WHERE LOCATE(@TargetProfile,PowerProfiles)>0) AS result ON result.computerid=computers.computerid
WHERE (computers.CurrentPwrProfile NOT LIKE result.TargetPowerGuid)
#AND computers.ComputerID IN (@TestAgent)
AND ComputerID IN (SELECT computerID FROM TCOMP) AND Computers.ComputerID NOT IN (SELECT ComputerID FROM AgentIgnore WHERE AgentID=@InternalMonitorIDNumber); DROP TEMPORARY TABLE IF EXISTS Tcomp;

I get an error:

 

Query: SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING(PowerProfiles,1+LOCATE(',',PowerProfiles,LOCATE(CurrentPwrProfile,PowerProfiles))),'|'...

Error Code: 1052
Column 'ComputerID' in IN/ALL/ANY subquery is ambiguous

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

Share this post


Link to post
Share on other sites

Hi Darren, 

I am working to reduce the number of tickets we get for 'Webroot 3 - Stale Agent' and believe some of our agents are on systems that hang out in standby most of the time. Your monitor could bring the number down, however, two of our clients would not like it if we changed their power plans. Do you know of a way to apply or not apply a specific monitor to a client or location? I don't want to create a service plan, as I would have to adjust all of the monitors manually. 

Thank you. 

Share this post


Link to post
Share on other sites
4 hours ago, PaulH said:

I am working to reduce the number of tickets we get for 'Webroot 3 - Stale Agent' and believe some of our agents are on systems that hang out in standby most of the time. Your monitor could bring the number down, however, two of our clients would not like it if we changed their power plans. Do you know of a way to apply or not apply a specific monitor to a client or location? I don't want to create a service plan, as I would have to adjust all of the monitors manually. 

Ignoring a specific location or client is pretty simple if you modify the monitor. It's more complex to use EDFs to control it, but EDFs are the nicer option. (I won't get into the SQL to make the EDF option work, but it is very doable).

The monitor has multiple queries, each ending in

AND computers.ComputerID IN (@TestAgent);
#AND Computers.ComputerID IN (SELECT computerID FROM TCOMP) AND Computers.ComputerID NOT IN (SELECT ComputerID FROM AgentIgnore WHERE AgentID=@InternalMonitorIDNumber);

As it explains, you are supposed to change to this in each place when you are ready to go live:

AND Computers.ComputerID IN (SELECT computerID FROM TCOMP) AND Computers.ComputerID NOT IN (SELECT ComputerID FROM AgentIgnore WHERE AgentID=@InternalMonitorIDNumber);

To exclude locations or clients, insert the following to the beginning of that line: 

AND Computers.ClientID NOT IN (1,2,3,4) AND Computers.LocationID NOT IN (4,7,9,11)

And there you have it. Computers from Clients 1,2,3 and 4, and Computers in Locations 4,7,9, and 11, will not be included by the monitor.

Share this post


Link to post
Share on other sites

Like this? 

AND Computers.ClientID NOT IN (93,33) AND Computers.LocationID NOT IN (240,157) #AND Computers.ComputerID IN (SELECT computerID FROM TCOMP) AND Computers.ComputerID NOT IN (Select ComputerID from AgentIgnore Where AgentID=@InternalMonitorIDNumber)

Share this post


Link to post
Share on other sites
4 hours ago, PaulH said:

Like this? 

AND Computers.ClientID NOT IN (93,33) AND Computers.LocationID NOT IN (240,157) #AND Computers.ComputerID IN (SELECT computerID FROM TCOMP) AND Computers.ComputerID NOT IN (Select ComputerID from AgentIgnore Where AgentID=@InternalMonitorIDNumber)

The "#" is supposed to be removed when you switch from targeting a single computer (AND computers.ComputerID IN (@TestAgent);) to full production (the second line starting with the #). Otherwise, yes, the line is correct.

That would prevent any computer from client 33 or 93, and any computer in location 157 or 240, from being affected by this monitor.

The monitor also still supports normal group targeting (which would not override your hardcoded conditions) so you can target only specific service plans, or types of computers, and even individually exclude agent (Monitor -> Exclusions -> Disabled agents).

Share this post


Link to post
Share on other sites
3 hours ago, PaulH said:

D'oh! Your (@TestAgent) is a group that you use for testing.

Actually, TestAgent is a computerid, set in the line that says:
SET @TestAgent=-1;

Just change that number to a specific computerid that you want to test with before re-configuring the monitor to impact multiple computers in production.

Share this post


Link to post
Share on other sites

What happens to the sql code and the monitor if the computer does not have the High Performance power profile?  I have some computers that don't have a High Performance option, only Power Saver or the manuf.'s custom power plan.  Also, when High Performance is chosen, is the hard disk set to never turn off and are all the sleep timers set to 0?

Share this post


Link to post
Share on other sites
3 hours ago, axiomcrs2 said:

What happens to the sql code and the monitor if the computer does not have the High Performance power profile?  I have some computers that don't have a High Performance option, only Power Saver or the manuf.'s custom power plan.  Also, when High Performance is chosen, is the hard disk set to never turn off and are all the sleep timers set to 0?

If the named plan does not exist, nothing happens.

High Performance does whatever it is set to do. Managing the actual settings for "High Performance" (or your own custom plans, etc.) is a separate topic.

FYI, I noticed a mistake that could cause the monitor to re-send system inventory when it shouldn't. The SQL in the top post has been corrected, if you use this you should compare and update.

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.

Guest
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...