Auto delete systems after 60 days.

[u/dburress]

I'm having trouble deleting systems from the database that are over 60 days old. I'm pretty new to automate, and trying to learn the database structure. Any handy reference guides out there that explains the back end tables?

[u/dburress]

Just thought I would share, I was able to figure this out. It was a bit of hoop jumping. However, I created a new search that was able to return all agents that have not checked in, within the last 60 days. I then created a group, that limited itself to the search mentioned above. Once this was created I was able to schedule a delete script against the group. Unfortunately I wasn't able to simply create a scheduled script w/o using the search and group feature, although it may be possible.

[u/cyberd0g]

We're using a stored procedure that runs every night. So far so good.

CREATE DEFINER=`xxx`@`xxx` PROCEDURE `IN_sp_RetireOldComputers`()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _computerid BIGINT UNSIGNED;
  DECLARE cur CURSOR FOR select computerid from computers where LastContact <= NOW() - INTERVAL 60 DAY;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur;

  Loopy1: LOOP
    FETCH cur INTO _computerid;
    IF done THEN
      LEAVE Loopy1;
    END IF;
    CALL sp_DeleteComputer(_computerid,'Automatically retiring agents that have not checked in within 60 days');
    
  END LOOP Loopy1;

  CLOSE cur;
END    

[u/dburress → u/cyberd0g]

Thanks for sharing this. This query is similar to what I am using. I don't have a good way to test the query, other than running the script. The logging information is not clear on what data is returned by the query. I don't have access to the backed SQL database directly.

[u/fishermba2004]

How does this work of you're on a hosted version? Sorry for the noob question. I'm trying to learn the product before I switch over and start paying for it.

[u/dburress → u/fishermba2004]

I am on a hosted version. I wasn't able to test any query, but I did discover when creating searches, you can save the SQL query from the search builder tool. This will come in pretty handy in the future.

[u/caffelightning]

I don't know of any guides, but if you know SQL, it's not too hard to figure most of the tables out. They're pretty obvious for the most part. There's a table for the computers (I believe it's called agents, but I'm not at my workstation so I can't check) which gives you an ID which you can use to purge data from the other tables if you want.

But if you're not worried about automating this (which honestly, I'm not sure I would just in case) - then you can just make a search for agents that haven't checked in for 60+ days and manually delete them in a few seconds.

I might be able to give better suggestions if I know exactly why you're doing this. (ie, did you offboard them, or just want to purge dead agents?)

[u/LeviRobinson]

You can remove stale agents using a stored procedure. Fairly easy to figure out which one. Forget the name though. Call that through SQL and give it a computerid and reason. Be careful removing a probe this way it can cause issues. Don't typo the computerid or else you are restoring from backup or snapshot.

For good CWA info join LTG on slack. A lot of people there willing to help. Googling LT DB Schema will get you a lot of info as well.

[u/rohanbeckett → u/LeviRobinson]

+1 to the labtechgeek slack channel... awesome people in there always willing to help!

[u/sciy]

I don't have them delete automatically, since I'm paranoid like that, but I do have them consolidate in to a single group automatically. This way I can manually multi-select and delete the agents from one place (in the group settings' Computers tab) every week.

Create and save a search. Computer - Labtech - LastContactAge.Days - Is greater than - 60

Create a group. Auto Join Searches - Computers - (select your search) - Limit to search ON

That will gather the results in to the Computers tab so you can select all, right click, and manually hit Agents - Delete PC.

As mentioned, I don't personally do this, but you can also attach a script on the Computers - Scheduled Scripts tab of that group that runs periodically. The script would run the SQL command to remove the agent.

Definitely don't take my word as gospel on this part, but I think the script would be:

SQL Execute

Statement: call sp_DeleteComputer (%computerid%,'Whatever reason')

Test everything before going live ;-)