PowerShell

HOWTO: Cleaning up orphaned Appx packages that prevent #Sysprep from running on #Windows Server 2019

@DeanColpittsLeave a comment

Recently, I had a customer who needed to expand the number of Windows Server 2019 instances in their Citrix Virtual Apps pool to be able to support more work from home staff. As the base image was rather complex with many traditional apps (and absolutely no Appx packages in use), rather than manually build a new 2019 server image, I decided to clone one of the existing servers and then just sysprep it. However, when I ran sysprep on the cloned server – it not surprisingly failed. The reason I say not surprisingly is because the base instances were all originally Windows Server 2016 which had over time been inplace upgraded to 2019. And we all know that Microsoft doesn’t support sysprep on inplace upgraded OSes anyways. Plus – with roaming user profiles, Appx packages end up “installed” for users that don’t actually exist in the OS instance anymore, but the Appx management mechanism fails to cleanup after itself. So what’s a fellow to do?

Well – first off, give the middle finger to Microsoft, and then find a way to work around it… And if you are here reading this, I’m guessing you’ve already given that middle finger to Microsoft and are now looking for a way to work around it…

I’m about to share how I worked around it and successfully sysprepped that clone of the production 2019 instance. I will also apologize to you in advance as WordPress has made some rather undesirable changes to it’s editor and posting system and I’m currently struggling to format this post correctly, so I’m not sure how this post is actually going to turn out…

And as always before I begin:

Use any tips, tricks, or scripts I post at your own risk.

On the machine you want to sysprep, download the current DB Browser for SQLite from https://github.com/sqlitebrowser/sqlitebrowser/releases and extract to it C:\TEMP.

Make a backup of “C:\ProgramData\Microsoft\Windows\AppRepository\StateRepository-Machine.srd” to C:\TEMP:

  • robocopy “C:\ProgramData\Microsoft\Windows\AppRepository” “C:\TEMP” “StateRepository-Machine.srd” /zb

Verify the only user account defined on the machine is for Administrator, and that C:\Users only contains Administrator, Default, and Public folders. Also verify that “Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList” only contains the SID for the local Administrator along with S-1-5-18, S-1-5-19, and S-1-5-20.

Before continuing, I would strongly recommend shutting down the machine at this point and taking a snapshot of it…

