SQL Select statement older than 30 days

TaSMania

ADSM.ORG Member
Joined
Nov 17, 2015
Messages
126
Reaction score
1
Points
0
PREDATAR Control23

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
 
PREDATAR Control23

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.
 
PREDATAR Control23

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.
 
PREDATAR Control23

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
 
PREDATAR Control23

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
 
PREDATAR Control23

I only get 2 columns in that output of that query:
upload_2018-2-8_8-15-26.png
 
PREDATAR Control23

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.
 
PREDATAR Control23

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
 
PREDATAR Control23

hmmm, you're right. I redid it and it is fine. I think I had extra " , " in there somewhere.
 
PREDATAR Control23

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.
 
PREDATAR Control23

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
 
Top