none
Trying to output to a csv RRS feed

  • Question

  • Hi Scripting Guys

    I'm new to power shell scripting and I have been tasked to write a few different scripts; one particularly to be scheduled to run every day, on a few PCs, get the age of the local admin password and if it is older than 30 days... output it to a file on a local SMB NAS.  Everyday it will create a new file with the current date in the file name... This is what I came up with (my very first script I ever wrote):

       

    new-psdrive -Name "S" -PSProvider FileSystem -Root "\\FakeShare\Share"
    $Date = (Get-Date).tostring("MMddyy")
    $FileLocation = "S:\Temp\"
    $FileName = ($FileLocation + $Date + “LAPS.txt”)
    $Computername = Hostname
    $30day = (get-date).AddDays(-30).ToString("g")
    $AdmPwd = net user Administrator | find /i "Password last set"
    $Space = "            "
    filter Timestamp {"$(Get-Date -Format G) $_"}
    $Timestamp = Timestamp
    $Title = "Script Run Time"
    $AdmPwd = net user Administrator | find /i "Password last set"
    #$Value = $Computername + $Space + $AdmPwd + $Space + $Timestamp + $Space + $ActiveUser
    $Value = $Computername + $Space + $AdmPwd + $Space + $Title + $Space + $Timestamp

    If ($AdmPwd –gt $30day)
    {
    $Value | Out-file $FileName -append -NoClobber 
    }

    C:
    Remove-PSDrive -Name "S"

    Works good, I get what I need but I there aren't any column headers (I've tried to create them with no luck), doesn't format nicely and I can't seem to filter out the "Password last set" and the long space in-between the 2 values from the output of the $AdmPwd = net user Administrator | find /i "Password last set" command; so I added spaces to make the format more aesthetically pleasing but still not lined up properly.

    I've played around with exporting to a .csv but I'm not there yet with my scripting as some of the formatting and coding is confusing to me. But, What I would really like is to get a csv file that resembles this:


    I would appreciate any assistance!!!! Thank you!


    Jason Ostrowski

    • Moved by Bill_Stewart Friday, July 27, 2018 6:38 PM This is not "debug/fix/rewrite my script for me" forum
    Wednesday, May 2, 2018 5:08 PM

All replies

  • Create a custom object and output using Export-Csv.

    or

    Just add a header to the default file when it is first created..  The header will then always be in the file.

    Using the first suggestion is the best method.


    \_(ツ)_/


    • Edited by jrv Wednesday, May 2, 2018 6:46 PM
    Wednesday, May 2, 2018 6:45 PM
  • So, I played around with some formatting for the .csv and this is the what I came up with so far... But still have 2 nagging issues; the space in-between the 2 values and if the csv is being written to, it's locked and the next PC can't write until the lock has cleared...  I'm pecking away at it, but since I am new to scripting it takes me a few to find the answer(s)… 

    #Variables
    #new-psdrive -Name "S" -PSProvider FileSystem -Root "\\FakeShare\Share$"
    $Date = (Get-Date).tostring("MMddyy")
    #$FileLocation = "S:\Temp\"
    $FileLocation = "C:\Temp\"
    $FileName = ($FileLocation + $Date + “LAPS.csv”)
    $Computername = Hostname
    $30day = (get-date).AddDays(-30).ToString("g")
    $AdmPwd = net user Administrator | find /i "Password last set"
    $Space = "            "
    filter Timestamp {"$(Get-Date -Format G) $_"}
    $ActiveUser = Get-WMIObject -class Win32_ComputerSystem | Select-Object -ExpandProperty username
    $Timestamp = Timestamp

    #CSV Formatting

    #Values
    $Value = @()
    $Value = [pscustomobject] [ordered] @{ "ComputerName" ="$Computername"; "Admin Password Timestamp" ="$AdmPwd"; "Script Run Time" ="$Timestamp"}

    #Script
    If ($AdmPwd –gt $30day)
    {   
    # $Value | Out-file $FileName -append -NoClobber
    $Value | export-csv $FileName -Append -NoTypeInformation -Encoding ASCII
    }


    Jason Ostrowski


    Wednesday, May 2, 2018 9:39 PM
  • Hi jrv

    Thank you for responding! 

    Yeah, remember this is my very first script I am writing (not copying from someone and customizing) so I have to research every thing and even stumble upon some as well... I used PowerShell before but I never really scripted so... 


    Jason Ostrowski

    Wednesday, May 2, 2018 9:44 PM
  • Wow ...  your code looks really quirky. You should urgently take some time and make a little step back and start to learn the very basics of Powershell. That's even free of charge when you take the lessons here: Microsoft Virtual Academy - Getting Started with Microsoft PowerShell.

    Here is a recommendation for your script to start with:

    $FilePath = '\\FakePath\Share$\Temp'
    $FileName = Join-Path -Path $FilePath -ChildPath ((Get-Date -Format 'MMddyy') + 'LAPS.csv')
    
    net user Administrator | Where-Object -FilterScript {$_ -match 'Password last set\s+\w{3},\s*(.+)'}
    $PasswordLastSet = [DateTime]::ParseExact($Matches[1],'MM/dd/yyyy hh:mm:ss',[System.Globalization.CultureInfo]::InvariantCulture)
    
    [pscustomobject][ordered]@{
        ComputerName = $env:COMPUTERNAME
        PasswordLastSet = $PasswordLastSet
        TimeStamp = Get-Date -Format 'MMddyyyy HH:mm:ss'
        ActiveUser = $env:USERNAME
    } |
        Export-Csv -Path $FileName -Append 
    


    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Wednesday, May 2, 2018 11:12 PM
  • Thanks for replaying Olaf

    net user Administrator | Where-Object -FilterScript {$_ -match 'Password last set\s+\w{3},\s*(.+)'}

    Doesn't return anything and I've been scripting for a week so I don't know what \s+\w{3},\s*(.+)'} means.. But, I will continue to peck at it..

    The way I did my script is "clunky" by I understand everything and how it all ties together... The example you gave me seems to be streamlined and cleaner but still confusing to me... 



    Jason Ostrowski


    Thursday, May 3, 2018 1:33 AM
  • net user Administrator | Where-Object -FilterScript {$_ -match 'Password last set\s+\w{3},\s*(.+)'}
    should do pretty much the same like your code
    $AdmPwd = net user Administrator | find /i "Password last set"
    ... and should make the date available for further steps. '\s+\w{3},\s*(.+)' is part of the regular expression I use to extract the date. It should be adjusted to the outpput you get from "net user". It worked in my environment. What does the line looks like in your environment? Here you have some general information about regular expressions: link1 link2
    The way I did my script is "clunky" by I understand
    Therefor my recommendation to learn the basics of powershell from scratch. The video course I linked above will take you about 8 to 12 hours but it will save you from a lot of wasted time and frustration.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''



    • Edited by BOfH-666 Thursday, May 3, 2018 8:04 AM
    Thursday, May 3, 2018 8:01 AM