Getting date date of last month first and last day to query AD CS DB

PowerShell Help
1

I am using a method to obtain the first and last day of the previous month to query for revoked certificates for PKI metrics.
I am using :

$lastmonth = (get-date).AddMonths(-1).ToUniversalTime() # Getting current date
$startofmonth = Get-Date $lastmonth -day 1 -hour 0 -minute 0 -second 0 # Getting First day of last month
$endofmonth = (($startofmonth).AddMonths(1).AddSeconds(-1)) # getting last day of last month

This out puts exactly the first and last day of the previous month.

I am then obtaining the index number of the columns I am querying in the Cert DB like this:

$Commonname_ColumnIndex = $caview.GetColumnIndex(0,“commonname”) #
$requestId_Columnindex = $cAView.GetColumnIndex(0,“RequestID”) #
$notbefore_Columnindex = $cAView.GetColumnIndex(0,“notbefore”) # Getting each column index number and assigning it to a variable
$issued_columnIndex = $cAView.GetColumnIndex(0,“Disposition”) #
$Resolved_ColumnIndex = $cAView.GetColumnIndex(0,“request.resolvedwhen”) #
$CallerName_columnIndex = $cAView.getColumnIndex(0,“request.callername”) #

Then I set my query like this:

$cAView.SetRestriction($issued_columnIndex,$operator[“eq”],0,20) # Setting the filters for the search
$cAView.SetRestriction($Resolved_ColumnIndex,$operator[“gt”],0,$startofmonth ) # getting values from after the first day of previous month
$cAView.SetRestriction($Resolved_ColumnIndex,$operator[“le”],0,$endofmonth ) # getting values from before the last day of previous month

Then the rest of the script iterates over the rows and returns the values for each column.

My trouble is that using the method to query the first and last days of the previous month are 5 hours off. In other words, I only get a subset of the total certs that were revoked. I can validate this by looking at the CA manager GUI and seeing that there are other certs that were not returned in the query. What’s more is that the value returned for request.resolvedwhen and effective date are reporting 5 hours later than actual.

I cant figure out why this is happening. I have been working on this part all day with no resolution.

The last thing I am test is the actual date variables by outputting to the screen the following, and it reports the correct date and time for the first and last days:

write-host $NumExpcerts issued between $startofmonth and $endofmonth

I cant figure out what is adding 5 hours to the times returned in my query. We are UTC -6, BTW. Does anyone have any idea?

Brian

2

Hi Brian.

Regarding the time, you are asking for time in UTC and basing all your date queries of that value 

 $lastmonth = (get-date).AddMonths(-1).ToUniversalTime() # Getting current date
.

Maybe you could try this one 

 $lastmonth = (Get-Date).AddMonths(-1).DateTime

Stein

3

Stein,

Unfortunately, that did not work either. Here is an example or my out put from the script

CommonName : Servicename.ex.domain.edu
RequestID : 693
NotBefore : 9/30/2014 9:28:27 PM
notafter :
Request.ResolvedWhen : 9/30/2014 9:38:28 PM
Status : Issued

What I can see in the GUI cent Manager, after displaying that columns I wanted, is:

Resolution Date Request ID Cert Effective Date Cert Expiration date Issued Common name
9/30/2014 4:38 PM 693 9/30/2014 4:28 PM 9/29/2016 4:28 PM Service.ex.domain.edu

You can see there is a 5 hour difference between my script and the GUI. I also verified the time on the server to be sure.

I am at a complete loss on this one. I can post my full sterilized code if that will help.

Brian

4

I get the same thing if I check it using the GUI vs using powershell, but only if I run against a Certificate that was created before Daylight Saving Time.
If I run it agaisnt a sertificate that was created before DST I get a one hour difference.
It could very well be the same thing you are experiencing. I just cheked the interwebs for some kond of world clock.
I believe that Chicago is on UTC -6. At the moment Chicago is off by five hours.

A SSL sertificate is typically created using UTC for the NotBefore and NotAfter date.
When querying them you need to take that into account and translate accordingly.

You can try this and see if it cuts down on the diff.

Function Convert-UTCToLocalTime
{
    [CmdLetBinding()]
    Param(
        [Parameter(Mandatory)]
        [String]$TimeInUTC
        
    )
    
    $CurrentTimeZone = (Get-WmiObject Win32_TimeZone).StandardName
    $TimeZoneId  = [System.TimeZoneInfo]::FindSystemTimeZoneById($CurrentTimeZone)
    $CurrentLocalTime = [System.TimeZoneInfo]::ConvertTimeFromUtc($TimeInUTC, $TimeZoneId)
    Write-Output $CurrentLocalTime
}

$lastmonth = (get-date).AddMonths(-1).ToUniversalTime() # Getting current date
$lastmonth
Convert-UTCToLocalTime $lastmonth

Stein

5

Stein,

Sorry it took so long to respond.

I am seeing that the function you created above returns the same value as what I get when creating the variable in my script.

For Instance:

$lastMonth = get-date).AddMonths(-1).ToUniversalTime() >>>>> Saturday, October 4, 2014 6:18:06 PM
ConvertTo-UTCLocalTime $lastmonth >>>>>>>>>>>>>>>>>>>> Saturday, October 4, 2014 1:18:06 PM

However, I get the same time as the convert function by using the following…

$lastmonth = (get-date).AddMonths(-1).DateTime >>>>>>>>>>>> Saturday, October 4, 2014 1:18:06 PM

So I am still at a loss as to why, when querying the CertDB, I return a +5 hour difference for the actual notValidAfter, notValidBefore, and resolvedWhen attributes when I should be getting the actual data in the DB not adding hours to the value.

Brian

6

Update,

Since the time change, I am seeing +6 hour difference on the returned value between the certsrv.msc gui and the returned values from my script. We are UTC -6, so it is definitely something to do with the datetime UTC. I just cant figure out what it is. Both, my machine and the server are in the same Time Zone. I even ran the script directly on the CA and it is still showing the +6 hour difference.

Brian

7

Can anyone assist with this please?

8

Hi Brian.

Sorry for the long delay.

In your script please print out the .kind property for your notBefore date.

$certTimeStamp = $cert.NotBefore
Write-Verbose "Using $($certTimeStamp.Kind) to calculate datetime"

I find that this will return ‘local’ on my systems. If the DateTimeKind is set to Local all convertions will be based on the local timezone. This is wrong when you convert from a UTC based string TC string used in the notBefore value.

If the test returns Local you need to tell it to be utc UTC.

$certTimeStampInUTC = [System.DateTime]::SpecifyKind($certTimeStamp, [System.DateTimeKind]::Utc)

Please check the output and let me know if I’m totally off

Stein