Launch the DB Browser using psexec (https://docs.microsoft.com/en-us/sysinternals/downloads/sysinternals-suite):

  • psexec -i -d -s “C:\TEMP\DB Browser for SQLite\DB Browser for SQLite.exe” “C:\ProgramData\Microsoft\Windows\AppRepository\StateRepository-Machine.srd”

In the DB Browser, drop all triggers with the following SQL statements (you should be able to cut and paste the entire list below all at once into the “Execute SQL” tab and then click the “Execute all” button) :

  • DROP TRIGGER “TRG_AFTERDELETE_DynamicAppUriHandlerGroup_DynamicAppUriHandler”;
  • DROP TRIGGER “TRG_AFTERDELETE_PackageUser_AppExecutionAliasUser_DynamicAppUriHandlerGroup”;
  • DROP TRIGGER “TRG_AFTERDELETE_PackageUser_Key”;
  • DROP TRIGGER “TRG_AFTERDELETE_Package_Key”;
  • DROP TRIGGER “TRG_AFTERDELETE_PrimaryTileUser_Key”;
  • DROP TRIGGER “TRG_AFTERDELETE_SecondaryTileUser_Key”;
  • DROP TRIGGER “TRG_AFTERINSERT_Application”;
  • DROP TRIGGER “TRG_AFTERINSERT_Package”;
  • DROP TRIGGER “TRG_AFTERINSERT_PackageFamily_SRJournal”;
  • DROP TRIGGER “TRG_AFTERINSERT_PackageUser_Key”;
  • DROP TRIGGER “TRG_AFTERINSERT_PackageUser_PackageFamilyUser”;
  • DROP TRIGGER “TRG_AFTERINSERT_PackageUser_SRJournal”;
  • DROP TRIGGER “TRG_AFTERINSERT_Package_Key”;
  • DROP TRIGGER “TRG_AFTERINSERT_PrimaryTileUser_Key”;
  • DROP TRIGGER “TRG_AFTERINSERT_SecondaryTileUser_Key”;
  • DROP TRIGGER “TRG_AFTERINSERT_User_SRJournal”;
  • DROP TRIGGER “TRG_AFTERUPDATE_PackageUser__Created_LongRunningTransactionUpdateDuringCommit”;
  • DROP TRIGGER “TRG_AFTERUPDATE_Package__Created_LongRunningTransactionUpdateDuringCommit”;
  • DROP TRIGGER “TRG_AFTER_UPDATE_Application_SRJournal”;
  • DROP TRIGGER “TRG_AFTER_UPDATE_PackageFamily_SRJournal”;
  • DROP TRIGGER “TRG_AFTER_UPDATE_PackageUser_SRJournal”;
  • DROP TRIGGER “TRG_AFTER_UPDATE_Package_SRJournal”;
  • DROP TRIGGER “TRG_AFTER_UPDATE_User_SRJournal”;
  • DROP TRIGGER “TRG_BEFOREDELETE_Application_SRJournal”;
  • DROP TRIGGER “TRG_BEFOREDELETE_PackageFamily_SRJournal”;
  • DROP TRIGGER “TRG_BEFOREDELETE_PackageUser_PackageFamilyUser”;
  • DROP TRIGGER “TRG_BEFOREDELETE_PackageUser_SRJournal”;
  • DROP TRIGGER “TRG_BEFOREDELETE_Package_SRJournal”;
  • DROP TRIGGER “TRG_BEFOREDELETE_SRJournal_SRJournalArchive”;
  • DROP TRIGGER “TRG_BEFOREDELETE_User_SRJournal”;
  • DROP TRIGGER “TRG_IDX_ActivityContext_ProductId”;
  • DROP TRIGGER “TRG_IDX_AppUriHandler_ProgID_Extension”;
  • DROP TRIGGER “TRG_IDX_AppxExtension_User_Package_Category_KeyString_RegistrationInformation”;
  • DROP TRIGGER “TRG_IDX_BackgroundServiceAgent_ProductId_TaskId_BackgroundSpecifier_BackgroundName_BackgroundSource_BackgroundType__WorkId”;
  • DROP TRIGGER “TRG_IDX_BundlePackage_Bundle_Version_Architecture_ResourceId__WorkId”;
  • DROP TRIGGER “TRG_IDX_DynamicAppUriHandler_ProgID_DynamicAppUriHandlerGroup”;
  • DROP TRIGGER “TRG_IDX_OptionalBundlePackage_OptionalBundle_Version_Architecture_ResourceId__WorkId”;
  • DROP TRIGGER “TRG_IDX_Package_PackageFamily_ResourceId_Architecture_Version__WorkId”;
  • DROP TRIGGER “TRG_IDX_PublisherCacheFolder_PackageExtension_FolderName”;
  • DROP TRIGGER “TRG_IDX_WorkInProgress_Key”;
  • DROP TRIGGER “TRG_IDX_XapExtension_Consumer_Supplier_ExtensionCategory_ExtensionId_SupplierTaskId__WorkId”;
  • DROP TRIGGER “TRG_IDX_Xap_ProductId__WorkId”;

Now, drop all the user records with the following SQL commands (clear the Execute SQL window of the trigger drop statements and paste these instead, then and execute them):

  • DELETE FROM PackageFamilyUser;
  • DELETE FROM PackageUser;
  • DELETE FROM PackageUserChangeLog;
  • DELETE FROM PrimaryTileUser;
  • DELETE FROM PrimaryTileUserChangeLog;
  • DELETE FROM DeploymentHistory;

If the machines was inplace upgraded from Windows Server 2016, drop the 2016 specific packages as well:

  • DELETE FROM Package where PackageFullName like “%14393%”;
  • DELETE FROM PackageIdentity where PackageFullName like “%14393%”;
  • DELETE FROM MrtApplication where DisplayNameReference like “%14393%”;
  • DELETE FROM MrtPackage where DisplayNameReference like “%14393%”;

Next, check Package, PackageIdentity, MrtApplication, and MrtPackage tables for left over packages from Windows Server 2016 and delete any found.  Examples of these left over packages after an inplace upgrade are:

  • Microsoft.Windows.SecondaryTileExperience_10.0.0.0_neutral__cw5n1h2txyewy
  • windows.immersivecontrolpanel_6.2.0.0_neutral_neutral_cw5n1h2txyewy
  • Windows.MiracastView_6.3.0.0_neutral_neutral_cw5n1h2txyewy
  • Windows.PrintDialog_6.2.0.0_neutral_neutral_cw5n1h2txyewy

As a side note, in my testing – these are the only packages present on a brand new 2019 installation (for comparision purposes to your installation):

  1. 1527c705-839a-4832-9118-54d4Bd6a0c89_10.0.17763.1_neutral_neutral_cw5n1h2txyewy
  2. c5e2524a-ea46-4f67-841f-6a9465d9d515_10.0.17763.1_neutral_neutral_cw5n1h2txyewy
  3. E2A4F912-2574-4A75-9BB0-0D023378592B_10.0.17763.1_neutral_neutral_cw5n1h2txyewy
  4. F46D4000-FD22-4DB4-AC8E-4E1DDDE828FE_10.0.17763.1_neutral_neutral_cw5n1h2txyewy
  5. InputApp_1000.17763.1.0_neutral_neutral_cw5n1h2txyewy
  6. Microsoft.AAD.BrokerPlugin_1000.17763.1.0_neutral_neutral_cw5n1h2txyewy
  7. Microsoft.AccountsControl_10.0.17763.1_neutral__cw5n1h2txyewy
  8. Microsoft.AsyncTextService_10.0.17763.1_neutral__8wekyb3d8bbwe
  9. Microsoft.BioEnrollment_10.0.17763.1_neutral__cw5n1h2txyewy
  10. Microsoft.CredDialogHost_10.0.17763.1_neutral__cw5n1h2txyewy
  11. Microsoft.ECApp_10.0.17763.1_neutral__8wekyb3d8bbwe
  12. Microsoft.LockApp_10.0.17763.1_neutral__cw5n1h2txyewy
  13. Microsoft.Win32WebViewHost_10.0.17763.1_neutral_neutral_cw5n1h2txyewy
  14. Microsoft.Windows.Apprep.ChxApp_1000.17763.1.0_neutral_neutral_cw5n1h2txyewy
  15. Microsoft.Windows.CapturePicker_10.0.17763.1_neutral__cw5n1h2txyewy
  16. Microsoft.Windows.CloudExperienceHost_10.0.17763.1_neutral_neutral_cw5n1h2txyewy
  17. Microsoft.Windows.Cortana_1.11.6.17763_neutral_neutral_cw5n1h2txyewy
  18. Microsoft.Windows.NarratorQuickStart_10.0.17763.1_neutral_neutral_8wekyb3d8bbwe
  19. Microsoft.Windows.OOBENetworkCaptivePortal_10.0.17763.1_neutral__cw5n1h2txyewy
  20. Microsoft.Windows.OOBENetworkConnectionFlow_10.0.17763.1_neutral__cw5n1h2txyewy
  21. Microsoft.Windows.PeopleExperienceHost_10.0.17763.1_neutral_neutral_cw5n1h2txyewy
  22. Microsoft.Windows.PinningConfirmationDialog_1000.17763.1.0_neutral__cw5n1h2txyewy
  23. Microsoft.Windows.SecHealthUI_10.0.17763.1_neutral__cw5n1h2txyewy
  24. Microsoft.Windows.ShellExperienceHost_10.0.17763.1_neutral_neutral_cw5n1h2txyewy
  25. Microsoft.Windows.XGpuEjectDialog_10.0.17763.1_neutral_neutral_cw5n1h2txyewy
  26. Windows.CBSPreview_10.0.17763.1_neutral_neutral_cw5n1h2txyewy
  27. windows.immersivecontrolpanel_10.0.2.1000_neutral_neutral_cw5n1h2txyewy
  28. Windows.PrintDialog_6.2.1.0_neutral_neutral_cw5n1h2txyewy

In my experience, 1527c705-839a-4832-9118-54d4Bd6a0c89_10.0.17763.1_neutral_neutral_cw5n1h2txyewy was always the first native 2019 package when sorting by “package_ID”. Also note that there could be duplicate packages here (i.e. PrintDialog 6.2.0.0 and 6.2.1.0), so start by only deleting the older one (it will generally have a package_ID that is lower than 1527c705-839a-4832-9118-54d4Bd6a0c89_10.0.17763.1_neutral_neutral_cw5n1h2txyewy).

Now recreate the trigger statements (clear the Execute SQL window of the delete statements and paste these instead and execute them):

  • CREATE TRIGGER TRG_AFTERDELETE_DynamicAppUriHandlerGroup_DynamicAppUriHandler AFTER DELETE ON DynamicAppUriHandlerGroup FOR EACH ROW WHEN is_triggers_enabled()BEGIN DELETE FROM DynamicAppUriHandler WHERE DynamicAppUriHandlerGroup=OLD._DynamicAppUriHandlerGroupID;END;
  • CREATE TRIGGER TRG_AFTERDELETE_PackageUser_AppExecutionAliasUser_DynamicAppUriHandlerGroup AFTER DELETE ON PackageUser FOR EACH ROW WHEN is_triggers_enabled()BEGIN DELETE FROM AppExecutionAliasUser WHERE User=OLD.User AND ApplicationIdentity NOT IN (SELECT ai._ApplicationIdentityID FROM ApplicationIdentity AS ai INNER JOIN Application AS a ON a.ApplicationUserModelId=ai.ApplicationUserModelId INNER JOIN PackageUser AS pu ON pu.Package=a.Package WHERE pu.User=OLD.User);DELETE FROM DynamicAppUriHandlerGroup WHERE User=OLD.User AND PackageFamily NOT IN (SELECT p.PackageFamily FROM Package AS p INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID WHERE pu.User=OLD.User);END;
  • CREATE TRIGGER TRG_AFTERDELETE_PackageUser_Key AFTER DELETE ON PackageUser FOR EACH ROW WHEN is_triggers_enabled() AND OLD._WorkId=0 BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=1;INSERT OR REPLACE INTO PackageUserChangelog(_Revision, _WorkId, _Created, _Deleted, User, Package, _PackageUserID, WhenOccurred, _Dictionary)SELECT 1, workid(), OLD._Created, s.LastValue, OLD.User, OLD.Package, OLD._PackageUserID, now(), NULL FROM Sequence AS s WHERE s.Id=1;END;
  • CREATE TRIGGER TRG_AFTERDELETE_Package_Key AFTER DELETE ON Package FOR EACH ROW WHEN is_triggers_enabled() AND OLD._WorkId=0 BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=1;INSERT OR REPLACE INTO PackageChangelog(_Revision, _WorkId, _Created, _Deleted, PackageFullName, _PackageID, WhenOccurred, _Dictionary)SELECT 1, workid(), OLD._Created, s.LastValue, OLD.PackageFullName, OLD._PackageID, now(), NULL FROM Sequence AS s WHERE s.Id=1;END;
  • CREATE TRIGGER TRG_AFTERDELETE_PrimaryTileUser_Key AFTER DELETE ON PrimaryTileUser FOR EACH ROW WHEN is_triggers_enabled() AND OLD._WorkId=0 BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=1;INSERT OR REPLACE INTO PrimaryTileUserChangelog(_Revision, _WorkId, _Created, _Deleted, User, TileUniqueId, WhenOccurred, _Dictionary)SELECT 1, workid(), OLD._Created, s.LastValue, OLD.User, OLD.TileUniqueId, now(), NULL FROM Sequence AS s WHERE s.Id=1;END;
  • CREATE TRIGGER TRG_AFTERDELETE_SecondaryTileUser_Key AFTER DELETE ON SecondaryTileUser FOR EACH ROW WHEN is_triggers_enabled() AND OLD._WorkId=0 BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=1;INSERT OR REPLACE INTO SecondaryTileUserChangelog(_Revision, _WorkId, _Created, _Deleted, User, TileUniqueId, WhenOccurred, _Dictionary)SELECT 1, workid(), OLD._Created, s.LastValue, OLD.User, OLD.TileUniqueId, now(), NULL FROM Sequence AS s WHERE s.Id=1;END;
  • CREATE TRIGGER TRG_AFTERINSERT_Application AFTER INSERT ON Application FOR EACH ROW BEGIN INSERT OR IGNORE INTO ApplicationIdentity (ApplicationUserModelId) VALUES(NEW.ApplicationUserModelId);UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, PackageIdentity, ApplicationIdentity, WhenOccurred, SequenceId)SELECT 1, workid(), 3, 1, NEW._ApplicationID, pi._PackageIdentityID, ai._ApplicationIdentityID, now(), s.LastValue FROM Sequence AS s CROSS JOIN PackageIdentity AS pi CROSS JOIN Package AS p CROSS JOIN ApplicationIdentity AS ai WHERE s.Id=2 AND pi.PackageFullName=p.PackageFullName AND p._PackageID=NEW.Package AND ai.ApplicationUserModelId=NEW.ApplicationUserModelId;END;
  • CREATE TRIGGER TRG_AFTERINSERT_Package AFTER INSERT ON Package FOR EACH ROW BEGIN INSERT OR IGNORE INTO PackageIdentity (PackageFamily, PackageFullName) VALUES(NEW.PackageFamily, NEW.PackageFullName);UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, PackageIdentity, WhenOccurred, SequenceId)SELECT 1, workid(), 1, 1, NEW._PackageID, pi._PackageIdentityID, now(), s.LastValue FROM Sequence AS s CROSS JOIN PackageIdentity AS pi WHERE s.Id=2 AND pi.PackageFullName=NEW.PackageFullName;END;
  • CREATE TRIGGER TRG_AFTERINSERT_PackageFamily_SRJournal AFTER INSERT ON PackageFamily FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, PackageFamily, WhenOccurred, SequenceId)SELECT 1, workid(), 5, 1, NEW._PackageFamilyID, NEW._PackageFamilyID, now(), s.LastValue FROM Sequence AS s WHERE s.Id=2 ;END;
  • CREATE TRIGGER TRG_AFTERINSERT_PackageUser_Key AFTER INSERT ON PackageUser FOR EACH ROW WHEN is_triggers_enabled() AND NEW._WorkId=0 AND NEW._Created=0 BEGIN DELETE FROM PackageUserChangelog WHERE NEW._Created=0 AND User=NEW.User AND User=NEW.User AND Package=NEW.Package;END;
  • CREATE TRIGGER TRG_AFTERINSERT_PackageUser_PackageFamilyUser AFTER INSERT ON PackageUser FOR EACH ROW WHEN is_triggers_enabled()BEGIN INSERT OR IGNORE INTO PackageFamilyUser (PackageFamily, User, WhenInstalled, _Revision)SELECT p.PackageFamily, NEW.User, now(), 1 FROM Package AS p INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID WHERE (SELECT EXISTS(SELECT 1 FROM User WHERE _UserID=NEW.User AND UserSid<>X’010100000000000512000000′))AND NEW.Package=p._PackageID;END;
  • CREATE TRIGGER TRG_AFTERINSERT_PackageUser_SRJournal AFTER INSERT ON PackageUser FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, User, PackageIdentity, WhenOccurred, SequenceId)SELECT 1, workid(), 2, 1, NEW._PackageUserID, NEW.User, pi._PackageIdentityID, now(), s.LastValue FROM Sequence AS s CROSS JOIN Package AS p CROSS JOIN PackageIdentity AS pi ON pi.PackageFullName=p.PackageFullName WHERE s.Id=2 AND p._PackageID=NEW.Package AND pi.PackageFullName=p.PackageFullName;END;
  • CREATE TRIGGER TRG_AFTERINSERT_Package_Key AFTER INSERT ON Package FOR EACH ROW WHEN is_triggers_enabled() AND NEW._WorkId=0 AND NEW._Created=0 BEGIN DELETE FROM PackageChangelog WHERE NEW._Created=0 AND PackageFullName=NEW.PackageFullName;END;
  • CREATE TRIGGER TRG_AFTERINSERT_PrimaryTileUser_Key AFTER INSERT ON PrimaryTileUser FOR EACH ROW WHEN is_triggers_enabled() AND NEW._WorkId=0 AND NEW._Created=0 BEGIN DELETE FROM PrimaryTileUserChangelog WHERE NEW._Created=0 AND User=NEW.User AND TileUniqueId=NEW.TileUniqueId;END;
  • CREATE TRIGGER TRG_AFTERINSERT_SecondaryTileUser_Key AFTER INSERT ON SecondaryTileUser FOR EACH ROW WHEN is_triggers_enabled() AND NEW._WorkId=0 AND NEW._Created=0 BEGIN DELETE FROM SecondaryTileUserChangelog WHERE NEW._Created=0 AND User=NEW.User AND TileUniqueId=NEW.TileUniqueId;END;
  • CREATE TRIGGER TRG_AFTERINSERT_User_SRJournal AFTER INSERT ON User FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, User, WhenOccurred, SequenceId)SELECT 1, workid(), 4, 1, NEW._UserID, NEW._UserID, now(), s.LastValue FROM Sequence AS s WHERE s.Id=2 ;END;
  • CREATE TRIGGER TRG_AFTERUPDATE_PackageUser__Created_LongRunningTransactionUpdateDuringCommit AFTER UPDATE OF _Created ON PackageUser FOR EACH ROW WHEN OLD._Created=-2 AND NEW._Created>0 BEGIN DELETE FROM PackageUserChangelog WHERE User=NEW.User AND Package=NEW.Package;END;
  • CREATE TRIGGER TRG_AFTERUPDATE_Package__Created_LongRunningTransactionUpdateDuringCommit AFTER UPDATE OF _Created ON Package FOR EACH ROW WHEN OLD._Created=-2 AND NEW._Created>0 BEGIN DELETE FROM PackageChangelog WHERE PackageFullName=NEW.PackageFullName;END;
  • CREATE TRIGGER TRG_AFTER_UPDATE_Application_SRJournal AFTER UPDATE ON Application FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, PackageIdentity, ApplicationIdentity, WhenOccurred, SequenceId)SELECT 1, workid(), 3, 2, NEW._ApplicationID, pi._PackageIdentityID, ai._ApplicationIdentityID, now(), s.LastValue FROM Sequence AS s CROSS JOIN PackageIdentity AS pi CROSS JOIN Package AS p CROSS JOIN ApplicationIdentity AS ai WHERE s.Id=2 AND pi.PackageFullName=p.PackageFullName AND p._PackageID=NEW.Package AND ai.ApplicationUserModelId=NEW.ApplicationUserModelId;END;
  • CREATE TRIGGER TRG_AFTER_UPDATE_PackageFamily_SRJournal AFTER UPDATE ON PackageFamily FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, PackageFamily, WhenOccurred, SequenceId)SELECT 1, workid(), 5, 2, NEW._PackageFamilyID, NEW._PackageFamilyID, now(), s.LastValue FROM Sequence AS s WHERE s.Id=2 ;END;
  • CREATE TRIGGER TRG_AFTER_UPDATE_PackageUser_SRJournal AFTER UPDATE ON PackageUser FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, User, PackageIdentity, WhenOccurred, SequenceId)SELECT 1, workid(), 2, 2, NEW._PackageUserID, NEW.User, pi._PackageIdentityID, now(), s.LastValue FROM Sequence AS s CROSS JOIN Package AS p CROSS JOIN PackageIdentity AS pi ON pi.PackageFullName=p.PackageFullName WHERE s.Id=2 AND p._PackageID=NEW.Package AND pi.PackageFullName=p.PackageFullName;END;
  • CREATE TRIGGER TRG_AFTER_UPDATE_Package_SRJournal AFTER UPDATE ON Package FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, PackageIdentity, WhenOccurred, SequenceId)SELECT 1, workid(), 1, 2, NEW._PackageID, pi._PackageIdentityID, now(), s.LastValue FROM Sequence AS s CROSS JOIN PackageIdentity AS pi WHERE s.Id=2 AND pi.PackageFullName=NEW.PackageFullName;END;
  • CREATE TRIGGER TRG_AFTER_UPDATE_User_SRJournal AFTER UPDATE ON User FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, User, WhenOccurred, SequenceId)SELECT 1, workid(), 4, 2, NEW._UserID, NEW._UserID, now(), s.LastValue FROM Sequence AS s WHERE s.Id=2 ;END;
  • CREATE TRIGGER TRG_BEFOREDELETE_Application_SRJournal BEFORE DELETE ON Application FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, PackageIdentity, ApplicationIdentity, WhenOccurred, SequenceId)SELECT 1, workid(), 3, 3, OLD._ApplicationID, pi._PackageIdentityID, ai._ApplicationIdentityID, now(), s.LastValue FROM Sequence AS s CROSS JOIN PackageIdentity AS pi CROSS JOIN Package AS p CROSS JOIN ApplicationIdentity AS ai WHERE s.Id=2 AND pi.PackageFullName=p.PackageFullName AND p._PackageID=OLD.Package AND ai.ApplicationUserModelId=OLD.ApplicationUserModelId;END;
  • CREATE TRIGGER TRG_BEFOREDELETE_PackageFamily_SRJournal BEFORE DELETE ON PackageFamily FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, PackageFamily, WhenOccurred, SequenceId)SELECT 1, workid(), 5, 3, OLD._PackageFamilyID, OLD._PackageFamilyID, now(), s.LastValue FROM Sequence AS s WHERE s.Id=2 ;END;
  • CREATE TRIGGER TRG_BEFOREDELETE_PackageUser_PackageFamilyUser BEFORE DELETE ON PackageUser FOR EACH ROW WHEN is_triggers_enabled()BEGIN DELETE FROM PackageFamilyUser WHERE (SELECT EXISTS(SELECT 1 FROM User WHERE _UserID=OLD.User AND UserSid<> X’010100000000000512000000′))AND User=OLD.User AND PackageFamily IN (SELECT p.PackageFamily FROM Package AS p INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID WHERE OLD.Package=pu.Package AND OLD.User=pu.User LIMIT 1) AND (SELECT EXISTS(SELECT 1 WHERE (SELECT COUNT(*) AS count FROM PackageUser AS pu INNER JOIN Package AS p ON p.rowid=pu.Package WHERE p.PackageFamily IN (SELECT p.PackageFamily FROM Package AS p INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID WHERE OLD.Package=pu.Package AND OLD.User=pu.User)) <= 1));END;
  • CREATE TRIGGER TRG_BEFOREDELETE_PackageUser_SRJournal BEFORE DELETE ON PackageUser FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, User, PackageIdentity, WhenOccurred, SequenceId)SELECT 1, workid(), 2, 3, OLD._PackageUserID, OLD.User, pi._PackageIdentityID, now(), s.LastValue FROM Sequence AS s CROSS JOIN Package AS p CROSS JOIN PackageIdentity AS pi ON pi.PackageFullName=p.PackageFullName WHERE s.Id=2 AND p._PackageID=OLD.Package AND pi.PackageFullName=p.PackageFullName;END;
  • CREATE TRIGGER TRG_BEFOREDELETE_Package_SRJournal BEFORE DELETE ON Package FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, PackageIdentity, WhenOccurred, SequenceId)SELECT 1, workid(), 1, 3, OLD._PackageID, pi._PackageIdentityID, now(), s.LastValue FROM Sequence AS s CROSS JOIN PackageIdentity AS pi WHERE s.Id=2 AND pi.PackageFullName=OLD.PackageFullName;END;
  • CREATE TRIGGER TRG_BEFOREDELETE_SRJournal_SRJournalArchive BEFORE DELETE ON SRJournal FOR EACH ROW WHEN sroptions() & 0x00100000 != 0 BEGIN INSERT INTO SRJournalArchive(_Revision, _WorkId, ObjectType, “Action”, ObjectId, Flags,User, PackageFamily, PackageIdentity, ApplicationIdentity,WhenOccurred, SequenceId, _Dictionary)SELECT OLD._Revision, OLD._WorkId, OLD.ObjectType, OLD.”Action”, OLD.ObjectId, OLD.Flags,OLD.User, OLD.PackageFamily, OLD.PackageIdentity, OLD.ApplicationIdentity,OLD.WhenOccurred, OLD.SequenceId, OLD._Dictionary;END;
  • CREATE TRIGGER TRG_BEFOREDELETE_User_SRJournal BEFORE DELETE ON User FOR EACH ROW BEGIN UPDATE Sequence SET LastValue=LastValue+1 WHERE Id=2 ;INSERT INTO SRJournal(_Revision, _WorkId, ObjectType, Action, ObjectId, User, WhenOccurred, SequenceId)SELECT 1, workid(), 4, 3, OLD._UserID, OLD._UserID, now(), s.LastValue FROM Sequence AS s WHERE s.Id=2 ;END;
  • CREATE TRIGGER TRG_IDX_ActivityContext_ProductId BEFORE INSERT ON ActivityContext FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _ActivityContextID FROM ActivityContext WHERE ProductId IS NEW.ProductId) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “ActivityContext” violates the unique index “IDX_ActivityContext_ProductId”‘)END;END;
  • CREATE TRIGGER TRG_IDX_AppUriHandler_ProgID_Extension BEFORE INSERT ON AppUriHandler FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _AppUriHandlerID FROM AppUriHandler WHERE ProgID IS NEW.ProgID AND Extension=NEW.Extension) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “AppUriHandler” violates the unique index “IDX_AppUriHandler_ProgID_Extension”‘)END;END;
  • CREATE TRIGGER TRG_IDX_AppxExtension_User_Package_Category_KeyString_RegistrationInformation BEFORE INSERT ON AppxExtension FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _AppxExtensionID FROM AppxExtension WHERE User=NEW.User AND Package=NEW.Package AND Category=NEW.Category AND KeyString IS NEW.KeyString AND RegistrationInformation=NEW.RegistrationInformation) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “AppxExtension” violates the unique index “IDX_AppxExtension_User_Package_Category_KeyString_RegistrationInformation”‘)END;END;
  • CREATE TRIGGER TRG_IDX_BackgroundServiceAgent_ProductId_TaskId_BackgroundSpecifier_BackgroundName_BackgroundSource_BackgroundType__WorkId BEFORE INSERT ON BackgroundServiceAgent FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _BackgroundServiceAgentID FROM BackgroundServiceAgent WHERE ProductId=NEW.ProductId AND TaskId=NEW.TaskId AND BackgroundSpecifier IS NEW.BackgroundSpecifier AND BackgroundName IS NEW.BackgroundName AND BackgroundSource IS NEW.BackgroundSource AND BackgroundType IS NEW.BackgroundType AND _WorkId=NEW._WorkId) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “BackgroundServiceAgent” violates the unique index “IDX_BackgroundServiceAgent_ProductId_TaskId_BackgroundSpecifier_BackgroundName_BackgroundSource_BackgroundType__WorkId”‘)END;END;
  • CREATE TRIGGER TRG_IDX_BundlePackage_Bundle_Version_Architecture_ResourceId__WorkId BEFORE INSERT ON BundlePackage FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _BundlePackageID FROM BundlePackage WHERE Bundle=NEW.Bundle AND Version=NEW.Version AND Architecture=NEW.Architecture AND ResourceId IS NEW.ResourceId AND _WorkId=NEW._WorkId AND Bundle<>0) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “BundlePackage” violates the unique index “IDX_BundlePackage_Bundle_Version_Architecture_ResourceId__WorkId”‘)END;END;
  • CREATE TRIGGER TRG_IDX_DynamicAppUriHandler_ProgID_DynamicAppUriHandlerGroup BEFORE INSERT ON DynamicAppUriHandler FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _DynamicAppUriHandlerID FROM DynamicAppUriHandler WHERE ProgID IS NEW.ProgID AND DynamicAppUriHandlerGroup=NEW.DynamicAppUriHandlerGroup) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “DynamicAppUriHandler” violates the unique index “IDX_DynamicAppUriHandler_ProgID_DynamicAppUriHandlerGroup”‘)END;END;
  • CREATE TRIGGER TRG_IDX_OptionalBundlePackage_OptionalBundle_Version_Architecture_ResourceId__WorkId BEFORE INSERT ON OptionalBundlePackage FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _OptionalBundlePackageID FROM OptionalBundlePackage WHERE OptionalBundle=NEW.OptionalBundle AND Version=NEW.Version AND Architecture=NEW.Architecture AND ResourceId IS NEW.ResourceId AND _WorkId=NEW._WorkId AND OptionalBundle<>0) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “OptionalBundlePackage” violates the unique index “IDX_OptionalBundlePackage_OptionalBundle_Version_Architecture_ResourceId__WorkId”‘)END;END;
  • CREATE TRIGGER TRG_IDX_Package_PackageFamily_ResourceId_Architecture_Version__WorkId BEFORE INSERT ON Package FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _PackageID FROM Package WHERE PackageFamily=NEW.PackageFamily AND ResourceId IS NEW.ResourceId AND Architecture=NEW.Architecture AND Version=NEW.Version AND _WorkId=NEW._WorkId) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “Package” violates the unique index “IDX_Package_PackageFamily_ResourceId_Architecture_Version__WorkId”‘)END;END;
  • CREATE TRIGGER TRG_IDX_PublisherCacheFolder_PackageExtension_FolderName BEFORE INSERT ON PublisherCacheFolder FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _PublisherCacheFolderID FROM PublisherCacheFolder WHERE PackageExtension=NEW.PackageExtension AND FolderName IS NEW.FolderName) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “PublisherCacheFolder” violates the unique index “IDX_PublisherCacheFolder_PackageExtension_FolderName”‘)END;END;
  • CREATE TRIGGER TRG_IDX_WorkInProgress_Key BEFORE INSERT ON WorkInProgress FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _WorkInProgressID FROM WorkInProgress WHERE “Key” IS NEW.”Key”) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “WorkInProgress” violates the unique index “IDX_WorkInProgress_Key”‘)END;END;
  • CREATE TRIGGER TRG_IDX_XapExtension_Consumer_Supplier_ExtensionCategory_ExtensionId_SupplierTaskId__WorkId BEFORE INSERT ON XapExtension FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _XapExtensionID FROM XapExtension WHERE Consumer IS NEW.Consumer AND Supplier=NEW.Supplier AND ExtensionCategory=NEW.ExtensionCategory AND ExtensionId=NEW.ExtensionId AND SupplierTaskId=NEW.SupplierTaskId AND _WorkId=NEW._WorkId) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “XapExtension” violates the unique index “IDX_XapExtension_Consumer_Supplier_ExtensionCategory_ExtensionId_SupplierTaskId__WorkId”‘)END;END;
  • CREATE TRIGGER TRG_IDX_Xap_ProductId__WorkId BEFORE INSERT ON Xap FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT _XapID FROM Xap WHERE ProductId IS NEW.ProductId AND _WorkId=NEW._WorkId) IS NOT NULL)THEN RAISE(ABORT, ‘INSERT INTO “Xap” violates the unique index “IDX_Xap_ProductId__WorkId”‘)END;END;

