Automated Query Problem 1

[DiscJockey]

Ok, I am extremely new to SQL. I have a problem where I need to set up an automated query to run every friday at 6pm and email the results to myself and two other persons. The table is 'users' and the fields that I am interested in are the 'user_id' and 'last_login_dt'. I need to know who has not logged into the system for the last 180 days. Any help would be greatly appreciated.

 

[KenCunningham]

I take it this is related to Thread51-1044512 ? As I meant to meantion in that thread, perhaps you could parse the output of last, at least at the OS level.

 

[DiscJockey]

Yes this is related to that thread. I discovered that the info I needed was stored on an Oracle database. I am really new at this, so what is "parse the output

 

[hoinz]

Ok, so there are two options for finding the data you are looking for:
Unix commands and Oracle database.
I think Oracle is the way to go, as Unix wtmp files are deleted now and then, depending on your flavour of Unix. Probably you can't go back 180 days.
Implementing a Unix-only solution might be easier though.
'Parsing' here just means using commands like grep, awk, cut and so on to extract the interesting piece of information from the output of 'last' command.

In Oracle it is quite easy to find a user_id with a last_login_dt older than 180 days. I presume last_login_dt is datatype date:
select user_id from users where last_login_dt < sysdate - 180

However there may be the additional problem of finding users who never logged in, and for whom there is no last_login_dt. You could use another table that contains all the users.

And then you will need some interaction between Unix and Oracle;
for a start look at faq186-2220

hope this helps

 

[DiscJockey]

Ok, I used the example from the referenced post you gave me. It worked directly from the command line as:

print "select user_id, last_login_dt from users where last_login_dt < sysdate - 180;" | sql > lastlogin.txt

This worked great for what I wanted to do. It redirected everything to a file that I could send to people. However, when I tried to build a script with it it kept saying that: sql not found. Any Ideas.

Thanks

 

[hoinz]

Hi,
I am a bit astonished that command 'sql' worked for you; in Unix it is 'sqlplus' usually.
And then, if you are trying it in a script, make sure that your PATH variable is set correctly. You may need other variables like ORACLE_HOME as well.
hope this helps

 

[DiscJockey]

Ok, I found a way this is what I did:

#!/usr/bin/ksh
print "select user_id, last_login_dt from users where last_login_dt < sysdate - 180;" | sqlplus $DB_LOGIN/$DB_PASSWORD > lastlogin.txt

It worked great!

Know I just need to do the emailing.

Thanks a lot guys.

 

[DiscJockey]

Well I am showing my true colors here. I have run into another snag that I hope you guys can help me with. I built the script to email the results to myself and it worked fine from the command line. When I put it into the crontab it blew up. This is the script:

#!/usr/bin/ksh
cd /home/clabay; print "select user_id, last_login_dt from users where last_login_dt < sysdate - 180;" | /u1/oracle/product/8.0.5/bi
n/sqlplus $DB_LOGIN/$DB_PASSWORD > lastlogin.txt


mail someuser@UNIX.com < lastlogin.txt

I had to show cron where to find the sqlplus command. This worked great from the command line...except I did not get a subject line. This is the error that cron sends back to me:

Message file sp1<lang>.msb not found
Error 6 initializing SQL*Plus

I know I must really sound like an idiot, but can anybody help with this problem?

Thanks

 

[hoinz]

Hi,

questions like this have been answered in Unix forums many times before.
When you run in script in cron, it does no know all those environment variables you usually have when you run the same script from command prompt.
So you will have to set them in your script.
To quote myself:
You may need other variables like ORACLE_HOME as well.
Have you set ORACLE_HOME in your script?

hope this helps

 

[DiscJockey]

Thanks Everyone for your help. I finally got it to work.