Logon History

[nilesf]

Is there a way to view the logon history for both computers and users. I would like to create a report to see what computers a certian user has loggged on to as well as at what times that user has logged on to that computer over a period of time.


Thanks,
Niles

[nilesf]

Here is the script that you need to run for SQL 2000 or higer (not SQL express, it does not have the abiltiy to create "Views" and "Stored Procedures"). This does still require a logon script from your DC (NETLOGON) to run the lsclient. Active Scanning will not populate the views though you can run both.

After the users logs in and the lsclient successful runs go to the computer that you wish to view and select the user name link on right under thier photo you should see the last 100 computers that user has logged onto. Now go back to the Action Screen for that computer and select Configuration browser> User Info. Here you should see the last 100 users that logged onto that computer.

Remember that you will only see from the time that you implament the SQL script, how many computer they have logged into and how many users have logged into that computer. So relax and watch it grow. Very handy for SOX audits

Credit goes to Solholm for the script below.

I have modified the page that shows the user information, to include a list of the computers that the user has logged on to.
The list only shows the last login for each computer.

I made a new View called TFK_web30lastlogon_computers
SELECT TOP 100 PERCENT Username AS lastuser, MAX([Domain]) AS lastdomain, MAX(logontime) AS lastlogon, Computername
FROM dbo.tblCPlogoninfo
GROUP BY Username, Computername
ORDER BY Username, Computername

Then i made a new stored procedure called TFK_web30searchuser, which uses the new view
CREATE PROCEDURE dbo.TFK_web30searchuser(@name varchar(300))
AS SELECT dbo.tblADusers.Displayname, dbo.tblADusers.Title, dbo.tblADusers.Company, dbo.tblADusers.Department, dbo.tblADusers.Username,
dbo.tblADusers.Firstname, dbo.tblADusers.Lastname, dbo.tblADusers.Telephone, dbo.tblADusers.email, dbo.tblADusers.Userdomain,
dbo.tblADusers.Mobile, dbo.TFK_web30lastlogon_computers.lastlogon, dbo.TFK_web30lastlogon_computers.Computername
FROM dbo.tblADusers LEFT OUTER JOIN
dbo.TFK_web30lastlogon_computers ON dbo.tblADusers.Username = dbo.TFK_web30lastlogon_computers.lastuser AND
dbo.tblADusers.Userdomain = dbo.TFK_web30lastlogon_computers.lastdomain
WHERE (dbo.tblADusers.Username LIKE @name COLLATE SQL_Latin1_General_Cp1_CI_AI + '%') OR
(dbo.tblADusers.Firstname LIKE @name COLLATE SQL_Latin1_General_Cp1_CI_AI + '%') OR
(dbo.tblADusers.Lastname LIKE @name COLLATE SQL_Latin1_General_Cp1_CI_AI + '%') OR
(dbo.tblADusers.Displayname LIKE @name COLLATE SQL_Latin1_General_Cp1_CI_AI + '%') OR
(dbo.tblADusers.Displayname LIKE '%' + ' ' + @name COLLATE SQL_Latin1_General_Cp1_CI_AI + '%') OR
(dbo.tblADusers.Department LIKE @name COLLATE SQL_Latin1_General_Cp1_CI_AI + '%')
ORDER BY dbo.TFK_web30lastlogon_computers.Lastlogon desc
GO

Then last a modified the ASPX file it-userdetail.aspx, by adding the computerlisting from the APSX file it-searchuser.aspx

<%@ Page Language="VB" ContentType="text/html" %>
<%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls" Assembly="DreamweaverCtrls,version=1.0.0.0,publicKeyToken=836f606ede05d46a,culture=neutral" %>

<!-- Connection info for Brukerinfo -->

<MM:DataSet
runat="Server"
id="dscomputers"
IsStoredProcedure="true"
CreateDataSet="true"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_Lansweeper") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_Lansweeper") %>'
CommandText="dbo.web30userdetails"
Debug="true"
><Parameters>
<Parameter Name="@RETURN_VALUE" Type="Int" Direction="ReturnValue" />
<Parameter Name="@username" Value='<%# request("username") %>' Type="VarChar" Direction="Input" />
<Parameter Name="@userdomain" Value='<%# request("userdomain") %>' Type="VarChar" Direction="Input" />
</Parameters></MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />
<MM:PageBind runat="server" PostBackBind="true" />

<!-- Connectioninfo for Computerliste -->

<MM:DataSet
runat="Server"
id="CIcomputers"
IsStoredProcedure="true"
CreateDataSet="true"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_Lansweeper") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_Lansweeper") %>'
CommandText="dbo.TFK_web30searchuser"
Debug="true"
><Parameters>
<Parameter Name="@RETURN_VALUE" Type="Int" Direction="ReturnValue" />
<Parameter Name="@name" Value='<%# request("username") %>' Type="VarChar" Direction="Input" />
</Parameters></MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />


<link href="css/ls.css" rel="stylesheet" type="text/css" />

<table width="100%" border="0" cellpadding="0" cellspacing="1">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td><img src="images/users.png" width="48" height="40" hspace="4" /></td>
<td>
<table border="0" cellspacing="2" cellpadding="0">
<tr>
<td class="lijntje"><font size="4"><strong><%# dscomputers.FieldValue("Displayname", Container) %></strong></font></td>
</tr>
<tr>
<td><span class="bigt" >User details </span></td>
</tr>
</table>
</td>
</tr>
</table>
</td>

<td>
<!-- Blank felt-->
</td>
</tr>

<tr>
<td>
<table width="100%" border="0" cellspacing="4" cellpadding="0">
<tr>
<td valign="top">
<table width="100%" border="0" cellpadding="0" cellspacing="2" class="box">
<tr>
<td valign="top" width="80%">
<table width="100%" border="0" cellpadding="3" cellspacing="0" bgcolor="#FFFFFF" class="borderbox">
<tr>
<td valign="top">
<table width="100%" border="0" cellpadding="3" cellspacing="0" bgcolor="#FFFFFF" class="borderbox">
<tr>
<td align="right" class="lijntje"><strong>Title:</strong>&nbsp;</td>
<td class="lijntje"><%# dscomputers.FieldValue("title", Container) %>&nbsp;</td>
</tr>
<tr class="ne">
<td align="right" class="lijntje"><strong>Department:</strong>&nbsp;</td>
<td class="lijntje"><%# dscomputers.FieldValue("department", Container) %>&nbsp;</td>
</tr>
<tr >
<td align="right" class="lijntje"><strong>Office:</strong>&nbsp;</td>
<td class="lijntje"><%# dscomputers.FieldValue("Office", Container) %>&nbsp;</td>
</tr>
<tr class="ne">
<td align="right" class="lijntje"><strong>Upn:</strong>&nbsp;</td>
<td class="lijntje"><%# dscomputers.FieldValue("UPN", Container) %>&nbsp;</td>
</tr>
<tr >
<td align="right" class="lijntje"><strong>Username:</strong>&nbsp;</td>
<td class="lijntje"><%# dscomputers.FieldValue("Username", Container) %>&nbsp;</td>
</tr>
<tr class="ne">
<td align="right" class="lijntje"><strong>Domain:</strong>&nbsp;</td>
<td class="lijntje"><%# dscomputers.FieldValue("Userdomain", Container) %>&nbsp;</td>
</tr>
<tr >
<td align="right" class="lijntje"><strong>Last computer:</strong>&nbsp;</td>
<td class="lijntje"><a href="default.aspx?item=compdetail&amp;comp=<%# dscomputers.FieldValue("Computername", Container) %>"><%# dscomputers.FieldValue("Computername", Container) %></a>&nbsp;</td>
</tr>
<tr class="ne">
<td align="right" class="lijntje"><strong>Last logon:</strong>&nbsp;</td>
<td class="lijntje"><%# dscomputers.FieldValue("lastlogon", Container) %>&nbsp;</td>
</tr>
<tr >
<td align="right" class="lijntje"><strong>E-mail:</strong>&nbsp;</td>
<td class="lijntje"><a href="mailto:<%# dscomputers.FieldValue("email", Container) %>"><%# dscomputers.FieldValue("email", Container) %></a>&nbsp;</td>
</tr>
<tr class="ne">
<td align="right" class="lijntje"><strong>Telephone:</strong>&nbsp;</td>
<td class="lijntje"><a href="callto:<%# dscomputers.FieldValue("Telephone", Container) %>"><%# dscomputers.FieldValue("Telephone", Container) %></a>&nbsp;</td>
</tr>
<tr >
<td align="right" class="lijntje"><strong>Mobile:</strong>&nbsp;</td>
<td class="lijntje"><a href="callto:<%# dscomputers.FieldValue("Mobile", Container) %>"><%# dscomputers.FieldValue("Mobile", Container) %></a>&nbsp;</td>
</tr>
<tr class="ne">
<td align="right" class="lijntje"><strong>Fax:</strong>&nbsp;</td>
<td class="lijntje"><%# dscomputers.FieldValue("Fax", Container) %>&nbsp;</td>
</tr>
<tr >
<td align="right" valign="top" class="lijntje"><strong>Company:</strong>&nbsp;</td>
<td class="lijntje"><%# dscomputers.FieldValue("company", Container) %>&nbsp;<br />
<%# dscomputers.FieldValue("street", Container) %>&nbsp;<br />
<%# dscomputers.FieldValue("zip", Container) %>&nbsp;<%# dscomputers.FieldValue("city", Container) %><br />
<%# dscomputers.FieldValue("country", Container) %></td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td valign="top"><img src="smallthumb.aspx?user=<%# dscomputers.FieldValue("Username", Container) %>&amp;domain=<%# dscomputers.FieldValue("Userdomain", Container) %>&amp;size=200" class="picbox" />
</td>
</tr>
</table>
</td>

