PDA

Click to See Complete Forum and Search --> : Selecting last login date/time from txt file

jrthor2
09-15-2003, 12:26 PM
I have a text file that stores when a person tries to login to my webadming site. It looks like this:


1 | mroscoe | 66.202.1.53 | 7/22/2003 7:05:47 PM | Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)
0 | mroscoe | 68.82.252.217 | 7/24/2003 9:14:12 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
1 | mroscoe | 68.82.252.217 | 7/24/2003 9:14:30 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
1 | debright | 172.167.71.212 | 7/29/2003 8:14:19 AM | Mozilla/4.0 (compatible; MSIE 6.0; Windows 98; Win 9x 4.90; .NET CLR 1.0.3705)
1 | debright | 204.28.140.7 | 7/29/2003 2:30:25 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Crazy Browser 1.0.5; .NET CLR 1.1.4322)
1 | mroscoe | 204.28.140.7 | 7/29/2003 2:46:44 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Crazy Browser 1.0.5; .NET CLR 1.1.4322)
1 | mroscoe | 204.28.140.7 | 7/29/2003 2:56:34 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Crazy Browser 1.0.5; .NET CLR 1.1.4322)
1 | debright | 204.28.140.7 | 7/29/2003 2:56:46 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Crazy Browser 1.0.5; .NET CLR 1.1.4322)
1 | mroscoe | 204.28.140.7 | 7/29/2003 3:24:10 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Crazy Browser 1.0.5; .NET CLR 1.1.4322)
1 | mroscoe | 204.28.140.7 | 7/29/2003 3:24:26 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Crazy Browser 1.0.5; .NET CLR 1.1.4322)
1 | jgray | 66.202.1.53 | 7/30/2003 10:51:26 AM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)
0 | mroscoe | 66.202.1.53 | 8/28/2003 10:10:11 AM | Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)
1 | mroscoe | 66.202.1.53 | 8/28/2003 10:10:22 AM | Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)
1 | jgray | 66.202.1.53 | 9/4/2003 10:15:43 AM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)
1 | jgray | 66.202.1.53 | 9/4/2003 10:27:45 AM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)
1 | mroscoe | 68.82.252.217 | 9/5/2003 3:04:13 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
1 | mroscoe | 68.82.252.217 | 9/5/2003 3:16:04 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
0 | JGRAY | 64.12.96.236 | 9/6/2003 9:26:44 PM | Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.1; Q312461; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
1 | jgray | 64.12.96.236 | 9/6/2003 9:27:19 PM | Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.1; Q312461; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
1 | jgray | 152.163.253.1 | 9/9/2003 7:51:35 AM | Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.1; Q312461; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
1 | jgray | 66.202.1.53 | 9/9/2003 12:19:52 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)
1 | jgray | 66.202.1.53 | 9/9/2003 12:45:19 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)
1 | mroscoe | 66.202.1.53 | 9/11/2003 11:40:57 AM | Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)
1 | mroscoe | 68.82.252.217 | 9/11/2003 12:30:56 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
1 | mroscoe | 68.82.252.217 | 9/11/2003 3:01:16 PM | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)


I have a page that shows me the counts of successful/failed/total logins. It shows the counts fine, but it is not showing the date of the last entry for the users. Example, for user jgray, it shows the last login date/time as 9/9/2003 7:51:35 AM. If you look above, that is not correct, it should be 9/9/2003 12:45:19 PM. Below is the code for my page:


<% @Language=VBScript %>
<%
'If the session variable is False or does not exsist then redirect the user to the login user page
If (Session("level")) <= 2 then

'Redirect to login user page
Session("from") = "http://" & Request.serverVariables("SERVER_NAME") & Request.serverVariables("PATH_INFO")
Response.Redirect"login.asp"
'Else
'name = Request.Querystring("name")
End If
'Response.Write ("Level " & Session("level"))
%>
<html>
<head>
<title>Login Attempts</title>
<!--#include virtual="/inc/head_include.asp"-->
</head>

<body <% Response.Write(bodytag) %>>
<%
Set oConn = Server.CreateObject("ADODB.COnnection")
oConn.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=D:\html\users\zluthorg\html\Admin\;" & _
"Extensions=asc,csv,tab,txt"

