Expiry Table Insert Errors

[SamOrlando]

After getting it working on our local network, I picked a co-managed client so we can work together to get it implemented and see what value they can get out of the notices.

After doing some local powershell testing to make sure I didn't have the same issues, I was able to configure the plugin to get past the issues we had on our network. However, after letting it run the full script, it returns an error I don't quite understand.

Code: Select all

The Script(6319) failed in the Then section at step 17. The reason: SQL Execute failed processing query

Script: S6319 - Starting at Server Time: Wednesday, July 24, 2019 2:24:56 PM
IF F1  T: 0.3269673
L1 F129 Jump (P1): :WEARESERVER T: 0.3279672
L3 F139 Note (P1): :WEARESERVER T: 0.3279672
L4 F109    Delimiter (P2):    Variable (P3): PoShVersion T: 0.3289671
L5 F70 Value (P1): PoShVersion   Comparer (P2): 6   To (P3): 2   Jump (P4): :EXECUTE T: 8.3301669
L8 F139 Note (P1): :EXECUTE T: 8.3301669
L9 F172 SqlStatement (P1): Select LDAPRoot FROM plugin_sw_expiry_configure WHERE ClientID = 10 T: 8.3301669
L10 F20 SetType(P1):1   Parameter(P2):OU=CIG Users,DC=cig-int,DC=net   VariableName(P3):LDAPRoot T: 8.3311668
L11 F90 File (P1): c:\windows\ltsvc\expiryAD.ps1 T: 8.3321667
L12 F109    Delimiter (P2):    Variable (P3): PoShCMD T: 10.3409658
L13 F114 File (P1): C:\windows\ltsvc\expiryAD.sql   Check (P2): 1   Jump (P3): :LDAPFAILED T: 20.3419656
L14 F20 SetType(P1):7   Parameter(P2):C:\windows\ltsvc\expiryAD.sql   VariableName(P3):SQLDATA T: 26.3443653
L15 F67 Sql (P1): UPDATE plugin_sw_expiry_configure SET LastRun = NOW() WHERE ClientID = 10 T: 32.3457651
L16 F67 Sql (P1): Delete from plugin_sw_expiry_email WHERE ClientID = 10 T: 32.3457651
L17 F67 Sql (P1): 'INSERT INTO  plugin_sw_expiry_email (ClientID, UPN, Name, DaysLeft) Values  (10, 'Jennifer...' T: 32.3477649

So it is successfully running the Powershell portion and creating the expirtyAD.sql file. But now it looks like it is having issues parsing the data and inserting it into the Expiry table.

[SamOrlando]

I just confirmed our local one is still working after Automate Patch 7 was applied and we migrated our hosted server from AWS West to AWS East. So I don't think either of those events are the cause.

Reviewed the expiryAD.sql doc and the first few lines as an example read as:

Code: Select all

(10, 'Jennifer.Garry@example.com', 'Jennifer Garry', '31')
,(10, 'Lisa.Atkins@example.com', 'Lisa A  Atkins', '73')
,(10, 'Polly.Beeler@example.com', 'Polly A  Beeler', '32')

[SamOrlando]

Just configured Expiry for another client and it ran and updated the user list and expiration days fine in the plugin.

[Cubert]

I need to see the entire SQL file for the failing client. I suspect there maybe a item in the file that is breaking the SQL query. The first few lines look accurate but I will need the entire view.

Can you post that here?

[SamOrlando]

Code: Select all

