Getting the expiration dates for computer certificates with PowerShell

$certs = get-childitem cert:\LocalMachine\My

$certs | foreach-object {

$output = new-object PSObject

$output | add-member NoteProperty -name Subject -value $_.Subject

$output | add-member NoteProperty -name FriendlyName -value $_.FriendlyName

$output | add-member NoteProperty -name Issuer -value $_.Issuer

$output | add-member NoteProperty -name ExpirationDate -value $_.GetExpirationDateString()

$output

}

 

Advertisements

Keeping a computer awake for the rest of the day

We have over 90,000 clients, 50 domains, various group policies for power settings as well as Configuration Manager power management settings on various collections.  We also use wake up timers, but sometimes a client will seemingly ignore it.  When a deployment is scheduled to install during the maintenance window (starting at 10 PM), it’s not always straightforward why a particular machine might sleep and miss the deployment.  For these one or two machines that always have “Waiting for maintenance window” as the status, I just wanted to have it stay awake, just for the rest of the day so it can get the deployment, and figure out the sleep issue later.

My thought was just to create a scheduled task that runs every 10 minutes for the rest of the day to set the power policy to “High Performance”.  I created a power policy on a workstation, exported it, and saved it as a template.  I need to have a way to edit the template to make the StartBoundary and EndBoundary be the date of the day it is run.  I edited the xml to be:

YYYY-MM-DDT00:00:00
YYYY-MM-DDT23:59:59

I then created a PowerShell script that, using the template, creates a new xml with the current date replacing the YYYY-MM-DD.

The first piece is the file template-insomnia.xml.  I am uploading it as a docx, copy the contents and save it as template-insomnia.xml.

template-insomnia

The second piece is the ps1 file (editxml.ps1) that will grab the date and creates a new xml file.

editxml.ps1:

$a = Get-Date

If ($a.Day -gt 9) {$Day = $a.Day} Else {$Day = "0" + $a.Day}

If ($a.Month -gt 9) {$Month = $a.Month} Else {$Month = "0" + $a.Month}

$Year = $a.Year

cat '.\template-insomnia.xml' | %{$_ -replace "YYYY-MM-DD","$Year-$Month-$Day"} > '.\Set High Performance Power Scheme.xml' 

The final piece is a batch file (insomnia.bat) that I run from the command line that checks to see if the client is up and running, and if so, creates the scheduled task.

insomnia.bat:

ping -w 1000 -n 1 %1
if %errorlevel%==0 goto goodping
echo %1 Offline
goto out

:goodping
powershell .\editxml.ps1
schtasks /delete /s %1 /tn "Set High Performance Power Scheme" /f
schtasks /create /s %1 /xml "Set High Performance Power Scheme.xml" /tn "Set High Performance Power Scheme"
del "Set High Performance Power Scheme.xml"
:out

Then from the command line:

C:\Users\administrator\Insomnia>insomnia.bat WORKSTATION.DOMAIN.NAME

C:\Users\administrator\Insomnia>ping -w 1000 -n 1 WORKSTATION.DOMAIN.NAME

Pinging WORKSTATION.DOMAIN.NAME [10.77.241.167] with 32 bytes of data:
Reply from 10.77.241.167: bytes=32 time=1ms TTL=117

Ping statistics for 10.77.241.167:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 1ms, Maximum = 1ms, Average = 1ms

C:\Users\administrator\Insomnia>if 0 == 0 goto goodping

C:\Users\administrator\Insomnia>powershell .\editxml.ps1

C:\Users\administrator\Insomnia>schtasks /delete /s WORKSTATION.DOMAIN.NAME /tn "Set High Performance Power Scheme" /f
ERROR: The system cannot find the file specified.

C:\Users\administrator\Insomnia>schtasks /create /s WORKSTATION.DOMAIN.NAME /xml "Set High Performance Power Scheme.xml" /tn "Set High Performance Power Scheme"
SUCCESS: The scheduled task "Set High Performance Power Scheme" has successfully been created.

C:\Users\administrator\Insomnia>del "Set High Performance Power Scheme.xml"

C:\Users\administrator\Insomnia>

That’s it.  As long as the user does not shut down the workstation, it won’t go to sleep for the rest of the day and will still be awake at the beginning of the maintenance window.

Finding computer names for certain status message IDs

We had a evaluation of our infrastructure and several message IDs were identified that pointed to “broken clients” and the recommendation was to reinstall the client by using client push from the console.

