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?