(10, 'Jennifer.Garry@digestivehs.com', 'Jennifer Garry', '31')
,(10, 'Lisa.Atkins@digestivehs.com', 'Lisa A  Atkins', '73')
,(10, 'Polly.Beeler@digestivehs.com', 'Polly A  Beeler', '32')
,(10, 'Jacki.Smith@digestivehs.com', 'Jacki J  Smith', '178')
,(10, 'David.Schulman@digestivehs.com', 'David Schulman', '119')
,(10, 'Sherry.Thomas@digestivehs.com', 'Sherry Thomas', '66')
,(10, 'Melissa.Mata@digestivehs.com', 'Melissa K  Mata', '174')
,(10, 'jennifer.anderson@digestivehs.com', 'Jennifer Anderson', '68')
,(10, 'will.ellsworth@digestivehs.com', 'Will Ellsworth', '18')
,(10, 'Marcella.Medlock@digestivehs.com', 'Marcella Medlock', '89')
,(10, 'Rishi.Grewal@digestivehs.com', 'Rishi Grewal', '89')
,(10, 'Jonathan.Horwitz@digestivehs.com', 'Jonathan P  Horwitz', '67')
,(10, 'Sheila.Schulz@digestivehs.com', 'Sheila L  Schulz', '19')
,(10, 'Debra.Fieth@digestivehs.com', 'Debra J  Fieth', '59')
,(10, 'Janie.Parks@digestivehs.com', 'Janie Parks', '17')
,(10, 'Syed.Khalid@digestivehs.com', 'Syed M  Khalid', '66')
,(10, 'Chun.Hsu@digestivehs.com', 'Chun Hsu', '54')
,(10, 'Donald.Clement@digestivehs.com', 'Donald J  Clement', '52')
,(10, 'Jessica.Thompson@digestivehs.com', 'Jessica Thompson', '49')
,(10, 'Tara.Linn@digestivehs.com', 'Tara R  Linn', '123')
,(10, 'Yan.Chen@digestivehs.com', 'Yan Chen', '61')
,(10, 'Steven.Kaster@digestivehs.com', 'Steven D  Kaster', '-750')
,(10, 'Thomas.Shireman@digestivehs.com', 'Thomas J  Shireman', '52')
,(10, 'Gregory.Barber@digestivehs.com', 'Gregory B  Barber', '-94')
,(10, 'Catherine.Linsey@digestivehs.com', 'Catherine A  Linsey', '10')
,(10, 'Joseph.Eisenach@digestivehs.com', 'Joseph B  Eisenach', '44')
,(10, 'Alison.Prentice@digestivehs.com', 'Alison L  Prentice', '152')
,(10, 'Holly.Messina@digestivehs.com', 'Holly Messina', '124')
,(10, 'Andrea.Wyatt@digestivehs.com', 'Andrea Wyatt', '11')
,(10, 'Thomas.Jones@digestivehs.com', 'Thomas F  Jones', '179')
,(10, 'Dale.Wytock@digestivehs.com', 'Dale H  Wytock', '31')
,(10, 'Adrienne.Nguyen@digestivehs.com', 'Adrienne Nguyen', '81')
,(10, 'Paula.Bennett@digestivehs.com', 'Paula J  Bennett', '38')
,(10, 'Kristi.Ott@digestivehs.com', 'Kristi Ott', '101')
,(10, 'Kristin.White@digestivehs.com', 'Kristin White', '-413')
,(10, 'Rita.Hays@digestivehs.com', 'Rita R  Hays', '67')
,(10, 'Tammy.Stewart@digestivehs.com', 'Tammy S  Stewart', '179')
,(10, 'Traci.Solomon@digestivehs.com', 'Traci P  Solomon', '66')
,(10, 'Susan.Dugger@digestivehs.com', 'Susan A  Dugger', '171')
,(10, 'Susan.Balthrop@digestivehs.com', 'Susan Balthrop', '136')
,(10, 'Kimberly.Wood@digestivehs.com', 'Kimberly D  Wood', '137')
,(10, 'Jane.Martino@digestivehs.com', 'Jane Martino', '124')
,(10, 'Larry.Lawson@digestivehs.com', 'Larry D  Lawson', '103')
,(10, 'Wanda.Gooch@digestivehs.com', 'Wanda S  Gooch', '165')
,(10, 'Karen.Brink@digestivehs.com', 'Karen S  Brink', '24')
,(10, 'Kelly.Bradley@digestivehs.com', 'Kelly A  Bradley', '87')
,(10, 'Michelle.Chandler@digestivehs.com', 'Michelle A  Chandler', '90')
,(10, 'Shannah.McGee@digestivehs.com', 'Shannah N  McGee', '66')
,(10, 'Lori.McCrea@digestivehs.com', 'Lori McCrea', '89')
,(10, 'Kandace.Donovan@digestivehs.com', 'Kandace Donovan', '127')
,(10, 'Gwen.Patterson@digestivehs.com', 'Gwen J  Patterson', '32')
,(10, 'Carla.Merriott@digestivehs.com', 'Carla Merriott', '52')
,(10, 'Peggy.Christman@digestivehs.com', 'Peggy J  Christman', '33')
,(10, 'Amy.Ford@digestivehs.com', 'Amy S  Ford', '178')
,(10, 'Audra.Riechers@digestivehs.com', 'Audra A  Riechers', '59')
,(10, 'Charlene.Hudson@digestivehs.com', 'Charlene J  Hudson', '18')
,(10, 'Teresa.Hubbard@digestivehs.com', 'Teresa Hubbard', '53')
,(10, 'Marva.Keeth@digestivehs.com', 'Marva Keeth', '59')
,(10, 'Janis.Wright@digestivehs.com', 'Janis R  Wright', '52')
,(10, 'Sharon.Diddle@digestivehs.com', 'Sharon L  Diddle', '63')
,(10, 'Connie.Mesa@digestivehs.com', 'Connie L  Mesa', '38')
,(10, 'heather.collins@digestivehs.com', 'Heather Collins', '66')
,(10, 'Dale.Farrell@digestivehs.com', 'Dale Farrell', '-33')
,(10, 'Tomisha.Woodruff@digestivehs.com', 'Tomisha Woodruff', '66')
,(10, 'Janice.Muzingo@digestivehs.com', 'Janice D  Muzingo', '178')
,(10, 'Toni.Johnson@digestivehs.com', 'Toni Johnson', '82')
,(10, 'Misty.Moyen@digestivehs.com', 'Misty Moyen', '42')
,(10, 'Aubry.Jackson@digestivehs.com', 'Aubry Jackson', '-11')
,(10, 'Shellie.Triboulet@digestivehs.com', 'Shellie Triboulet', '171')
,(10, 'Lisa.Barr@digestivehs.com', 'Lisa Barr', '164')
,(10, 'Francis.McCormick@digestivehs.com', 'Francis P  McCormick', '66')
,(10, 'Scott.McGuire@digestivehs.com', 'Scott E   McGuire', '66')
,(10, 'Theresa.Maxwell@digestivehs.com', 'Theresa Maxwell', '171')
,(10, 'Olivia.McCoy@digestivehs.com', 'Olivia McCoy', '133')
,(10, 'Lindsay.Ingels@digestivehs.com', 'Lindsay Ingels', '153')
,(10, 'Jennifer.Murphy@digestivehs.com', 'Jennifer Murphy', '80')
,(10, 'Michael.Hagan@digestivehs.com', 'Michael Hagan', '131')
,(10, 'Kimberly.Bellof@digestivehs.com', 'Kimberly Bellof', '21')
,(10, 'Amy.Waller@digestivehs.com', 'Amy Waller', '73')
,(10, 'Andrea.Gray@digestivehs.com', 'Andrea Gray', '179')
,(10, 'Jayne.Cole@digestivehs.com', 'Jayne Cole', '164')
,(10, 'Brandi.Garibay@digestivehs.com', 'Brandi Garibay', '157')
,(10, 'Debbie.Gasaway@digestivehs.com', 'Debbie Gasaway', '171')
,(10, 'Frank.Totta@digestivehs.com', 'Frank Totta', '-2')
,(10, 'Hilary.Hartwig@digestivehs.com', 'Hilary Hartwig', '158')
,(10, 'Jackie.Hatfield@digestivehs.com', 'Jackie Hatfield', '7')
,(10, 'Jessica.McCoy@digestivehs.com', 'Jessica McCoy', '150')
,(10, 'Julie.Hunt@digestivehs.com', 'Julie Hunt', '157')
,(10, 'Karen.Baxter@digestivehs.com', 'Karen Baxter', '152')
,(10, 'Katie.Kleffner@digestivehs.com', 'Katie Kleffner', '157')
,(10, 'Lauren.Davis@digestivehs.com', 'Lauren Davis', '178')
,(10, 'Lindsay.Walz@digestivehs.com', 'Lindsay Walz', '171')
,(10, 'Lora.Watson@digestivehs.com', 'Lora Watson', '10')
,(10, 'Mallory.Grassmuck@digestivehs.com', 'Mallory Grassmuck', '171')
,(10, 'Marilyn.Hamilton@digestivehs.com', 'Marilyn Hamilton', '172')
,(10, 'MaryLynne.Lucido@digestivehs.com', 'Mary Lynne Lucido', '10')
,(10, 'Nanette.Ham@digestivehs.com', 'Nanette Ham', '157')
,(10, 'Rhonda.Thompson@digestivehs.com', 'Rhonda Thompson', '45')
,(10, 'Todd.Kilgore@digestivehs.com', 'Todd Kilgore', '180')
,(10, 'Marta.Beal@digestivehs.com', 'Marta Beal', '56')
,(10, 'Vadsana.Smith@digestivehs.com', 'Vadsana Smith', '70')
,(10, 'Leslie.Ragan@digestivehs.com', 'Leslie Ragan', '73')
,(10, 'Chrystal.Simpson@digestivehs.com', 'Chrystal Simpson', '80')
,(10, 'Samantha.Sharp@digestivehs.com', 'Samantha Sharp', '123')
,(10, 'Kelly.Gawatz@digestivehs.com', 'Kelly Gawatz', '54')
,(10, 'Victoria.Bennetzen@digestivehs.com', 'Victoria Bennetzen', '3')
,(10, 'Julie.Camp@digestivehs.com', 'Julie Camp', '119')
,(10, 'Kimberly.Deck@digestivehs.com', 'Kimberly Deck', '')

[Cubert]

Hmm..

I found no SQL issues with that information

If you add to the top of your SQL code

Code: Select all

INSERT INTO plugin_sw_expiry_email Values 

And paste your data below this line and run it in SQL does it spit out an error for you? We were successful in adding those users to our expiry table. Reran to verify updates worked as well.


If you pull the error files from the server is there a plugin_expiry_error.txt file in the logs folder of the server?

[SamOrlando]

If you mean run it in SQL on our Automate server, we can't since they removed access to hosted servers.

[Cubert]

Lol...

Actually you can.

We made a plugin for that purpose. Hosted LT server MSP can now get SQL access to the environment. Run any SQL commands you like.

https://www.plugins4automate.com/produc ... y-analyzer

Maybe of help with your environment for all sorts of things moving forward.