For instance, we got several workstations that returned status message ID 10901 – “Client(s) failed to create Software Metering usage data file”.  Easy enough to identify and build a collection with direct members (limit it to the “All Workstation Clients” collection as Servers are handled by another group.

select distinct MachineName from v_StatMsgWithInsStrings sm

where MessageID = 10901

and sm.ResourceID in (select ResourceID from v_FullCollectionMembership fcm inner join v_Collection c on c.CollectionID = fcm.CollectionID where c.Name = 'All Workstation Clients')

order by MachineName

10901

Build the collection “Message ID 10901”, add  the computers with the right-click tools, and schedule the reinstall of the client.  Wait a day or two.

Next questions are: how to identify new machines that returned the error? and, how to identify machines that already had the client push scheduled for them, but still returned the error again?

select MachineName, max (time), fcm.CollectionID from v_StatMsgWithInsStrings sm

left join v_FullCollectionMembership fcm on fcm.ResourceID = sm.ResourceID and fcm.CollectionID = (select CollectionID from v_Collection where Name = 'Message ID 10901')

where MessageID = 10901

and sm.ResourceID in (select ResourceID from v_FullCollectionMembership fcm inner join v_Collection c on c.CollectionID = fcm.CollectionID where c.Name = 'All Workstation Clients')

group by MachineName, fcm.CollectionID

having max (time) > convert (varchar, getdate (), 110)

order by MachineName

10901.2

If the client was already in the “Message ID 10901” collection, it would show in the CollectionID column.

Data validation input on a report (for Custom Bitlocker data)

The Active Directory and Bitlocker teams have been putting together a custom database with a backup of all Bitlocker keys.  They want to have a report where the helpdesk can specify either a computer name, or the first 8 characters of the bitlocker GUID.

The table consists of 5 columns – Domain, ComputerName, Bitlocker Timestamp, Bitlocker Recovery Key, and Bitlocker GUID.

BL

The report has a parameter, @SearchColumn, which is either ‘computerName’ or ‘BLGuid’.  Once that parameter is set, they can enter the computer name, or the first 8 characters of the recovery key ID.  The sticking point was to ensure that exactly 8 characters were entered.  I wasn’t sure how to do it on the reporting server side, but it was easy enough to do it once the user clicks “Show Report”.

I set a variable called @ValidValue to FALSE if the string length of @SearchValue is not 8.  If it is not, the report displays one row with ‘Bitlocker GUID must have 8 characters’ in the Bitlocker GUID field.

DECLARE @ValidValue NVARCHAR (10) = 'TRUE'

IF @SearchColumn = 'BLGuid' AND LEN (@SearchValue) <> 8 SET @ValidValue = 'FALSE'

 SELECT

 [domain]

, [computerName]

, [BLTimestamp]

, [BLRecoveryPW]

, [BLGuid]

FROM [ADBitLockerBackup].[dbo].[ADBitLockerBackup]

WHERE (@SearchColumn = 'computerName' AND computerName = @SearchValue)

OR (@SearchColumn = 'BLGuid' AND @ValidValue = 'TRUE' AND BLGuid LIKE '{' + @SearchValue + '%')

UNION ALL

SELECT TOP 1

NULL [domain]

, NULL [computerName]

, NULL [BLTimestamp]

, NULL [BLRecoveryPW]

,'Bitlocker GUID must have 8 characters' [BLGuid] FROM [ADBitLockerBackup].[dbo].[ADBitLockerBackup] WHERE @ValidValue = 'FALSE'

Content on a specific distribution point

I know there is a built in report for this, but I find it faster to run from SSMS, and easier to copy and paste; in case I need to get a list of failed packages to put into a text file for redistribution via http://eskonr.com/2013/09/sccm-configmgr-2012-powershell-script-redistribute-multiple-packages-to-single-distribution-point/.

select

PackageID

, State

, DATEADD(HH, DATEDIFF (hh, GETUTCDATE(),GETDATE()), SummaryDate) as SummaryDate

, InstallStatus

, SecuredTypeID

from v_PackageStatusDistPointsSumm

where ServerNALPath like '%SERVER.DOMAIN.NAME%'

order by SummaryDate

How to find the package source path for applications’ deployment types

We have to change the \\server.domain\share name on all of our packages and applications.  It’s pretty easy to find for most objects, but not deployment types.

select ObjectTypeID, Name, PkgSourcePath from v_Package where PkgSourcePath <>  ''

will give the source path for the following ObjectTypeIDs:

2 – Software package
14 – Operating system upgrade package
18 – Operating system image package
19 – Boot image package
23 – Driver package
24 – Software update package

(There may be others, but these are what we have.)

For deployment types, the data is contained in an xml data type that can be queried with the v_ConfigurationItems view.

WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest')

select
  SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Title)[1]', 'nvarchar(max)') [Title]

, SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Installer/Contents/Content/Location)[1]', 'nvarchar(max)') [SourcePath]

, SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Installer/InstallAction/Args/Arg)[1]', 'nvarchar(max)') [Install]

, SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Installer/UninstallAction/Args/Arg)[1]', 'nvarchar(max)') [Uninstall]

from v_ConfigurationItems

where CIType_ID = 21 and IsLatest = 1

order by Title

See the references at the bottom – the one from the forum was most helpful… the question was how to query for the dependencies on an application.  This can be done with:

WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest', 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' as ns )

select

 SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Title)[1]', 'nvarchar(MAX)') [Title]

, SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Dependencies/ns:DeploymentTypeRule/ns:Annotation/ns:DisplayName/@Text)[1]', 'varchar(50)') [SoftwareDependencyName]

, SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Dependencies/ns:DeploymentTypeRule/ns:Annotation/ns:DisplayName/@Text)[2]', 'varchar(50)') [SoftwareDependencyName]

, SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Dependencies/ns:DeploymentTypeRule/ns:Annotation/ns:DisplayName/@Text)[3]', 'varchar(50)') [SoftwareDependencyName]

, SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Installer/InstallAction/Args/Arg)[1]', 'nvarchar(max)') [Install]

, SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Installer/UninstallAction/Args/Arg)[1]', 'nvarchar(MAX)') [Uninstall]

from v_ConfigurationItems

where CIType_ID = 21 and IsLatest = 1

order by Title

For more than 3 references, keep adding new columns to return and change the digit in the []:

, SDMPackageDigest.value ('(/AppMgmtDigest/DeploymentType/Dependencies/ns:DeploymentTypeRule/ns:Annotation/ns:DisplayName/@Text)[4]', 'varchar(50)') [SoftwareDependencyName]

References:

https://social.technet.microsoft.com/Forums/en-US/46302409-576c-492c-a64d-c7aa09d4f610/xml-query-on-application-dependencies?forum=configmanagerapps

https://docs.microsoft.com/en-us/sql/t-sql/xml/with-xmlnamespaces

https://docs.microsoft.com/en-us/sql/relational-databases/xml/add-namespaces-to-queries-with-with-xmlnamespaces

https://docs.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/xml/retrieve-and-query-xml-data

https://docs.microsoft.com/en-us/sql/t-sql/xml/xml-data-modification-language-xml-dml

https://docs.microsoft.com/en-us/sql/t-sql/xml/xml-data-type-methods

https://docs.microsoft.com/en-us/sql/t-sql/xml/query-method-xml-data-type

https://docs.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type

Where do the “Office 365 Client Management” numbers come from?

Looking in the console, we have the following pie chart:

o365piechart

Hovering over the orange section informs us that out of the 88,114 O365 clients, 76,470 are managed.

o365piechartorange

The question then was, where did these numbers come from, and how could we identify those in the blue section (“unmanaged”?)?

Using SQL Profiler, it was determined that the stored procedure spDashboardsGetChartData was being called…

EXEC spDashboardsGetChartData @chartGuid=N'0eee5653-518b-4508-b00c-d4766b39b79c', @chartConfigData=N'SMS00001',@userSids=N'user sids removed'

Within the stored procedure, the chartGuid is used to specify another stored procedure, spGetO365ClientCount, using the following:

SELECT @sqlHandler = SqlHandler FROM Console_DashboardCharts WHERE ChartGuid = @chartGuid

The spGetO365ClientCount counts the following:

SET @memberCount = (

SELECT COUNT(*) FROM dbo.vSMS_O365Configurations WHERE SiteID = @collectionID 

)

SET @office365Clients = (

SELECT COUNT(*) FROM dbo.vSMS_O365Configurations WHERE SiteID = @collectionID AND VersionToReport00 <> ''

)

SET @managedOffice365clients = (

SELECT COUNT(*) FROM dbo.vSMS_O365Configurations WHERE SiteID = @collectionID AND VersionToReport00 <> '' AND CCMManaged00 <> ''

)

Thus, a list of “unmanaged” can be generated via:

SELECT s.ResourceID

, s.Full_Domain_Name0

, s.Name0

, s.User_Domain0

, s.User_Name0

, o.VersionToReport00

, o.CCMManaged00

FROM dbo.vSMS_O365Configurations o

inner join v_R_System s on s.ResourceID = o.MachineID

WHERE SiteID = 'SMS00001' AND VersionToReport00 <> '' and CCMManaged00 is null