1. Please help support our sponsors by considering their products and services.
    Our sponsors enable us to maintain high-speed Internet connection and fast webservers.
    They support this free information and knowledge exchange forum service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions

SQL Select statement older than 30 days

Discussion in 'Scripting' started by TaSMania, Feb 6, 2018.

  1. TaSMania

    TaSMania ADSM.ORG Member

    Joined:
    Nov 17, 2015
    Messages:
    75
    Likes Received:
    1
    so I want all the nodes that hasn't been access for over 30 days.
    This work if I run it today.
    Select node_name,lastacc_time from nodes where lastacc_time < '2018-2-06'

    what would I have to replace '2018-12-11' with to make it work?
    I'd tried:
    Select node_name,lastacc_time from nodes where lastacc_time=(current _timestamp-30 days)
    Select node_name,lastacc_time from nodes where lastacc_time= date(current_date)-date)>30
    Select node_name,lastacc_time from nodes where lastacc_time= NO W( ) - INTERVAL 30 DAY
     
  2.  
  3. marclant

    marclant ADSM.ORG Moderator

    Joined:
    Jun 16, 2006
    Messages:
    2,720
    Likes Received:
    377
    Occupation:
    Accelerated Value Specialist for Spectrum Protect
    Location:
    Canada
    Try:
    ... where lastacc_time<current_timestamp-30 days

    Plenty of examples here to steal parts of queries:
    http://thobias.org/tsm/sql/

    Even if the queries are different than what you are looking for, the logic portion can be similar. I just stole the same where clause half an hour ago for a query of my own.
     
  4. TaSMania

    TaSMania ADSM.ORG Member

    Joined:
    Nov 17, 2015
    Messages:
    75
    Likes Received:
    1
    didn't work.
    I look at the link it has similar to what I want but for 90 days. It requires input of current date. I just want it to be able to know it is current so I could implemented into automation.
     
  5. TaSMania

    TaSMania ADSM.ORG Member

    Joined:
    Nov 17, 2015
    Messages:
    75
    Likes Received:
    1
    Ok, using the info from that link and little mofication worked. Since i'm querying multiple servers BKP: i'm getting unwanted data which I'd open another thread.
    thank you
     
  6. TaSMania

    TaSMania ADSM.ORG Member

    Joined:
    Nov 17, 2015
    Messages:
    75
    Likes Received:
    1
    so I'm using this from the link merclant provided(saved favorite):
    SELECT node_name, TO_CHAR(lastacc_time,'YYYY-MM-DD HH24:MI') as "lastacc_time" FROM -
    nodes WHERE DAYS(current_date)-DAYS(lastacc_time)>30 ORDER BY "lastacc_time"

    no where in the statement it mentioned filespaces but output has filespaces. It ruins my csv files because I have -comma parameter.
    when open the output, date and times scattered in column 2 and 3 because some nodes has no filespace.

    column1, column2, column3
    NODE , Files , DATE/time
     
  7. marclant

    marclant ADSM.ORG Moderator

    Joined:
    Jun 16, 2006
    Messages:
    2,720
    Likes Received:
    377
    Occupation:
    Accelerated Value Specialist for Spectrum Protect
    Location:
    Canada
    I only get 2 columns in that output of that query:
    upload_2018-2-8_8-15-26.png
     
  8. TaSMania

    TaSMania ADSM.ORG Member

    Joined:
    Nov 17, 2015
    Messages:
    75
    Likes Received:
    1
    yea, if I take out the -comma parameter I will get output of what you got but I want columns in excel when I > to csv file.
     
  9. marclant

    marclant ADSM.ORG Moderator

    Joined:
    Jun 16, 2006
    Messages:
    2,720
    Likes Received:
    377
    Occupation:
    Accelerated Value Specialist for Spectrum Protect
    Location:
    Canada
    If I do -comma, I still only get 2 columns, node and lastacc_time. There's no filespace field in the nodes table, so I'm not sure how you are getting it.
    upload_2018-2-8_10-58-23.png
     
  10. TaSMania

    TaSMania ADSM.ORG Member

    Joined:
    Nov 17, 2015
    Messages:
    75
    Likes Received:
    1
    hmmm, you're right. I redid it and it is fine. I think I had extra " , " in there somewhere.
     
  11. TaSMania

    TaSMania ADSM.ORG Member

    Joined:
    Nov 17, 2015
    Messages:
    75
    Likes Received:
    1
    So I thought everything works.
    I created bat file to run my sql statement command.
    I'd also created powershell to run that bat file and send report.
    What I don't understand is why when I run the powershell manually it works perfect.
    When I setup a schedule task to run that powershell, data came out empty.
     
  12. TaSMania

    TaSMania ADSM.ORG Member

    Joined:
    Nov 17, 2015
    Messages:
    75
    Likes Received:
    1
    Here's my ps1 file:

    Clear-content D:\ScheduledTasks\30DayOrphans\30dayfile.csv
    Clear-content D:\ScheduledTasks\30DayOrphans\30file.csv
    .\orphans_file.bat
    clear
    $textfile='D:\ScheduledTasks\30DayOrphans\30dayfile.csv'
    $headerfile='D:\ScheduledTasks\30DayOrphans\headers-files.csv'
    $(Get-Content$headerfile; Get-Content$textfile) |Set-Content$textfile

    ###########Define Variables########
    $fromaddress="[email protected]"
    $toaddress="[email protected]"
    $Subject="Orphaned Filespaces"
    $body="The following attachment contains EE filespaces that appear to have been orphaned for a number of reasons.
    Please review the possible explanations and make the necessary corrections."
    $attachment="D:\ScheduledTasks\30DayOrphans\30dayfile.csv"
    $smtpserver="mail.me.com"

    ####################################
    $message=new-objectSystem.Net.Mail.MailMessage
    $message.From =$fromaddress
    $message.To.Add($toaddress)
    $message.Subject =$Subject
    $attach=new-objectNet.Mail.Attachment($attachment)
    $message.Attachments.Add($attach)
    $message.body =$body
    $smtp=new-objectNet.Mail.SmtpClient($smtpserver)
    $smtp.Send($message)
    del file*.csv
     

Share This Page