</tr>
</table>
</td>




</tr>

<tr>
<td>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td><img src="images/users.png" width="48" height="40" hspace="4" />
</td>
<td>
<table border="0" cellspacing="2" cellpadding="0">
<tr>
<td class="lijntje"><span class="bigt">Results for search &quot;<%=request("username")%>&quot;</span>
</td>
</tr>
<tr>
<td><%= CIcomputers.RecordCount %> items found
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>

<td>
<!-- blank felt -->
<td>
</tr>

<tr>
<td><%if CIcomputers.recordcount <> 0 then%>
<table width="100%" border="0" cellpadding="2" cellspacing="0">
<tr>
<td valign="top">
<table width="100%" border="0" cellpadding="0" cellspacing="2" class="box">
<tr>
<td valign="top">
<table width="100%" border="0" cellpadding="3" cellspacing="0" bgcolor="#FFFFFF" class="borderbox">
<tr>
<td valign="top">
<table width="100%" border="0" cellpadding="3" cellspacing="0" bgcolor="#FFFFFF" class="borderbox">
<tr>
<td valign="middle" class="tblcell">&nbsp;</td>
<td valign="middle" class="tblcell">Display name&nbsp;</td>
<td valign="middle" class="tblcell">Department&nbsp;</td>
<td valign="middle" class="tblcell">Title&nbsp;</td>
<td valign="middle" class="tblcell">Computer&nbsp;</td>
<td valign="middle" class="tblcell">Logon Time&nbsp;</td>
</tr>
<%dim tal as integer
dim CIrow as system.data.datarow
tal = 1%>
<% For Each CIrow In CIcomputers.DefaultView.Table.Rows%>
<tr <%if (tal mod 2) = 0 then%>class="ne"<%end if%>>
<td height="30" align="center" valign="middle" class="lijntje"><a href="default.aspx?item=userdetail&amp;username=<%=CIrow("username") %>&amp;userdomain=<%=CIrow("userdomain")%>"><img src="smallthumb.aspx?user=<%=CIrow("username") %>&amp;domain=<%=CIrow("userdomain") %>&amp;size=26" border="0" align="baseline" class="pic" /></a></td>
<td class="lijntje"><a href="default.aspx?item=userdetail&amp;username=<%=CIrow("username") %>&amp;userdomain=<%=CIrow("userdomain")%>"><%=CIrow("displayname") %></a>&nbsp;</td>
<td class="lef"><%=CIrow("department") %>&nbsp;</td>
<td class="lef"><%=CIrow("title") %>&nbsp;</td>
<td class="lef"><a href="default.aspx?item=compdetail&amp;comp=<%=CIrow("Computername") %>"><%=CIrow("computername") %></a>&nbsp;</td>
<td align="right" class="lef"><%=CIrow("lastlogon") %>&nbsp;</td>
</tr><%tal = tal + 1%>
<%next%>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
<%end if%>
</td>

<td>
<!-- blank felt -->
</td>
</tr>

</table>


Now when i search for a user and click to get the user details displayed i also get to se all the computers where the user has login.

[tdewar1]

I implemented the ability to see the computers the user logged into, but I was curious about the second part of nilesf's original post.

" as well as at what times that user has logged on to that computer over a period of time."

Is there any way to add that?

[Hemoco]

tdewar@leakeandwatts.org wrote:
I implemented the ability to see the computers the user logged into, but I was curious about the second part of nilesf's original post.

" as well as at what times that user has logged on to that computer over a period of time."

Is there any way to add that?

You can export all the data from the logon table (will be a lot) to excel and with a nice pivot table or graph you can do all research you like.

[cmadding]

One thing I had to do was actually Execute the code he posted for the 2nd step. I created the first step with no problem, it just showed up in the SQL list. The 2nd set of code was pasted in the box after right clicking on the process and selecting add new. I pasted the code and it did not work. After some research I found that there is an Execute button at the top. I clicked that and I got a message that said it ran with no problems. It second file then showed up in the list. Then when I pasted the code in the webpage everything started working. This was my first time to use the SQL manager so I was a but scared, but it all worked out. I hope this helps.

[Hemoco]

see this thread http://forum.lansweeper.com/yaf_postst299_Update-User-report-to-show-all-computers-a-user-has-logged...