Close the DB Browser, and open PowerShell. Check for remaining provisioned packages against users (the output of this should be blank)

  • Get-AppxPackage -AllUsers | foreach {
  • $row = $_
  • $row.PackageUserInformation | foreach {
  • $info = $_
  • [pscustomobject]@{Package=$row.packagefullname
  • user=$info.UserSecurityId.sid
  • username=$info.UserSecurityId.Username
  • installstate=$info.installstate}}}

Ideally at this point, shutdown the VM and take another snapshot before attempting to run sysprep.

I would also highly recommending connecting via the VMware console as opposed to RDP to run sysprep so you can see what is actually happening (otherwise you’ll lose connection via RDP before sysprep completes running and you’ll never know if it failed or not).

If sysprep does fails, check C:\Windows\System32\Sysprep\Panther\setuperr.log to see what package you missed. Revert your snapshot, launch the DB Browser for SQLite again, delete the triggers, then clean up the missed package, recreate the triggers, reboot, and try your sysprep again. It may take you a few attempts to fully clean up all the necessary packages before sysprep successfully completes, but eventually you’ll get there!

Keep in mind – this is totally unsupported by Microsoft, and who knows what this may break in the future… Use the above instructions completley at your own risk!!!

Updating the firmware of a list of HPE ILO 5 IPs via PowerShell