Set oRs = oConn.Execute("SELECT t0.Username, IIf(IsNull(t1.Failed),0,t1.Failed) as failed," & _
" IIF(IsNUll(t2.Successful), 0, t2.Successful) as successful, " & _
" (IIf(IsNull(t1.Failed), 0, t1.Failed) + " & _
" IIF(IsNUll(t2.Successful), 0, t2.Successful)) as TotalLogin, " & _
" Max(LoginDate) as LastLogin " & _
" FROM (login.txt as t0 LEFT JOIN " & _
" (SELECT Username, COUNT(*) as Failed FROM " & _
" login.txt WHERE ID = '0' " &_
" GROUP BY Username) as t1" & _
" ON t0.Username = t1.Username) LEFT JOIN " & _
" (SELECT Username, COUNT(*) as Successful FROM " & _
" login.txt WHERE ID = '1' " &_
" GROUP BY Username) as t2" & _
" ON t0.Username = t2.Username"& _
" GROUP BY t0.Username, t1.Failed, t2.Successful")

Set oRs2 = oConn.Execute("Select Count(*) as Failed from login.txt where ID = '0'")
tot_failed = oRs2("Failed")

Set oRs3 = oConn.Execute("Select Count(*) as Successful from login.txt where ID = '1'")
tot_successful = oRs3("Successful")
%>
<table width="100%" border="0" cellspacing="0" cellpadding="2">
<tr>
<td rowspan="4" valign="top">
<!--#include virtual="/inc/Menu.asp"-->
</td>
<td width="100%" valign="top">
<!--#include virtual="/inc/layout_top.asp"-->
<!--#include virtual="/inc/admin_top.asp"-->
</td>
</tr>
<tr>
<td width="100%" height="455" valign="top">
<table width="100%" border="1" cellpadding="5" cellspacing="0" bordercolor="330099" bgcolor="ffffff">
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="2">
<tr>
<td><div align="center"><font class="fontsize5"><u><strong>Login
Attempts</strong></u></font>
</div></td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<tr>
<td colspan="2">
<table width="100%" border="0" align="center" cellpadding="3" cellspacing="0">
<tr valign="bottom">
<td nowrap><b>Username</b></td>
<td align="center" nowrap><strong>Successful</strong>
</td>
<td align="center" nowrap><strong>Failed</strong>
</td>
<td align="center" nowrap><b>Total</b></td>
<td align="right" nowrap><b>Last
Login
Date/Time</b></td>
</tr>
<tr>
<td colspan="5">
<img src="/images/purple_bar_menu.gif" width="100%" height="1"></td>
</tr>
<%
Dim mycolor
mycolor = "#d7d7ff"
Do while Not oRs.EOF
If mycolor = "#ffffff" Then
mycolor = "#d7d7ff"
Else
mycolor = "#ffffff"
End If

username = oRs("Username")
lst_login_dte = oRs("LastLogin")
successful = oRs("Successful")
failed = oRs("Failed")
counts = oRs("TotalLogin")
%>
<tr bgcolor="<%= mycolor %>">
<td><%=username%></td>
<td align="center"><%=successful%></td>
<td align="center"><%=failed%></td>
<td align="center"><%=counts%></td>
<td align="right" nowrap><%=lst_login_dte & "&nbsp;" & times%></td>
</tr>
<%
oRs.MoveNext
Loop
oRs.Close
Set oRs = Nothing
oConn.Close
Set oConn = Nothing
%>
<tr>
<td colspan="5">
<img src="/images/purple_bar_menu.gif" width="100%" height="1"></td>
</tr>
<tr>
<td><strong>Totals:</strong></td>
<td align="center"><%=tot_successful%></td>
<td align="center"><%=tot_failed%></td>
<td align="center"><%=tot_successful + tot_failed%></td>
<td>&nbsp;</td>
</tr>
</table></td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<td valign="top" colspan="2">
<!--#include virtual="/inc/layout_bottom.htm"-->
</td>
</tr>
</table>

</body>
</html>


Can someone please help me out here?

Thanks
jrthor2
09-16-2003, 12:16 PM
Can anyone please help?????