Jump to content

TonyPags

Members
  • Content Count

    22
  • Joined

  • Last visited

Community Reputation

1 Neutral

My Information

  • Location
    NYC
  • Agent Count
    > 6000 Agents

Converted

  1. Sir Darren sent me this on Slack, so I can't take credit. Putting it here for eternity. >> Just poking at (link to my comment): The function you want is called FIND_IN_SET .. I haven't tested this, but it should be something like this: -- I DID NOT TEST THIS YET DELIMITER $$ CREATE TRIGGER `mycompany_computers_illegalMACs` BEFORE UPDATE ON `labtech`.`computers` FOR EACH ROW BEGIN IF( SELECT COUNT(*)=0 FROM lt_web_servers_config WHERE FIND_IN_SET(NEW.mac,MACSignupBlackList)>0 ) AND OLD.mac IS NOT NULL THEN SET NEW.mac = OLD.mac; END IF; END$$ DELIMITER ; Since my current trigger is working so well, I don't have this high on my to-do list. I DID NOT TEST THIS! So you don't have to scroll up, this will grab the MACs from the blacklist under advanced config, instead of hard-coding the values into the trigger and updating it whenever you want to add another MAC.
  2. Everything worked great, in tests. When it came time for LT script engine to run the query that changes the value, it fails. I set the query to a variable and ran that while logging it, to ensure the query is what I expect, looks good, still fails. Pulling the query the script used from the log and running it in mysql worked fine. Any known issues doing this in the LT function?
  3. Yes sir, this table grouped by stage looks like it matches reality. I just hope I can edit these values and it will work without any other changes. Since it's not an EDF value I should be careful here. To the lab...
  4. I'm struggling to build a working script that changes the patching stage on a device (test/pilot/production). This should be easy, like any EDF. ExtraData set value does not work. Looking deeper into the issue, the table with extrafielddata doesn't match device patching screens. So where is the data really stored? You might be tempted to say, "ummm, Tony, all EDFs are in extrafielddata," to which I would reply "Yes Darren 😁 I already tried using the extradata set value function and selecting the patching stage item. That didn't seem to work. I can see the new record in the extrafielddata table, but this has no effect on the device patching screen....that still says the original value. Yes, I have force-updated the EDF views using call v_ExtraDataRefresh(1,'Computers'); which updates the v_extradatacomputers table. But this table has values for Patching Stage that don't jive with reality." Is there another spot it's stored? Are we sure there isn't another, newer field for this? I should have a lot more records in that table for this field that I do. I tried running sql spy but that didn't yield anything helpful. I tried looking for a dataview that had patching stage so I could dissect it. No luck. Did I miss it? The only way I've been able to batch-change this setting is by shift-clicking devices under the PM GUI. Manually. With my MOUSE! The horror...
  5. Yeah thanks. I do see some 2-hour gaps in that first log. Nothing suspect in the other two. I'll open a ticket with them about this. You probably saved me a few days of back-and-forth with them, so thanks!
  6. Hi all, We have auto-join groups and I was making some config changes earlier this morning that affected the membership of these groups. Pretty standard EDF stuff. Later in the day, well after that 26min refresh that is supposed to happen automatically, I see these devices I made changes to in the group listed as "not in group" but "will join group". I forced it by clicking the auto-join now button. And that seems to work OK, as expected. How do I check to see if this automatic group thing is working as it should?
  7. What if a "non-functional" agent is functional? Do I reinstall that agent and make sure it's re-registered under a new ID or something? Also, 2a makes no sense to me, why are we using old password moving forward?
  8. 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.
  9. Good idea. Not sure how I would accomplish that but worth a ponder.
  10. 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.
  11. 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.
  12. 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.
  13. I thought the inv_networkcard table was used as reference during MAC signup?
  14. @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.
×
×
  • Create New...