@DeanColpittsLeave a comment

In addition to my previous blog post of upgrading a list of ILO 4’s firmware via PowerShell, I also wrote a similar script for ILO 5. However this script makes use of the iLO Repository and the .fwpkg file type, and does not rely on an IIS server for the ILO to pull the firmware file from. Other than that, it functions very similar to the ILO 4 update script I previously posted.

Below is my PowerShell code.  You’ll need to adjust it as required for your own environment.  Be sure to update the items in red where required.

And as always:
Use any tips, tricks, or scripts I post at your own risk.

### get ILORest here - https://downloads.hpe.com/pub/softlib2/software1/pubsw-windows/p1440367746/v177187/ilorest-3.0.1.0-8.x86_64.msi

Import-Module BitsTransfer
$url_zip = "https://downloads.hpe.com/pub/softlib2/software1/fwpkg-ilo/p991377599/v167897/ilo5_230.fwpkg"
$output_path = "C:\TEMP\ILO5"
$output_zip = $output_path + '\ilo5_230.fwpkg'
New-Item -Path $output_path -ItemType "Directory" -Force -Confirm:$false | out-null
Start-BitsTransfer -Source $url_zip -Destination $output_zip

$username = "ilo-username"
$password = "ilo-password"
$ILOrest = "C:\Program Files\Hewlett Packard Enterprise\RESTful Interface Tool\ilorest.exe"
$ILOlist = "$output_path\ilolist.csv"
'iloip' | Out-File $ILOlist
'192.168.11.230' | Out-File $ILOlist -Append
'192.168.11.230' | Out-File $ILOlist -Append

