Jump to content
Slartibartfast

Daily SQL Script

Recommended Posts

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

Edited by Slartibartfast
  • Like 3

Share this post


Link to post
Share on other sites
25 minutes ago, Slartibartfast said:

5. DELETE FROM ScriptState WHERE LOWER(Variable) LIKE '%ticketid' AND VALUE NOT IN (SELECT ticketid FROM tickets)

        This is something from Chsir Taylor, so you know it's good Specifically this deletes any noted ticket IDs from the script state table when those tickets no longer exist.

8. delete from windowsupdateetlfiles

        Deletes everything from this table, as this table is known for it's insane bloat an uselessness, and I seem to recall some potential issues with truncate that delete doesn't

        suffer from -I think the issues with this table have been fixed but I don't recall for sure.

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
DELETE FROM computerroledefinitions WHERE currentlydetected=0 AND `type`=0;

Another good one to add to this list! In certain cases will reduce monitor noise - this detects roles that were detected and are now missing from agents. A lot of these will be false positives generated by poorly coded roles (IE a lot of the official ones)

Share this post


Link to post
Share on other sites

Does anyone have a script for deleting tickets older than 90 days? If the ticket has reached this age, we would consider it not sync'd with the plugin and worth manually killing in LT.

  • Like 1

Share this post


Link to post
Share on other sites
On 9/27/2018 at 12:43 PM, gibsurfer84 said:

Does anyone have a script for deleting tickets older than 90 days? If the ticket has reached this age, we would consider it not sync'd with the plugin and worth manually killing in LT.

# Delete Tickets that are Closed and older than 90 days
DELETE FROM `tickets` WHERE `STATUS` = "4" AND `UpdateDate` < NOW() - INTERVAL 90 DAY;
                                                                   
# Delete Tickets that never synced to Manage and are older than 90 days
DELETE FROM `tickets` WHERE `ExternalID` = "0" AND `UpdateDate` < NOW() -INTERVAL 90 DAY;

# Delete info from labtech.ticketdata table if tickets.ticketID no longer exists in labtech.tickets
DELETE FROM `ticketdata` WHERE `ticketID` NOT IN (SELECT `ticketID` FROM tickets);

Pieces of my maintenance/cleanup script I use to dump old ticket info.

  • Like 2

Share this post


Link to post
Share on other sites

Here's a selection from my daily SQL maintenance script:

# Automatically classify drives as SSDs
UPDATE drives 
SET SSD='1' 
WHERE ( Model LIKE '%SSD%'
   OR Model LIKE '%NVMe%'
   OR Model LIKE 'SAMSUNG MZ%'
   OR Model LIKE 'SAMSUN  MZ%'
   OR Model LIKE 'SanDisk SD%'
   OR Model LIKE 'SK hynix%'
   OR Model LIKE '%LITEONIT LMT-256%'
   OR Model LIKE '%LITEONIT LCS-128%'
   OR Model LIKE '%LITEON LCH-128%'
   OR Model LIKE '%LITEON L8H-256%'
   OR Model LIKE 'MTFDDAK%'
   OR Model LIKE 'PLEXTOR PX%'
   OR Model LIKE '%Optane%'
   OR Model LIKE 'SD Card'
   OR Model LIKE '%SD-CARD%'
   OR Model LIKE '%SDXC Card%'
   OR Model LIKE '%Flash Drive%'
   OR Model LIKE '%Flash Disk%'
   OR Model LIKE '%Flash Memory%'
   OR Model LIKE 'hp v150w USB Device'
   OR Model LIKE 'SanDisk Cruzer%'
   OR Model LIKE 'SanDisk U3 Cruzer%'
   OR Model LIKE '%USB 2.0 FD%'
   OR Model LIKE '%Card Reader%'
   OR Model LIKE '%Card  Reader%' )
   AND SSD <> '1';

UPDATE drives 
SET INTERNAL='1' 
WHERE ( Model LIKE 'SAMSUN  MZ%'
   OR VolumeName LIKE 'Windows' )
   AND INTERNAL <> '1';

UPDATE drives 
SET INTERNAL='0' 
WHERE ( VolumeName LIKE 'HP_TOOLS'
   OR VolumeName LIKE 'HP_RECOVERY'
   OR Model LIKE '%USB%'
   OR SmartStatus LIKE 'USB%' )
   AND INTERNAL <> '0';

# Drop drives marked as "missing" from the database.
DELETE FROM drives WHERE missing=1;

# Reset hotfix status and re-allow push
UPDATE hotfix SET pushed='0' WHERE installed=0 AND pushed=1 AND approved=2;

# Cleanup h_scripts table
DELETE FROM `h_scripts`
WHERE HistoryDate < DATE_SUB(NOW(), INTERVAL (
    SELECT VALUE
    FROM properties
    WHERE NAME = 'RetentionHistoryScriptLogs') DAY);
OPTIMIZE TABLE h_scripts;

# Delete all ticket data older than 30 days
DELETE FROM tickets 
WHERE updateDate < NOW() - INTERVAL 30 DAY;
OPTIMIZE TABLE tickets;
DELETE FROM ticketdata 
WHERE ticketid NOT IN (SELECT ticketid FROM tickets);
OPTIMIZE TABLE ticketdata;

 

Edited by Leapo
  • Like 1

Share this post


Link to post
Share on other sites

Love the drive models for SSD's. If anyone has any others they know of, keep em coming!

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×