Report-Specific Column Extracting Date From String

Question

I am trying to create a report-specific column (DateTime - Regular Date) where I can extract the datetime from the description field of the user object using a regular expresssion.

The description field is popluated similar to "Deprovisioned on 1/20/23 8:20:20 AM by me@email.com"

$object = $Context.GetDirectoryObject() $line = $object.Get("description")

$dateTime = ??

$Context.Value = $dateTime

Answer 1

Hello,

The easiest way would be to just add the Description column to the report. However, if you need a column with only the dates from the Description property and it always starts with Deprovisioned on and ends with the required date, you can use the below code. The report specific column should be of the Text type.

$object = $Context.GetDirectoryObject()
$string = $object.Get("description")

$Context.Value = $string.Replace("Deprovisioned on ", "")

Comments

I apologize, my initial question didn't include all the information.

The description field is popluated similar to "Deprovisioned on 1/20/23 8:20:20 AM by me@email.com"

---

Hello,

Unfortunately, there is no easy way to achieve the desired as it requires parsing strings. The best solution here is to preserve the date not only in the text Description property, but also in a custom date attribute (e.g. CustomAttributeDate1). In this case, you will only need to add the attribute column to the report.

Answer 2

I was able to accomplish this with the following value calculation of a regular date custom column.

$description = ""
$object = $Context.GetDirectoryObject()

try{
    $description = $object.Get("description")
}
catch [System.Runtime.InteropServices.COMException]
{}

if($description.Contains("Deprovisioned")){
    $datePattern = "(((((0[13578])|([13578])|(1[02]))[\-\/\s]?((0[1-9])|([1-9])|([1-2][0-9])|(3[01])))|((([469])|(11))[\-\/\s]?((0[1-9])|([1-9])|([1-2][0-9])|(30)))|((02|2)[\-\/\s]?((0[1-9])|([1-9])|([1-2][0-9]))))[\-\/\s]?\d{4})(\s(((0[1-9])|([1-9])|(1[0-2]))\:([0-5][0-9])((\s)|(\:([0-5][0-9])\s))([AM|PM|am|pm]{2,2})))"
    $matches = $description | Select-String -Pattern $datePattern -AllMatches 
    $date = Get-Date $matches.Matches[0].Value
    $tzone = Get-TimeZone -Id "Central Standard Time"
    $Context.Value = $date.AddHours( - ($tzone.BaseUtcOffset.totalhours))
}