Import-Csv $ILOlist | Foreach {
$iloip = $_.iloip
$args = " flashfwpkg C:\DL\ILO5\ilo5_230.fwpkg --url " + $iloip + " -u " + $username + " -p " + $password
Start-Process -Wait -Filepath $ILOrest -ArgumentList $args
}

Updating the firmware of a list of HPE ILO 4 IPs via PowerShell

@DeanColpittsLeave a comment

Recently, I had to update a bunch of HPE ILO 4s at multiple locations. Most of my managed sites have between 3 and 9 ILOs that need updated when HPE pushes out an ILO firmware update. I could have used ILO federation group firmware update, or the ILO Amplifier Pack to do this, but I’m a fan of scripting things so I just have to RDP a server onsite, open a prompt and paste a few lines of code and let it start doing it’s thing, then RDP the next site and do the same thing. So I built a PowerShell script to download the ILO 4 update, extract the .bin file, copy it an IIS server, and then proceed to upgrade each ILO one a time utilizing the ILO RestAPI.

Below is my PowerShell code.  You’ll need to adjust it as required for your own environment.  Be sure to update the items in red where required.  Keep in mind your IIS server ($iisip) will need to have a mime type associated with bin files for this to work.

And as always:

Use any tips, tricks, or scripts I post at your own risk.


### get ILORest here if you need it - https://downloads.hpe.com/pub/softlib2/software1/pubsw-windows/p1440367746/v177187/ilorest-3.0.1.0-8.x86_64.msi

Import-Module BitsTransfer
$url_zip = "https://downloads.hpe.com/pub/softlib2/software1/sc-windows-fw-ilo/p1012384589/v186433/cp045313.exe"
$binname = "ilo4_275.bin"
$output_path = "C:\TEMP\ILO4"
$output_zip = $output_path + '\cp045313.exe'
$binpath = $output_path + "\" + $binname
$iisip = "192.168.11.4"
$iispath = "\\" + $iisip + "\c$\inetpub\wwwroot\" + $binname
New-Item -Path $output_path -ItemType "Directory" -Force -Confirm:$false | out-null
Start-BitsTransfer -Source $url_zip -Destination $output_zip
$7zpath = "C:\Program Files\7-Zip\7z.exe"
$7options = "e "+ $output_zip + " " + "-o" + $output_path + " *.bin"
Start-Process -Wait -Filepath $7zpath -ArgumentList $7options
Copy-item -path $binpath -destination $iispath -force -confirm:$false

$username = "ilo-username"
$password = "ilo-password"
$ILOrest = "C:\Program Files\Hewlett Packard Enterprise\RESTful Interface Tool\ilorest.exe"
$ILOlist = "$output_path\ilolist.csv"
'iloip' | Out-File $ILOlist
'192.168.11.230' | Out-File $ILOlist -Append
'192.168.11.231' | Out-File $ILOlist -Append

Import-Csv $ILOlist | Foreach {
$iloip = $_.iloip $args = " firmwareupdate http://" + $iisip + "/" + $binname + " --url " + $iloip + " -u " + $username + " -p " + $password Start-Process -Wait -Filepath $ILOrest -ArgumentList $args }

 

HOWTO: Set the creation and modification timestamp on a file via #PowerShell

@DeanColpittsLeave a comment

Recently, I updated one of our internal tool kits, and then packaged it for distribution.  It was a busy day when I updated it, so I didn’t manage to package it on the same day as I had updated / built / compiled it.  Internally, we use the date as the version number of the tool (occasionally suffixed with a letter which indicates my screw-ups in the build process on that given day).  In this particular case, the version number was 2018-11-24b, indicating I updated it on 2018-11-24, and that this was the 3 revision (no suffix, a, then b) that I had created on 2018-11-24 (I found bugs in the first two after testing the packaging).

Because I wasn’t packaging on the same day as I updated it, the time stamps on my archives didn’t match the build date, so I need to change them – all of them!  So I figured up PowerShell and used it instead.  Below are the commands necessary to view and set both the creation and modification timestamps on a file via an elevated PowerShell prompt.

As always – Use any tips, tricks, or scripts I post at your own risk.

To view the file creation timestamp:

(Get-ChildItem “c:\path\file_to_change.wim”).CreationTime

To set the file creation timestamp:

(Get-ChildItem “c:\path\file_to_change.wim”).CreationTime = ’11/24/2018 11:24AM’

To view the file modification timestamp:

(Get-ChildItem “c:\path\file_to_change.wim”).LastWriteTime

To set the file modification timestamp:

(Get-ChildItem “c:\path\file_to_change.wim”).LastWriteTime = ’11/24/2018 11:24AM’

To set the creation and modification timestamp on every single file in a folder:

foreach ($objFile in Get-ChildItem “c:\path\*”) {$objFile.Creationtime = ’11/24/2018 11:24AM’}

foreach ($objFile in Get-ChildItem “c:\path\*”) {$objFile.LastWriteTime = ’11/24/2018 11:24AM’}

 

 

HOWTO: Permanently replace the ugly Windows 10/2016 login screen background and colors for all users with #PowerShell

@DeanColpittsLeave a comment

I can’t stand the default Windows 10 and Windows Server 2016 logon background, and one of the first things I do when I build a new Windows template at a customer site is wipe that default background out!  I typically replace it with a single solid color, and I’m kind of fond of the old blue backgrounds that came with Windows XP (or was it Windows NT 4 – or may Windows 2000, I don’t remember now) as they are easy on the eyes… Anyways – the background color I like and use has a RGB value of 58 110 165.

I used to have a basic batch file to wipe it Microsoft’s stock background out by copying an existing background over from my staging server, but with every iteration of Windows 10 and Windows Server 2016, the path to img100.jpg in C:\Windows\WinSxS changes.  So last night I decided it was time to use some PowerShell to take care of this menace and allow the script to run on multiple platforms and software updates.

I struggled with creating a new solid color background jpg in PowerShell using the RGB value I wanted, but eventually I found some code that someone had posted elsewhere on how to create a gradient jpg, so I snagged it and set the gradient to be same at the end as the beginning, which results in a solid color all the way across.  I’m sure someone with better skills than me could clean this up properly – but this suits my purposes for what I need so I stopped searching for a better way.

So basically what this script does is create a new jpg that is 640×480 in C:\Windows\Web\Wallpaper\Staging, adjusts the accent colors for the current user and the default user profile, finds the path to img100.jpg and replaces it after taking ownership and setting appropriate ntfs rights to it, then clears out the lock screen jpgs using RoboCopy.  The lock screen jpgs are owned by the System account, and Robocopy /mir /zb is the simplest way to wipe them out that I know of without using Sysinternals Suite psexec to involve System account privileges and delete the jpgs.

You definitely need to run this in an elevated PowerShell session too!

As always – Use any tips, tricks, or scripts I post at your own risk.

