truncate a text object's output

This topic contains 3 replies, has 3 voices, and was last updated by  Daniel Krebs 3 years, 3 months ago.

  • Author
  • #21319

    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


    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

    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

    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.