Jump to content

TonyPags

Members
  • Content Count

    15
  • Joined

  • Last visited

Community Reputation

1 Neutral

My Information

  • Location
    NYC
  • Agent Count
    > 6000 Agents

Converted

  1. I have a status to report. After adding the trigger a week ago, I was down to 1 remaining record using an illegal MAC address today. Not too shabby! I manually updated that record and now I have 0. So I can finally test to see if that inv_ table I mentioned has any effect on agent signup. I have disabled my script which purges illegal MACs from that inv table and I waited for it to repopulate. While waiting, I checked out the h_agentsignups table for issues. -- Get-MacSignupIssues SELECT COUNT(h.computerid) AS Count, h.Computerid, h.clientid,GROUP_CONCAT(DISTINCT h.name) AS Names,GROUP_CONCAT(DISTINCT h.user) AS Users, GROUP_CONCAT(DISTINCT h.mac) AS MACs, h.errorlevel,h.errormessage,h.ActionDate FROM h_agentsignup AS h JOIN computers AS c ON h.ComputerID=c.ComputerID WHERE h.computerid > 0 AND h.MAC IN ('00-05-9A-3C-7A-00','6C-4B-90-09-1D-D4','B4-AE-2B-3A-A2-9B','00-FF-22-44-A7-CF','00-09-0F-FE-00-01','02-50-41-00-00-01') AND c.LastContact > DATE_SUB(NOW(), INTERVAL 15 MINUTE) GROUP BY h.computerid HAVING COUNT(h.computerid) > 1 ORDER BY COUNT(h.computerid) DESC, h.ActionDate DESC; Sure enough, I see only 1 issue in the last day and the ID matches the item I manually edited today. That is just a sanity check. Tired of waiting, I resent network info and verified the inv table had that nasty Cisco AnyConnect MAC present. Then I remoted into another device to reset the agent registration. # This will reset everything: MAC, ID, and LocationID. (new-object Net.WebClient).DownloadString('https://bit.ly/ltposh') | iex; Reset-LTService After doing that I get no ID in the result. The device was not added to Automate. This is because the MAC address is blacklisted. The MAC blacklist works when there are no records in the database already! Finally! LTService v190.333 - 6/4/2020 12:42:53 PM - Registered LTService for remoting on 42000.::: LTService v190.333 - 6/4/2020 12:42:53 PM - REMOTING PORT IS:42000::: LTService v190.333 - 6/4/2020 12:42:53 PM - INIT CLR:4.0.30319.42000::: LTService v190.333 - 6/4/2020 12:42:53 PM - Platform\OS: Win32NT\10 0::: LTService v190.333 - 6/4/2020 12:42:53 PM - Janus Enabled based on Windows Version::: LTService v190.333 - 6/4/2020 12:42:53 PM - Using Standard Event Log Engine::: LTService v190.333 - 6/4/2020 12:42:56 PM - Status Result Error: ::: LTService v190.333 - 6/4/2020 12:43:10 PM - Attempting SignUp::: LTService v190.333 - 6/4/2020 12:43:21 PM - Failed Signup, Will wait over 30 minutes to try again.::: Also, the inv table has no effect! It's nice to have empirical results. Makes me feel warm and safe. Probably also makes OP happy too (?) It seems like when registration fails, it will keep retrying twice an hour until that VPN connection drops and the MAC is back to the hardware one. Now I just have to figure out a way to run that reset command on all rest of the endpoints with the agent ID sharing issue. I'm thinking a script to schedule a task to run the command. But that's for another day.
  2. Good idea. Not sure how I would accomplish that but worth a ponder.
  3. I added my condition to OP's trigger to avoid manually editing the list of MACs every time you want to change it. Instead it pulls the list from the MAC Signup Blacklist, which is the native place to store these MACs. DELIMITER $$ CREATE TRIGGER `mycompany_computers_illegalMACs` BEFORE UPDATE ON `labtech`.`computers` FOR EACH ROW BEGIN IF( SELECT CONCAT( '\'', REPLACE( ( SELECT MACSignupBlackList FROM lt_web_servers_config WHERE MACSignupBlackList LIKE '%-%' ), ',', '\',\'' ), '\'' ) ) LIKE CONCAT('%','\'',NEW.mac,'\'','%') AND OLD.mac IS NOT NULL THEN SET NEW.mac = OLD.mac; END IF; END$$ DELIMITER ; Here I am pulling a single cell value (which is the comma-separated list of MACs in the blacklist) from the config table. Because MySQL has no SPLIT function, I had to be a little creative to get the conditional to work properly: Replacing all commas with a single quote and a comma and another single quote. Concatenating that result with one more single quote on each end to make the long string. Comparing the long string with the new MAC, after adding in the wildcards and the single quotes to match our long string's format. Everything else is the same. Except the trigger name I changed for my own purposes.
  4. Hey @Automation Theory I had a case where an agent DEFINITELY used the computers table MAC value for Mac signup, like you said. I have a few questions about this trigger for you: Are you running it, how is it working? What maintenance is required for it? For example, when you upgrade your server instance, do you verify it is still present and functional? How? I still want to find a case where I can test the opposite case: if no MACs exist in the computers table but do in the inv tables, will Mac Signup look at both tables for a match? But without a safe way to clear out the computers table of bad MACs (like your trigger would do, over time), I won't have a way to test that case.
  5. I think your trigger is good to keep those MACs out of the computers table. But I swear I had a senior tech tell me it was the inv table. Maybe it's both, in a cascade. Just thinking out loud. I'll share something I have that pulls the list of MACs from the blacklist. Maybe you can incorporate the subquery on the blacklist into your trigger, to make it a little more automatic. (I've commented out the inv table and replaced it with computers a la your trigger). SELECT * FROM computers -- SELECT * FROM inv_networkcard WHERE mac IS NOT NULL AND mac != '' AND ( SELECT CONCAT('\'', REPLACE((SELECT MACSignupBlackList FROM lt_web_servers_config WHERE MACSignupBlackList LIKE '%-%'),',','\',\''), '\'') ) LIKE CONCAT('%','\'',mac,'\'','%') I've been automating cleanup of that inv table with an adaptation of my above query. I've replaced my normal FROM clause with the table from your example. I run this and get a lot more results on the computers table. It will be interesting to see if I test against this to see if it's using one, the other, or both of these tables. =-=-=-= There are inconsistencies with that document you've linked. Within the blue box that has the query, they refer to the limit number as being time in seconds AND minutes. I've also had senior support reps tell me this number is measured in DAYS, and also told me everyone thinks this setting does something different and that really, no one knows how it works exactly. I take all documented knowledge with a grain of salt. The doc also says that -1 forces agents to sign up as new devices but that is not how my server behaves using this setting, in fact it's the opposite. I saw your post and was curious because I had exactly the same idea, to make a trigger, but on the inv table! I'll have to do some reverse engineering, and design a case to test the edges of this blackbox logic. =-=-=-= Do you know where they hide the MAC Signup function or stored procedure in the DB? That would be so much easier, to settle this for me.
  6. I thought the inv_networkcard table was used as reference during MAC signup?
  7. @Dave M, I made a RAWSQL internal monitor that looks up the MAC Blacklist as entered under the advanced server config (the WHERE clause below). Just add your MACs there and run this on All Agents. You could also hard-code your own list in the query, or pull it from a table somewhere, for example. Select DISTINCT Computers.MAC as TestValue, computers.computerid As IdentityField, computers.computerid, computers.Name as ComputerName, Convert(CONCAT(clients.name,' ',locations.name) Using utf8) As Location, acd.NoAlerts, acd.UpTimeStart, acd.UpTimeEnd FROM computers LEFT JOIN Locations ON Locations.LocationID=computers.Locationid LEFT JOIN Clients ON Clients.ClientID=Computers.clientid JOIN AgentComputerData AS acd on Computers.ComputerID=acd.ComputerID WHERE (SELECT MACSignupBlackList FROM lt_web_servers_config WHERE MACSignupBlackList LIKE '%-%') LIKE CONCAT('%',MAC,'%') AND NOT Locations.LocationID=581 AND Computers.ComputerID IN (Select Distinct ComputerID From Tcomp) AND Computers.ComputerID NOT IN (Select ComputerID from AgentIgnore Where AgentID=294883) Please note on the 3rd to last line I have something custom for my env (581 is a location I exclude from this check). Remove or modify this line for your own needs. I made this because when you blacklist a MAC, but an agent in the system already has that MAC, the blacklist doesn't block the device, since MAC signup is flaming trash.
  8. So I was able to get the reports imported and published, but I cannot run them. To get them imported I had to put back the duplicate column and keep the DomainIfoLastUpdated column named as DomainIfoLastUpdated. But something wonky happened when pasting the text from this post into Yog. Even if I used a plain text to paste and re-copy before setting that up. No matter what I did, the view would not be created, it kept giving me the error I originally got above. I had to take your query and paste it as-is into MySQL Workbench and use the intellisense there, to rewrite the lines that spit back an error. Annoying but not too difficult. What's really weird is that it gave me a duplicate column error yesterday, but today it didn't. Now, when I run the reports, all 3 of them give me this error: "Could not find file c:\Program Files (x86)\LabTech Client\PageHeaderLandscape [long GUID]'. And I confirmed this file does not exist on my PC, the web server, nor the DB server (I am split server). I re-imported the header and that didn't help any. Any ideas on that path error?
  9. Hi @rmuell01, Yes I did create the views after having some trouble (as described above). There was 1 column I had to remove from the SELECT clause in the first view, because it was a duplicate and caused an error. I don't think this would affect anything down the line since a column is a column and it doesn't matter what order they are in. Actually looking at this again, that might be it. The aliases are different. `plugin_ad_domains`.`LastUpdate` AS `lastupdate`, `plugin_ad_domains`.`LastUpdate` AS `DomainIfoLastUpdated`, Which one is right? "DomainIfoLastUpdated" looks like a typo as well. Should it be "DomainInfoLastUpdated"?
  10. I hit another roadblock importing the REPX files. The subreport imported without issue. The 3 other reports failed with a message of "There was an error loading the report. Please verify that the report is in a valid format and try again." I tried reloading the report center, no joy. I tried reloading system caches for Control Center, no joy. I closed the Control Center and cleared the caches using this script. ############# clear LTCC ################ # Close LTCC $LTCC = Get-Process ltclient -ea 0 if($LTCC){$LTCC|Stop-Process -Force} # Remove Reg Keys Get-childItem 'HKCU:\Software\LabTech\Client\Lists' | Remove-Item -Confirm:$false # Delete folders Get-Item 'C:\ProgramData\Labtech Plugins' -ea 0 | Remove-Item -Recurse -Confirm:$false -ea 0 Get-Item 'C:\ProgramData\Labtech Client\Cache' -ea 0 | Remove-Item -Recurse -Confirm:$false -ea 0 ########## end clear LTCC ################ ...no joy. I tried going directly to my web server and running the Control Center from there and importing the file, no joy. I am out of ideas.
  11. Thanks @rmuell01, I was having trouble creating views. The execute button was grayed out in the create view tab. I created a new tab and pasted the above into it. It turns out when I paste your query into the SQL editor, it's saying that the columns do not exist, but there they are as plain as day. I had my DBA look at it and he was stumped too. I took the select subquery and tried to run it, it gives errors. I tried multiple devices and clients all with an error related to the aliases. I noted that the aliases were not really needed (except for the GROUP_CONCAT function) and I took them out. Then started getting unknown column errors. I when thru line by line and recreated the columns using intellisense auto complete. Then it broke on ContactID and I couldn't get it to work by retyping it out. Error Code: 1054. Unknown column 'plugin_ad_users.ContactID' in 'field list' Then I tried about 3 more times with intellisense and it worked on the 4th try. Adding this in case someone else has the same problem but I think it's my PC, it's running slow today so maybe that had something to do with it. If at 4th you don't succeed, try again. EDIT: turns out there was also a duplicate column: plugin_ad_domains.LastUpdate
  12. You have to make a custom report for that date range (like within the last 30 days, or similar). You would add a WHERE statement to the appropriate table(s) under Data Sources. Also, Happy Halloween! 👻
  13. Hi, we had to remove ESET before installing our own AV at a few of our clients. The issue was that it couldn't be done without the uninstall password, and the only workaround was to reboot into safe mode and run a script provided on the ESET support site to remove it without the password. We eventually contacted the old MSP and were able to work with them to re-hook up their ESET plugin into our platform and push the uninstall command, that got most of them. They couldn't give us the password. It was a manual removal by field techs to get the remaining ones. Not a fun project. For your specific issue, you might want to reach out to ESET support or check their website. I would assume they have something already, given the above experience.
  14. Man this looks really cool, but the ZIP files I found on this thread have SQL in them that doesn't seem to do anything other than drop 4 views that aren't there by default, and a lot of commented out stuff. I just looked at the code, didn't run it. Is it possible to re-post the original ZIP? I'm on 12.0.12 and latest RC, if that kills my chances.
×
×
  • Create New...