New-Item -Path "C:\Windows\Web\Wallpaper\Staging" -ItemType "Directory" -Force -Confirm:$false | out-null
Add-Type -AssemblyName System.Drawing
$newbackground = New-Object System.Drawing.Bitmap 640, 480
[System.Drawing.Graphics]::FromImage($newbackground).FillRectangle(
(New-Object System.Drawing.Drawing2D.LinearGradientBrush(
(New-Object System.Drawing.Point(0, 0)),
(New-Object System.Drawing.Point(640, 480)),
[System.Drawing.Color]::FromArgb(58, 110, 165),
[System.Drawing.Color]::FromArgb(58, 110, 165))),
0, 0, $newbackground.Width, $newbackground.Height)
$newbackground.Save('C:\Windows\Web\Wallpaper\Staging\background.jpg',[System.Drawing.Imaging.ImageFormat]::Jpeg)
copy-item -path C:\Windows\Web\Wallpaper\Staging\background.jpg -destination c:\windows\web\wallpaper\background.jpg -force -confirm:$false
REG LOAD HKEY_USERS\ZZZ C:\USERS\DEFAULT\NTUSER.DAT
REG ADD "HKEY_USERS\ZZZ\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Accent" /f /v "StartColor" /t REG_DWORD /d 0xffa66c39
REG ADD "HKEY_USERS\ZZZ\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Accent" /f /v "AccentColor" /t REG_DWORD /d 0xffb51746
REG UNLOAD HKEY_USERS\ZZZ
REG ADD "HKEY_USERS\.DEFAULT\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Accent" /f /v "StartColor" /t REG_DWORD /d 0xffa66c39
REG ADD "HKEY_USERS\.DEFAULT\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Accent" /f /v "AccentColor" /t REG_DWORD /d 0xffb51746
REG ADD "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Accent" /f /v "StartColor" /t REG_DWORD /d 0xffa66c39
REG ADD "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Accent" /f /v "AccentColor" /t REG_DWORD /d 0xffb51746
REG ADD "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Accent" /f /v "DefaultStartColor" /t REG_DWORD /d 0xffa66c39
takeown /f C:\ProgramData\Microsoft\Windows\SystemData /a /r /d y
takeown /f C:\Windows\Web\Screen\img100.jpg /a
icacls C:\Windows\Web\Screen\img100.jpg /grant Administrators:F
$lockscreen = "C:\ProgramData\Microsoft\Windows\SystemData\S-1-5-18\ReadOnly\LockScreen_Z"
$tempfolder = "C:\ProgramData\Microsoft\Windows\SystemData\S-1-5-18\ReadOnly\LockScreen_Temp"
$img100 = Get-ChildItem C:\Windows\WinSxS -Recurse -Include img100.jpg
write-host $img100
takeown /f $img100 /a
icacls $img100 /grant Administrators:F /q
copy-item -path c:\windows\web\wallpaper\background.jpg -destination $img100 -force -confirm:$false | out-null
copy-item -path c:\windows\web\wallpaper\background.jpg -destination C:\Windows\Web\Wallpaper\Windows\BlueBackground.jpg -force -confirm:$false | out-null
copy-item -path c:\windows\web\wallpaper\background.jpg -destination C:\Windows\Web\Screen\img100.jpg -force -confirm:$false | out-null
New-Item -Path $tempfolder -ItemType "Directory" | out-null
Robocopy $tempfolder $lockscreen /zb /mir /njh /njs
Remove-Item -Path $tempfolder -force -confirm:$false | out-null

 

HOWTO: #PowerShell script to download, extract and add #SysinternalsSuite to the path

@DeanColpittsLeave a comment

I absolutely love Microsoft’s Sysinternals Suite – it’s an amazing set of tools for troubleshooting and tweaking Windows machines.  Heck – there isn’t a day goes by that I don’t use at least one of the tools out of the suite.  I generally try to download, extract and add the suite to the path of any computer I touch.

This morning while building a new 2016 template for a customer, I realized I had missed downloading and adding it to the path, but the VM was in a firewalled VLAN and unable to reach my staging and support server – so I couldn’t just grab the extracted directory from my staging server.  This got me to thinking there must be a simple way to use a cli or script to download, extract, and add the extracted folder to the computer’s path.  So I took 30 minutes and wrote one.

Basically, this script can be cut and pasted into an elevated PowerShell session, and it will grab the most recent SysinternalsSuite.zip from Microsoft, extract the .zip to C:\Program Files\SysinternalsSuite, and then add C:\Program Files\SysinternalsSuite to the computer’s path if it does not already exist in the path.

I’ve tested this with Windows 7, Windows 10 (1803), Windows Server 2012 R2 and Windows Server 2016.

As always – Use any tips, tricks, or scripts I post at your own risk.

Import-Module BitsTransfer
$url_zip = "https://download.sysinternals.com/files/SysinternalsSuite.zip"
$output_path = "C:\Program Files\SysinternalsSuite"
$output_zip = $output_path + '\SysinternalsSuite.zip'
Remove-Item -Path $output_path\*.* -force -confirm:$false
New-Item -Path $output_path -ItemType "Directory" -Force -Confirm:$false | out-null
Start-BitsTransfer -Source $url_zip -Destination $output_zip
Add-Type -AssemblyName System.IO.Compression.FileSystem
function Unzip
{
param([string]$zipfile, [string]$outpath)
[System.IO.Compression.ZipFile]::ExtractToDirectory($zipfile, $outpath)
}
Unzip $output_zip $output_path
Remove-Item -Path $output_zip -force -confirm:$false
$oldpath = (Get-ItemProperty -Path "Registry::HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\Environment" -Name PATH).path
If ($oldpath -NotLike "*SysinternalsSuite*") {
$newpath = "$oldpath;$output_path"
Set-ItemProperty -Path "Registry::HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\Environment" -Name PATH -Value $newPath
}
$writtenpath = (Get-ItemProperty -Path "Registry::HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\Environment" -Name PATH).path
write-host $writtenpath

 

 

