• Please help support our sponsors by considering their products and services.
    Our sponsors enable us to serve you with this high-speed Internet connection and fast webservers you are currently using at ADSM.ORG.
    They support this free flow of information and knowledge exchange service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions
  • Community Tip: Please Give Thanks to Those Sharing Their Knowledge.

    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.

  • Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)

    Click the link above to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This notice will disappear after you have made at least 3 posts.

SQL Select statement older than 30 days

TaSMania

ADSM.ORG Member
#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
 

marclant

ADSM.ORG Moderator
#2
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.
 

TaSMania

ADSM.ORG Member
#3
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.
 

TaSMania

ADSM.ORG Member
#4
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
 

TaSMania

ADSM.ORG Member
#5
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
 

TaSMania

ADSM.ORG Member
#7
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.
 

marclant

ADSM.ORG Moderator
#8
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
 

TaSMania

ADSM.ORG Member
#10
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.
 

TaSMania

ADSM.ORG Member
#11
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
 

Advertise at ADSM.ORG

If you are reading this, so are your potential customer. Advertise at ADSM.ORG right now.

UpCloud high performance VPS at $5/month

Get started with $25 in credits on Cloud Servers. You must use link below to receive the credit. Use the promo to get upto 5 month of FREE Linux VPS.

The Spectrum Protect TLA (Three-Letter Acronym): ISP or something else?

  • Every product needs a TLA, Let's call it ISP (IBM Spectrum Protect).

    Votes: 9 22.5%
  • Keep using TSM for Spectrum Protect.

    Votes: 19 47.5%
  • Let's be formal and just say Spectrum Protect

    Votes: 8 20.0%
  • Other (please comement)

    Votes: 4 10.0%

Forum statistics

Threads
30,998
Messages
131,965
Members
21,253
Latest member
mawah
Top