locked
subtraction Dates in Power Shell RRS feed

  • Question

  • Hi All, could do with a bit of Help here

    currently working on a Powershell Script that will E-mail Users when there SFTP password is about to Expire.

    but having trouble subtracting System Date Time to Pass word expiry date in SQL Table

    so i would like to Email Users when there password will expire in 6 Days time. Every thing is working but the the Expiry time in the Emails showing up as 847064000565

      $SqlQuery = "SELECT * FROM dbo.Host_Users
                     WHERE CONVERT(INT,GETDATE()) - CONVERT(INT,Pass_Reset_Date) = 35
                     AND Pass_Expire_Days = 42;"

    $DataSet.Tables[0]|ForEach-Object{
        Write-Output $_

        $systemTime =(Get-Date).ToFileTime()  
        $PassTime = ($_.Pass_Reset_Date).ToFileTime()
        $emailaddress = $_.User_Email_Addr # Get User Email Address
        $name = $_.User_FullName # Get User Full Name
        $userName = $_.User_LoginID # Get User Login Name
        $messageDays = $systemTime - $PassTime

        # Email Subject Set Here
        $subject="SFTP Password due to expire"
        # Email Body Set Here.
        $body ="
        Dear $name,
        <p>Your SFTP password is due to expire in $messageDays </b>
        <p>SFTP Username is $userName.
        <br>
         <br>
        <p>Thanks, <br>
         VGTSI - Support<br>
        </P>"
         # If Testing Is Enabled - Email Administrator
        if (($testing) -eq "Enabled")
        {
            $emailaddress = $testRecipient
        } # End Testing
          # Send Email Message
            Send-Mailmessage -smtpServer $smtpServer -from $from -to $emailaddress -subject $subject -body $body -bodyasHTML -priority High  

            }


    Wednesday, October 28, 2015 2:46 PM

Answers

  • $days= ($_.Pass_Reset_Date - [datetime]::Today).Days + Pass_Expire_Days

    You just have to add the days back if the days is the amount of time from reset to expiration.


    \_(ツ)_/

    • Marked as answer by dave_ie Wednesday, October 28, 2015 4:47 PM
    Wednesday, October 28, 2015 4:38 PM

All replies

  • Why are you converting t file time.  File time is just a long integer

    You want to use [datetime]

    $days= ([datetime]($_.Pass_Reset_Date) - [datetime]::Today).Days

    If the database has that date stored as a date then this is all you need.

    $days= ($_.Pass_Reset_Date - [datetime]::Today).Days


    \_(ツ)_/



    • Edited by jrv Wednesday, October 28, 2015 4:01 PM
    • Proposed as answer by Ruud BorstMVP Wednesday, October 28, 2015 4:20 PM
    Wednesday, October 28, 2015 4:00 PM
  • Thanks for your Reply

    The problem i am facing is the Days are showing up as - 33 Days

    Within the Database we have

    Pass_Expire_Days 42

    Pass_Reset_Date 2015-09-24 14:55:00

    PARAM ()
    BEGIN{
    $smtpServer="IP ADDRESS" #SMTP SERVER
    $from="VGTSI-Support <SUPPORT>"
    $logging="Enabled" # Set to Disabled to Disable Logging
    $testing="Enabled"#Disabled to Email Users
    $testRecipient="EMAIL"
    $support="support" #Email Support Email
    $date = Get-Date -format MMddyyyy # Get Date
    $MaxPasswordAge = 42 # Max Password Age for SFTP Users

        # modify these to point to your db server and database
        $SqlServer = "SERVER" #DB Server
        $SqlDBName = "DB" #Database being Connected to
        # use these for a sql login.  Or you can modify the connection string to use integrated authentication and set the scheduled task to run under that user context (omit User ID and Password from the connect string).
        $SqlLoginUser = "USERNAM" #Username
        $SqlLoginPassword = "PASSWORD" #Password

        #Database Connection and Query
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDBName;Integrated Security=SSPI;Persist Security Info=False;User ID=$SqlLoginUser;Password=$SqlLoginPassword"
        $SqlQuery = "SELECT * FROM dbo.Host_Users
                     WHERE CONVERT(INT,GETDATE()) - CONVERT(INT,Pass_Reset_Date) >30
                     AND CONVERT(INT,GETDATE()) - CONVERT(INT,Pass_Reset_Date) <=42
                     AND Pass_Expire_Days = 42;"


        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $DataSet = New-Object System.Data.DataSet
        [void] $SqlAdapter.Fill($DataSet)
        $SqlConnection.Close() #Close Connection to DB
    }
    PROCESS{

        $DataSet.Tables[0]|ForEach-Object{
        Write-Output $_
        #Get Days Left
        #$expireson = $_.Pass_Reset_Date + $MaxPasswordAge #Last RestDay
        #$today = (get-date) #Get Todays Date
        #$daystoexpire =(New-TimeSpan -Start $today -End $Expireson).Days
        #$emailaddress = $_.User_Email_Addr # Get User Email Address
        $name = $_.User_FullName # Get User Full Name
        $userName = $_.User_LoginID # Get User Login Name
        #$messageDays = $daystoexpire # Check Number of Days to Expiry
        #$messageDays ="$daystoexpire" + " days."

        #$pwdChanged = [DateTime]::Days($_.Pass_Reset_Date)
        #$message = ((get-date) - $pwdChanged).days -ge $MaxPasswordAge
        $days= ($_.Pass_Reset_Date - [datetime]::Today).Days


        $daysleft = $days
        $dayslest = "$days" + "Days"


        $subject="SFTP Password due to expire"   # Email Subject
        # Email Body Set Here.
        $body ="
        Dear $name,
        <p>Your SFTP password is due to expire in <font color=red>$days.</b></font>.<br>
        <p>Your SFTP Username is $userName.<br>
        <p>Thanks,<br>
        VGTSI - Support<br>
        </P>"
         #End Email Body


         # If Testing Is Enabled - Email Administrator
        if (($testing) -eq "Enabled")
        {
            $emailaddress = $testRecipient
        } # End Testing

          # Send Email Message
            Send-Mailmessage -smtpServer $smtpServer -from $from -to $emailaddress -subject $subject -body $body -bodyasHTML -priority High  
            }

            }
    END{}

    Wednesday, October 28, 2015 4:22 PM
  • $days= ($_.Pass_Reset_Date - [datetime]::Today).Days + Pass_Expire_Days

    You just have to add the days back if the days is the amount of time from reset to expiration.


    \_(ツ)_/

    • Marked as answer by dave_ie Wednesday, October 28, 2015 4:47 PM
    Wednesday, October 28, 2015 4:38 PM
  • Thanks a lot you saved me from a lot of headaches that i was starting to get..
    Wednesday, October 28, 2015 4:47 PM
  • You can also do that in sql.  Databases can do date arithmetic.


    \_(ツ)_/

    Wednesday, October 28, 2015 5:16 PM