HOWTO: Manually uninstall Citrix StoreFront after a 1603 MSI Installer error during an upgrade or uninstall (#Citrix #StoreFront #msiexec)

@DeanColpittsLeave a comment

Many of my clients utilize Citrix XenDesktop or XenApp and thus Citrix StoreFront.  Once it is initially configured and running, things are generally pretty smooth going.  But when it comes time to perform in-place upgrades of Citrix StoreFront, sometimes things get a bit hairy and go off track, usually ending up with a dreaded 1603 MSI installer error.  Then you are royally screwed because the StoreFront installation is half installed (or half uninstalled if you are an optimist) and you can’t repair, reinstall, or even uninstall using normal methods.  Below are the notes I’ve developed for myself and my support team to manually uninstall StoreFront should the need arise – which it does, often.

2017-02-21-19-14-07-snagit-0020

These notes are based on single server stand alone installs of Citrix StoreFront versions 2.6.0.5031, 3.0.1.55 , and 3.6.0.33 (as in I’ve used these notes to manually uninstall those versions before).  I have used these notes on XenApp 6.5 servers and on XenDesktop 7.x controllers without any issues.  Your mileage may vary though.

As always – Use any tips, tricks, or scripts I post at your own risk.

**Warning** Reboot and take a VM snapshot of the StoreFront server before doing anything else.  A reboot is a requirement before doing anything with StoreFront, it doesn’t matter if you are doing an install / upgrade, or are already screwed and need to manually uninstall – reboot before continuing!!!  And if you do not reboot – YOU WILL GET ERRORS that will prevent the instructions below from working.

Immediately after you have rebooted, open an elevated Command Prompt and remove all thumbs.db files on the StoreFront server which can be locked opened by Windows Explorer and cause the uninstaller to fail:

c:
cd \
del /s thumbs.db

2017-02-21-19-24-22-snagit-0022

Next, verify that the HTML5 Client is actually installed on the machine, otherwise the uninstaller will likely fail later on.

msiexec /i "C:\Program Files\Citrix\Receiver StoreFront\Features\HTML5Client\template\HTML5Installer.msi"

If you get a repair / remove Windows Installer dialog box, then it is installed and you can just exit the installer, otherwise install using the default settings.

Open the StoreFront MMC, and if it allows you (which it likely won’t), delete all your stores.

Open Add/Remove Programs and uninstall the Citrix Receiver if it is installed.

Open an elevated Powershell console.  Add the Delivery Services Framework snapin,  remove all the Feature Instances, then confirm they are all removed.

**Note – only add the single snapin listed below, otherwise you potentially will end up with files locked open during the removal process, which can cause the removal to fail**

### Add the Citrix Delivery Services Framework Powershell Snapin
add-pssnapin Citrix.DeliveryServices.Framework.Commands

### Remove all DS FeatureInstances
Remove-DSFeatureInstance -all -confirm:$false

### Verify all FeatureInstances are deleted - you should see just {} listed
Get-DSFrameworkController

### If any FeatureInstances are still listed, remove manually them with the next line, otherwise skip to Uninstall-DSFeatureClass
remove-dsfeatureinstance -featureinstanceid feature_name

2017-02-21-19-35-01-snagit-0025

Stop the Storefront services if they are still running before continuing.

Close Powershell

**Note – it is very important you close the PowerShell console at this point and reopen a new one before continuing below and attempting to remove StoreFront’s Feature Classes, otherwise the removal of the Feature Classes will fail**

Open a new elevated Powershell console (see warning above).  Add just the Delivery Services Framework snapin,  remove all the Feature Classes, then confirm they are all removed.

###Add the Citrix Delivery Services Framework Powershell Snapin
add-pssnapin Citrix.DeliveryServices.Framework.Commands

###Remove all FeatureClasses
Uninstall-DSFeatureClass -all -confirm:$false

###Verify all Feature Classes have been removed - you should only see {} listed
Get-DSFrameworkController

2017-02-21-19-39-03-snagit-0026

If there are no DSFeatureClasses are still listed, skip to Citrix.DeliveryServices.UninstallUtil.exe below.  Otherwise, some extra manual cleanup is going to be required.  Using your favorite text editor (Notepad++ in my case), open the Framework.xml file (I usually just run the following from an elevated command prompt.

start notepad++ "C:\Program Files\Citrix\Receiver StoreFront\Framework\FrameworkData\Framework.xml"

Within Notepad++, search for the tag “<Type>” to get all the guid’s of any remaining Feature Classes.

Back in the elevated Powershell console, repeatedly run the Uninstall-DSFeatureClass for each <Type> guid you found in the Framework.xml:

Uninstall-DSFeatureClass -Type {guid}

Run the uninstall for each guid one at a time – if you get an error, don’t worry about it, skip it and continue on with the next one.  Once you have gone through all of the guids, run:

Uninstall-DSFeatureClass -all -confirm:$false
Get-DSFrameworkController

Verify all DSFeatureClasses have now been removed.  You may need to repeat the above three steps a few times to completely remove all the DSFeatureClasses due to dependencies within them.

**note – don’t forget to reload/refresh Framework.xml in your text edit of choice if you need to go back and do it again to the list of the remaining DS Feature Classes**

Once all DS Feature Classes have been removed, close PowerShell and open an elevated Command Prompt and run Citrix.DeliveryServices.Install.Uninstall.exe.

C:\ProgramData\Citrix\DeliveryServicesUninstall\UninstallUserInterface\Citrix.DeliveryServices.Install.Uninstall.exe

2017-02-21-19-41-51-snagit-0028

StoreFront should successfully uninstall for you now and disappear from Add/Remove programs.  Reboot the machine from the elevated command prompt:

shutdown /f /r /t 0

After logging back in, open an elevated Command Prompt and cleanup any leftover folders by running:

rd /q /s "C:\ProgramData\Citrix\DeliveryServicesUninstall"
rd /q /s "C:\Program Files\Citrix\Receiver StoreFront"

Don’t worry if you get a “The system cannot find the file specified” error message – that just means the folder has already been removed or doesn’t exist anymore.

Finally, using Windows Explorer navigate to C:\Inetpub\wwwroot and verify the Citrix directory has been removed – if it has not been removed, manually check it’s contents for anything you need to keep and then delete C:\Inetpub\wwwroot\Citrix.

You should now be ready to install a fresh version of StoreFront.

HOWTO: Enable Jumbo Frames in Windows 2012R2 VMs via #PowerShell

@DeanColpittsLeave a comment

Cleaning up my Inbox, I found this nugget that I had sent to myself and I figured I would share… For reasons that now escape me, a few months ago I had the need to enable Jumbo Frames inside of several VMs (ESXi 6.x). Sure, I could have used the mouse and the Network Control panel to do so, but why wear out the ball on my trackball needlessly when I had a ton of HP keyboards laying about that I could abuse instead… So, off to PowerShell!

Basically, open an Administrative PowerShell and run one of these three sets of commands depending on the type of vNIC you have.

For the E1000E:

$NICNameE1000E = Get-NetAdapter | Where-Object { $_.InterfaceDescription –Like "Intel(R) 82574L Gigabit Network Connection" } | Select -expand nameSet-NetAdapterAdvancedProperty -Name $NicNameE1000E –DisplayName "Jumbo Packet" –DisplayValue "9014 Bytes"

For the E1000:

$NicNameE1000 = Get-NetAdapter | Where-Object { $_.InterfaceDescription –Like "Intel(R) PRO/1000 MT Network Connection" } | Select -expand nameSet-NetAdapterAdvancedProperty -Name $NicNameE1000 –DisplayName "Jumbo Packet" –DisplayValue "9014 Bytes (Alteon)"

For the VMXNET3:

$NicNameVMXNet = Get-NetAdapter | Where-Object { $_.InterfaceDescription –Like "vmxnet3 Ethernet Adapter" } | Select -expand nameSet-NetAdapterAdvancedProperty -Name $NicNameE1000 –DisplayName "Jumbo Packet" –DisplayValue "Jumbo 9000"

As always – Use any tips, tricks, or scripts I post at your own risk.

HOWTO: Approve just the #WSUS updates that are needed using #PoshWSUS and #PowerShell on a schedule

@DeanColpittsLeave a comment

Recently we deployed a new “do-all, be-all” server (AD, WSUS, etc) to a small client with very poor bandwidth availability.  Just allowing WSUS to auto-approve every published update across all categories and all select products just wasn’t an option (and really shouldn’t be anyways) because it would have taken a month to download them over 3Mbps DSL.  So I wanted to only approve and download those updates that were needed, as opposed to WSUS built-in process of automatic approve everything in a classification or product then immediately trying to download it.

For a bit more background information, typically we configure WSUS at our smaller “hands off clients” to check for new updates at 4 am daily (although I’m considering switching to manual synchronization and using PowerShell to invoke the check on a schedule that is more “robost” shall we say.  This gives us about a day to vet the basic updates on our own production system (which is typically close in configuration to our smaller “hands off clients”) before the updates auto-approve after synchronization at the client locations.

In the past I have played with the native Windows Server Update Services PowerShell cmdlets, but lets be honest, they are only about half baked and certainly not ready for enterprise IT production (I’m really not sure what Microsoft was thinking).  The inability to auto-accept EULAs is bad.  Even worse is that if there is a EULA waiting to be accepted, Approve-WsusUpdate just pukes, exits, and refuses to acknowledge, and refuses to approve any other updates (even without EULAs on them).  So this means there really is no hands free way provided by Microsoft to auto-approve just the needed updates as they come out…

After some research, I settled on PoshWSUS.  In my honest opinion however, it is not well documented or supported (but it is good given the resources the author likely has at his disposal)…  It took me 4 hours of scouring Google and trial and error to come up with this basic script, which simply looks at all the needed updates and approves them, in the fewest lines of PowerShell possible.  In the end I had to mash together both the native WSUS PowerShell cmdlets and the PoshWSUS cmdlets to make this work, but it does work (so far anyways).

So, download PoshWSUS_2_3_1_0.zip from GitHub, drop it in C:\Windows\System32\WindowsPowerShell\v1.0\Modules and lets go…

First of all, we need to find just updates that are needed – err – rather **ALL** the updates that are needed.  I didn’t find a way in PoshWSUS to do this, but the native WSUS cmdlets let you do this.  Great – but you can’t pipe these updates (directly out of Get-WsusUpdate) into PoshWSUS’s Approve-PSWSUSUpdate, and I couldn’t find any way to get all needed updates out of Get-PSWSUSUpdate (which can pipe to Approve-PSWSUSUpdate), so I needed to get creative…  Get-PSWSUSUpdate, using a piped Where will allow you to select by the UpdateId, and Get-WsusUpdate will output UpdateId…  Hmmm….

Now – how to get the UpdateId from Get-WsusUpdate to Get-PSWSUSUpdate…  How about by piping the UpdateId via >> to a text file, then use foreach to read that text file into Get-PSWSUSUpdate, which then pipes to Approve-PSWSUSUpdate…  It’s ugly, it’s slow, but hey it works, and should only take a minute or two to run even on the heaviest of Patch Tuesdays…  And along the way, the text file needs cleaned up before being fed into Get-PSWSUSUpdate.

So here is the entire process (save this script as C:\Windows\WSUS_Updates_Approval.ps1)…

Import-Module PoshWSUS
$DNSDomainName=$env:userdnsdomain
$ThisComputersFQDN = "$env:computername"+"."+"$DNSDomainName"
Connect-PSWSUSServer -WsusServer $ThisComputersFQDN -Port 8530
$UpdateIDFile = "C:\Windows\Temp\UpdateIDFile.txt"
If (Test-Path $UpdateIDFile){Remove-Item $UpdateIDFile}
Get-WsusUpdate -Approval Unapproved -Status Needed | Select-Object UpdateId >> $UpdateIDFile
(Get-Content $UpdateIDFile| Select-Object -Skip 3) | Set-Content $UpdateIDFile
(Get-Content $UpdateIDFile| Foreach {$_.TrimEnd()}) | Set-Content $UpdateIDFile
$Groups = Get-PSWSUSGroup -Name 'All Computers'
foreach ($NewUpdateID in get-content $UpdateIDFile) {Get-PSWSUSUpdate | Where {$_.UpdateID -eq "$NewUpdateID"} | Approve-PSWSUSUpdate -Action Install -Group $Groups -Confirm:$false}

Finally, we just need to schedule this script to run on a regular basis… I typically schedule it to run every 15 minutes.  To do that, run this in an Administrative Command Prompt:

schtasks /create /tn "WSUS Updates Approval" /tr "\"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe\" -ExecutionPolicy RemoteSigned -noprofile -File C:\Windows\WSUS_Updates_Approval.ps1" /sc minute /mo 15 /st 00:02:00  /rp "*" /ru "%userdomain%\%username%"

As always – Use any tips, tricks, or scripts I post at your own risk.

HOWTO: Install Windows Server 2012 R2 #WSUS via script (including all prereqs)

@DeanColpittsLeave a comment

It’s probably no secret that I’m a fan of scripting.  I love the ability to be able to get consistent and even results on every job, install or upgrade my support team or I do.  The end result is it makes installations and updates faster.  And it definitely makes everyone’s life easier when trouble finds you – all you have to do is compare your broken system to a working system that is configured the same way and you can usually figure out the issue.  Also I’ve been around the industry long enough to remember DOS 5, DOS 6.22, and early versions of OS/2 (not to mention the Vic20, C64 and Amiga 500s I had over the years) – I’m generally always faster on a keyboard than with a mouse (although I actually use a trackball when I’m at my desk), albeit sometimes the mouse is just simpler – but those cases are few and far between in an enterprise class IT world.

One of the tools I end up often having to install from scratch at client locations is WSUS (Windows Server Update Services).  And it’s a pain in the ass to install.  You need to install IIS, you need to install SQL, you need to install WSUS, and then you have to configure WSUS itself.  All time consuming if you use Server Manager.  But hey – breakout the Command Prompt and PowerShell and you can be done in no time with a few simple commands.

Before you follow these instructions, make sure the server you are doing this on is an already updated Windows 2012 R2 Server with all current Microsoft Updates (otherwise it may cause you extra bullshit and grief later on).  Then after WSUS is installed, be to check for Microsoft Updates (hey – you could probably use your shiny new WSUS server to do that!) again to update the prerequisites you had to install before even installing WSUS.

Speaking of those prerequisites, of course WSUS on Windows Server 2012 R2 has a few prerequisites.  The first one is IIS – it must be installed with specific features. Being lazy, I usually install most all the features with my catch-all IIS installation script.  Open an Administrative Command Prompt and run:

DISM.EXE /enable-feature /online /featurename:IIS-ASP /featurename:IIS-ASPNET /featurename:IIS-ASPNET45 /featurename:IIS-ApplicationDevelopment /featurename:IIS-ApplicationInit  /featurename:IIS-BasicAuthentication  /featurename:IIS-CGI  /featurename:IIS-CertProvider /featurename:IIS-ClientCertificateMappingAuthentication /featurename:IIS-CommonHttpFeatures /featurename:IIS-CustomLogging  /featurename:IIS-DefaultDocument  /featurename:IIS-DigestAuthentication  /featurename:IIS-DirectoryBrowsing /featurename:IIS-HealthAndDiagnostics /featurename:IIS-HttpCompressionDynamic  /featurename:IIS-HttpCompressionStatic /featurename:IIS-HttpErrors /featurename:IIS-HttpLogging /featurename:IIS-HttpRedirect  /featurename:IIS-HttpTracing /featurename:IIS-IIS6ManagementCompatibility /featurename:IIS-IISCertificateMappingAuthentication  /featurename:IIS-IPSecurity  /featurename:IIS-ISAPIExtensions /featurename:IIS-ISAPIFilter /featurename:IIS-LegacyScripts /featurename:IIS-LegacySnapIn /featurename:IIS-LoggingLibraries  /featurename:IIS-ManagementConsole /featurename:IIS-ManagementScriptingTools  /featurename:IIS-ManagementService /featurename:IIS-Metabase  /featurename:IIS-NetFxExtensibility  /featurename:IIS-NetFxExtensibility45 /featurename:IIS-ODBCLogging /featurename:IIS-Performance /featurename:IIS-RequestFiltering /featurename:IIS-RequestMonitor /featurename:IIS-Security /featurename:IIS-ServerSideIncludes  /featurename:IIS-StaticContent /featurename:IIS-URLAuthorization /featurename:IIS-WMICompatibility /featurename:IIS-WebServer  /featurename:IIS-WebServerManagementTools /featurename:IIS-WebServerRole /featurename:IIS-WebSockets /featurename:IIS-WindowsAuthentication /featurename:NetFx4Extended-ASPNET45

Next, we need to install SQL Express.  I’ve been pretty much standardized on SQL Express 2014 SP2 with tools (SQLEXPRWT_x64_ENU.exe) for a while now.  From the command prompt you are going to extract it to C:\TEMP then run an unattended SQL setup using these two commands from an Administrative Command Prompt (**note:  this command line expects you to be using SQL Express 2014 SP2 and not a previous version of SQL Express, even if WSUS supports it**):

SQLEXPRWT_x64_ENU.exe /q /x:"c:\TEMP\SQLEXPRWT_x64_ENU"
c:\TEMP\SQLEXPRWT_x64_ENU\SETUP.exe /ACTION=Install /ADDCURRENTUSERASSQLADMIN /AGTSVCACCOUNT="NT AUTHORITY\LOCAL SERVICE" /AGTSVCSTARTUPTYPE="AUTOMATIC" /BROWSERSVCSTARTUPTYPE="AUTOMATIC" /FEATURES=SQLENGINE,Replication,Tools /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS /INSTANCEID=MSSQLSERVER /INSTANCENAME=MSSQLSERVER /NPENABLED=1 /QS /ROLE=AllFeatures_WithDefaults /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /SQLSYSADMINACCOUNTS="Administrators" /SQMREPORTING=0 /TCPENABLED=1

WSUS also has a prerequisite that IIS_WPG, NETWORK, NETWORK SERVICE, and SERVICE have “Log on a service” rights on the WSUS server.  Unfortunately, this part is a mouse job (I haven’t done the research yet to script this).  Start the Local Security Policy Management Console on the WSUS server. Navigate to Local Policies –> User Rights Assignment branch, edit the “Log on as a service” setting, and add the following four accounts (you should be able to cut and paste these into the add accounts dialog box):

IIS_WPG; NETWORK; NETWORK SERVICE; SERVICE

There is a chance IIS_WPG won’t exist, don’t worry about it, just remove it and keep on going (if it doesn’t exist now, then it isn’t going to exist when WSUS is installed either).  After closing Local Security Policy, don’t forget to update the policy with “gpupdate /force” from the Administrative Command Prompt before continuing.

Now open an Administrative PowerShell and run:

Install-WindowsFeature -Name UpdateServices-Services, UpdateServices-DB -IncludeManagementTools

Once Install-WindowsFeature completes, back in the Administrative Command Prompt run:

"C:\Program Files\Update Services\Tools\wsusutil.exe" postinstall SQL_INSTANCE_NAME="%computername%" CONTENT_DIR=D:\SHARED\WSUS

When wsusutil.exe has completed, if KB3148812, which is a May 2016 Windows Update (or later version of it, which is required for distributing Windows 10 Anniversary or new version updates) has not yet been installed on the server (this is why told you earlier to start with a fully patched Windows 2012 R2 Server because you don’t need to deal with this bullshit if you do), after installing KB3148812 (or whatever Microsoft superseded it with) you must open an Administrative PowerShell and run:

Install-WindowsFeature -Name NET-WCF-HTTP-Activation45

Then, still related to KB3148812 (see my bullshit comment above – you should have started with a fully patched Windows 2012 R2 Server to begin with), open an Administrative Command Prompt and run:

"C:\Program Files\Update Services\Tools\wsusutil.exe" postinstall /servicing

Finally, after taking care of all the KB3148812 bullshit (I told you that you should have started with a fully patched Windows 2012 R2 Server to begin with), or if you smart enough to actually take my advice and start with a fully updated Windows 2012 R2 server, it’s finally time to the launch the WSUS console and run the configuration wizard by running this from the Administrative Command Prompt :

"C:\Program Files\Update Services\AdministrationSnapin\wsus.msc"

Lastly, if you are lazy like me, you’ll just next, next, next all the way through the WSUS Setup Wizard, skipping the “Synchronize Now” option.  Once you end up at the normal WSUS console, open a new Administrative PowerShell and run:

Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Applications"} | Set-WsusClassification Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Critical Updates"} | Set-WsusClassification Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Definition Updates"} | Set-WsusClassification Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Security Updates"} | Set-WsusClassification Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Service Packs"} | Set-WsusClassification Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Tools"} | Set-WsusClassification Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Update Rollups"} | Set-WsusClassification Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Updates"} | Set-WsusClassification Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Drivers"} | Set-WsusClassification -Disable Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Feature Packs"} | Set-WsusClassification -Disable Get-WsusServer | Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Upgrades"} | Set-WsusClassification -Disable Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "CAPICOM"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Developer Tools, Runtimes, and Redistributables"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Dictionary Updates for Microsoft IMEs"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Exchange Server 2007 and Above Anti-spam"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Exchange Server 2013"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Exchange Server 2016"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Expression"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Expression Design 1"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Expression Design 2"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Expression Design 3"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Expression Design 4"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Expression Media 2"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Expression Media V1"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Expression Web 3"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Expression Web 4"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Microsoft SQL Server 2012"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Microsoft SQL Server 2014"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Microsoft SQL Server 2016"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Microsoft SQL Server 2017"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Microsoft SQL Server Management Studio v17"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "New Dictionaries for Microsoft IMEs"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Office 2002/XP"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Office 2003"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Office 2007"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Office 2010"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Office 2013"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Office 2016"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Report Viewer 2005"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Report Viewer 2008"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Report Viewer 2010"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "SDK Components"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "SQL Server"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "SQL Server 2000"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "SQL Server 2005"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "SQL Server 2008"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "SQL Server 2008 R2"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "SQL Server 2012 Product Updates for Setup"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "SQL Server 2014-2016 Product Updates for Setup"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "SQL Server Feature Pack"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Silverlight"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Visual Studio 2005"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Visual Studio 2008"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Visual Studio 2010"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Visual Studio 2010 Tools for Office Runtime"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Visual Studio 2012"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Visual Studio 2013"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Windows 10 LTSB"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Windows 2000"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Windows 7"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Windows Server 2008 R2"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Windows Server 2012 R2"} | Set-WsusProduct Get-WsusServer | Get-WsusProduct | Where-Object -FilterScript {$_.Product.Title -Eq "Windows Server 2016"} | Set-WsusProduct

You can then go back to the WSUS console, select Options, and now Products and Classifications.  The above PowerShell commands will give you a great Product and Classification base to start your WSUS server configuration from without all the mouse clicks during the configuration wizard.

Congratulations – you now have a fully installed and configured WSUS server!

As always – Use any tips, tricks, or scripts I post at your own risk.