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 1 year, 11 months ago.

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

    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 $_.info 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)]
        param()
        
        BEGIN {
        $DATE = Get-Date -format 'yyMMdd_hhmm'
        $CSVPATH = "CSV-PATH"
        $GETREP = Get-ADUser -filter * -Properties * | where {$_.info -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
    GET-REPORTS
  • #21321
    Profile photo of tommymaynard
    tommymaynard
    Member

    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
    Participant

    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.

    Thanks!

    P.s. Can't I be both?

    function My-Function {
        [CMDLETBINDING(SupportsPaging = $true,
                    SupportsShouldProcess = $true)]
        param()
        
        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
    Participant

    Grant,

    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 {
    
        [CmdletBinding()]
        Param
        (
            [Parameter(Mandatory=$true)]
            [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.