MySQL help

[byrdman]

I was wondering if anyone out there could help me. I have a 'log file' generated from the /var/log/secure that tells me my clients logging in.

I have the file trimmed down so I see the following:

May 2 18:19:17 user1 x.x.x.1
May 2 18:19:18 user2 x.x.x.2
May 2 18:19:21 user3 x.x.x.3
May 3 18:19:26 user1 x.x.x.1
May 3 18:19:27 user2 x.x.x.2
May 3 18:19:34 user3 x.x.x.3
May 4 18:19:39 user1 x.x.x.1
May 4 18:19:53 user2 x.x.x.2
May 5 18:22:50 user1 x.x.x.1

I can insert the above into mysql into a single table with the fields:
month, day, time, user, user_ip

My question is how can I build queries that would let me know that the last time user2 connected was May 4th at 18:19
and user3 has been MIA since May3rd 18:19?

My goal is to have an admin web page to let a co-worker see the non connecting offenders. I tried log watch, but that will not let me know that user3 is not logging in with out comparing - I have over 250 clients connecting...
Am I going about this the right way or is there an easier way? Your help is greatly appreciated!!

[Void Main]

The easiest way is to not use 3 fields for the date and time. Use one field with the DATETIME type. Querying dates becomes extremely easy at that point.

[byrdman]

my INSERT looks like:

$sql = 'INSERT into ncl';
$sql .= '(`month`,`day`,`year`,`time`,`client_venue`,`client_ip`)';
$sql .= "VALUES ('$cur_line[0]','$cur_line[1]','2010','$cur_line[2]','$cur_line[3]','$cur_line[4]')";

how would I get it so $cur_line 0, 1, and 2 all go into a single DATETIME field?

[Void Main]

This should do it:

Code: Select all

$sql  = "INSERT into ncl (dt,client_venue,client_ip)";
$sql .= " VALUES ('2010-{$cur_line[0]}-{$cur_line[1]} {$cur_line[2]}','$cur_line[3]','$cur_line[4]')";


Of course I wouldn't hard code the year like that.

[byrdman]

Thank you! I will try it. The year was hard-coded for testing reasons. I was having issues with pulling it from the array and got tired of guessing cause it was getting late, I think. Thanks again for your help...!!

[byrdman]

Perfect, it worked.
Now on to another plea for help.
I have the data correctly in the database, and even the year is not hardcoded.

If I did a select * from database it would give me:
May 2 18:19:17 user1 x.x.x.1
May 2 18:19:18 user2 x.x.x.2
May 2 18:19:21 user3 x.x.x.3
May 3 18:19:26 user1 x.x.x.1
May 3 18:19:27 user2 x.x.x.2
May 3 18:19:34 user3 x.x.x.3
May 4 18:19:39 user1 x.x.x.1
May 4 18:19:53 user2 x.x.x.2
May 5 18:22:50 user1 x.x.x.1

How would I do the query in mysql to say show me just the users that have not logged in, in the last 24 hours. It other words, it will produce a "Non-connecting list" A list that will show me that user 3 is not checking in.

[Void Main]

My initial thought is that would a little tricky and require a subquery if you want to do it in one SQL statement. You first have to get a list of total active users (for whatever time period you wish, e.g. the previous year) and then run a query over the last 24 hours and remove any users that appear in both lists. I don't have time to think more about it right now but I know a guy that sits right next to me could probably spit the query out in his sleep (he's not here at the moment). I'll try and get some time to work on it tonight if you haven't already figured it out.

[byrdman]

I am diving into subqueries and just realized how deep the water is!!

The following query is not correct but here is where I am headed:

Code: Select all

SELECT client_venue, client_ip, dt FROM `ncl` WHERE client_venue = (SELECT client_venue, client_ip, dt FROM `ncl` where dt BETWEEN ADDDATE(NOW(), INTERVAL -3 DAY) AND ADDDATE(NOW(), INTERVAL -1 DAY)) AND client_venue != (SELECT client_venue, client_ip, dt FROM `ncl` where dt NOT BETWEEN ADDDATE(NOW(), INTERVAL -1 DAY) AND NOW())


I get a mySQL error : Operand should contain 1 column(s)
and that is as far as I get. I can get results if I do the SELECT statements by themselves)

I am starting to believe that there should be more then one table, but then I have to figure out how to dump one text file into multiple tables and figure out how to do some sort of "if exists" on the data...

[byrdman]

I just thought of something that might be easier using one table:

Is there a way to check a table and if user1 exists, just update the datetime field, else insert new row with new user, datetime, ip.
That way it would be easy to query that table and see who hasn't updated their datetime.

Does that make sense?

Not to sure how I would do that in php, updating/inserting into mysql.

[Void Main]

Okay, just got a chance to look at this again and it turns out to be a pretty easy subselect:

Code: Select all

SELECT distinct client_venue FROM `ncl`
  WHERE dt > DATE_SUB(now(), INTERVAL 1 MONTH)
     AND client_venue NOT IN
       (SELECT client_venue FROM ncl
            WHERE dt > DATE_SUB(now(), INTERVAL 1 DAY))


In the example above I get a list of all users that have logged in within the last 30 days but have not logged in within the last 1 day. I use 30 days to create the master list of users you care about. You may want to go back farther than 1 month, or remove the date qualification altogether and get the complete list of distinct users that exist in the table:

Code: Select all

SELECT distinct client_venue FROM `ncl`
  WHERE client_venue NOT IN
       (SELECT client_venue FROM ncl
            WHERE dt > DATE_SUB(now(), INTERVAL 1 DAY))


It worked in my short little test but you might want to check it against your data to make sure it does what you want.