Jump to content

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

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.

11. INSERT INTO CacheActions (`UserID`,`ID`,`Action`,`HistoryDate`, `SecondaryIdentifier`, `Description`) VALUES (1,0,17,NOW(),'','')

        This makes the CWC plugin update the client name property for computers. It is the command performed by the "Assign Company/Site Names to Sessions" button in the dashboard>config>integration>CWC tab does

Edited by Slartibartfast
New daily step discovered.
  • Like 3
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.

 

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)

Link to post
Share on other sites
  • 4 weeks later...
  • 1 month later...
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
Link to post
Share on other sites
  • 2 months later...
  • 3 weeks later...

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 2
Link to post
Share on other sites
  • 7 months later...

Two more we added recently for keeping Network Devices clean:

# Auto-purge network devices that have not been seen in more than 30 days
DELETE FROM networkdevices WHERE LastContact < DATE_ADD(NOW(),INTERVAL -30 DAY);

# Auto-purge duplicate network devices
DELETE FROM networkdevices 
WHERE networkdevices.DeviceID IN (
    SELECT DISTINCT DeviceID FROM (
        SELECT t1.*
        FROM networkdevices AS t1
        JOIN (SELECT LocationID, IPAddress
            FROM networkdevices
            GROUP BY LocationID, IPAddress
            HAVING count(*) > 1) AS t2
        ON t1.LocationID = t2.LocationID AND t1.IPAddress = t2.IPAddress
        ORDER BY LocationID,IPAddress,CreationDate ASC
    ) AS DevicesWithDupes
)
AND networkdevices.DeviceID NOT IN (
    SELECT DISTINCT DeviceID FROM (
        SELECT t1.* FROM networkdevices t1
            JOIN (SELECT IPAddress, MAX(CreationDate) CreationDate 
                FROM (SELECT t1.*
                    FROM networkdevices AS t1
                    JOIN (SELECT LocationID, IPAddress
                        FROM networkdevices
                        GROUP BY LocationID, IPAddress
                        HAVING count(*) > 1) AS t2
                    ON t1.LocationID = t2.LocationID AND t1.IPAddress = t2.IPAddress) t3
                GROUP BY IPAddress) t2
            ON t1.IPAddress = t2.IPAddress AND t1.CreationDate = t2.CreationDate
            ORDER BY LocationID,IPAddress,CreationDate ASC
    ) AS DevicesToKeep
);

 

Edited by Leapo
  • Thanks 1
Link to post
Share on other sites
On 9/30/2020 at 5:17 AM, Leapo said:

Two more we added recently for keeping Network Devices clean:


# Auto-purge network devices that have not been seen in more than 30 days
DELETE FROM networkdevices WHERE LastContact < DATE_ADD(NOW(),INTERVAL -30 DAY);

# Auto-purge duplicate network devices
DELETE FROM networkdevices 
WHERE networkdevices.DeviceID IN (
	SELECT DISTINCT DeviceID FROM (
		SELECT t1.*
		FROM networkdevices AS t1
		JOIN (SELECT LocationID, IPAddress
		      FROM networkdevices
		      GROUP BY LocationID, IPAddress
		      HAVING count(*) > 1) AS t2
		ON t1.LocationID = t2.LocationID AND t1.IPAddress = t2.IPAddress
		ORDER BY LocationID,IPAddress,CreationDate ASC
	) AS DevicesWithDupes
)
AND networkdevices.DeviceID NOT IN (
	SELECT DISTINCT DeviceID FROM (
		SELECT t1.* FROM networkdevices t1
			JOIN (SELECT IPAddress, MAX(CreationDate) CreationDate 
					FROM (SELECT t1.*
							FROM networkdevices AS t1
							JOIN (SELECT LocationID, IPAddress
							      FROM networkdevices
							      GROUP BY LocationID, IPAddress
							      HAVING count(*) > 1) AS t2
							ON t1.LocationID = t2.LocationID AND t1.IPAddress = t2.IPAddress) t3
					GROUP BY IPAddress) t2
			ON t1.IPAddress = t2.IPAddress AND t1.CreationDate = t2.CreationDate
			ORDER BY LocationID,IPAddress,CreationDate ASC
	) AS DevicesToKeep
);

 

Nice - I just did some testing and I am definitely getting some different devices showing in that duplicate device query.

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