truncate a text object's output

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Daniel Krebs Daniel Krebs 2 years, 6 months ago.

  • Author
  • #21319
    Profile photo of Grant Harrington
    Grant Harrington

    I've got a function that extracts data from AD which contains information in the "info" field (Telephones>Notes in the ADUC GUI). I keep training records in a standardized date format on the first row of each record: 2014 Training Completed: xx/xx/xxxx I also have other minor data in that field. The following script works fine for extracting and formatting the data for Excel. However in Excel, I need to run a =LEFT(A1,35) to only include that relevant first line of text. Which also works, but involves more work after the export from POSH has happened.

    [blockquote]2014 Training Completed: xx/xx/xxxx
    Some other text, some more text[/blockquote]
    results in
    [blockquote]2014 Training Completed: xx/xx/xxxx[/blockquote]

    My question: is there a way to take the $ data and truncate it by the left most 35 characters as part of the function? Output the same data, just have POSH (v4) handle the truncation as part of the function, saving the extra step from having to be done in Excel?

    function GET-REPORTS {
        [CMDLETBINDING(SupportsPaging = $true,
                    SupportsShouldProcess = $true)]
        BEGIN {
        $DATE = Get-Date -format 'yyMMdd_hhmm'
        $CSVPATH = "CSV-PATH"
        $GETREP = Get-ADUser -filter * -Properties * | where {$ -like '*Training*' -and $_.enabled -eq $TRUE} | select Name,Info,PasswordLastSet,LastLogonDate
        } #end BEGIN
        PROCESS {
        $GETREP | Export-Csv "$CSVPATH\$DATE-TRAINING_REPORT.csv" -NoTypeInformation
        } #end PROCESS
        END {
        } #end END
    } #end GET-REPORTS
  • #21321
    Profile photo of tommymaynard

    First thing, since you're only selecting four properties, you may want to change -Properties * to -Properties Info,PasswordLastSet,LastLogonDate. Think about it, you're returning all the user's properties, and then only using three, that aren't included by default.

    The more you can do in PowerShell the better, because really, who wants to be an Excel superstar? You need to employ the use of calculated properties. Search around for some examples, as well, take a look at my example below. Good luck; this should get you the proper results.

    PS C:\> $x = '2014 Training Completed: 12/05/2014 Hello Freakshow!'
    PS C:\> $x.Substring(0,35)
    2014 Training Completed: 12/05/2014
    PS C:\> Get-ADUser username -Properties Info,PasswordLastSet | Select-Object Name,@{N='Info';E={($_.Info).Substring(0,35)}},PasswordLastSet
    Name           				Info                                    PasswordLastSet
    ----                    	----                                    --------------- #These are supposed to be dashes
    Lastname, Firstname			2014 Training Completed: 12/05/2014     9/16/2014 9:49:47 AM
  • #21322
    Profile photo of Grant Harrington
    Grant Harrington

    Brilliant! That's a technique I'll add to my workflow. That gave the desired results without having to modify data in Excel. I agree with your comment about only grabbing properties needed and understand the reason why, I just threw this script together this morning and will refine with your suggestions.


    P.s. Can't I be both?

    function My-Function {
        [CMDLETBINDING(SupportsPaging = $true,
                    SupportsShouldProcess = $true)]
        BEGIN {
        $startypes = 'Powershell','Excel'
        } #end BEGIN
        PROCESS {
        foreach ($star in $startypes)
            {Write-Output "You are a $star Superstar!"}
        } #end PROCESS
        END {
        } #end END
    } #end My-Function

    [blockquote]You are a Powershell Superstar!
    You are a Excel Superstar![/blockquote]

  • #21323
    Profile photo of Daniel Krebs
    Daniel Krebs


    Yes, you can be both once you have learned everything about PowerShell and Excel, and speak at conferences.

    Please check if below works for you. The function will ask your domain controller to filter for the training users and returns only the relevant properties. A calculated property in the Select-Object line will take care of getting the 35 characters from the Info attribute.

    function Get-TrainingReport {
            [String] $OutputPath
        Begin {
            $Date = Get-Date -format 'yyMMdd_hhmm'
            $CsvPath = Join-Path -Path $OutputPath -ChildPath "$Date-TRAINING_REPORT.csv"
        Process {
            $Users = Get-ADUser -Filter { info -like '*Training*' -and enabled -eq $True } -Properties Name, Info, PasswordLastSet, LastLogonDate | 
                Select-Object -Property Name, @{Name='Info';Expression={$_.Info.Substring(0,35)}}, PasswordLastSet, LastLogonDate |
                    Export-Csv -Path $CsvPath -NoTypeInformation
    Get-TrainingReport -OutputPath C:\MyReportFolder

You must be logged in to reply to this topic.