This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Update User report to show all computers a user has logged into

Solholm
Engaged Sweeper

‎02-21-2008 12:11 PM

Hi

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.
Labels:
Preview file
89 KB
0 Kudos
25 REPLIES 25
dwildey
Engaged Sweeper

‎08-04-2008 08:13 PM

Thanks for this tool - It's awsome!

On LanSweeper 2.0, you could view the computer and you could see everyone who logged into it instead of looking at the user to see which computers they logged in? Does anyone know how to do this in 3.x?

Thanks!
0 Kudos
NYNet
Engaged Sweeper III

‎07-18-2008 04:32 PM

I actually added this to the "Wish List Forum" no realizing someone came up with it already!! Works great! :d/
0 Kudos
nilesf
Engaged Sweeper

‎07-17-2008 06:07 PM

Hi Solholm,

In you screenshots for view and Stored Procedures I notice that you are using SQL 2000. SQL 2005 Express is diferent in that there does not seem to be a way to create a this view, but only to modify one already listed. Also Stored Procedures is not list any where in SQL 2005 Express.



Please advise,
Niles
0 Kudos
Hemoco
Lansweeper Alumni
In response to nilesf

‎07-17-2008 10:22 PM

nilesf wrote:
Hi Solholm,

In you screenshots for view and Stored Procedures I notice that you are using SQL 2000. SQL 2005 Express is diferent in that there does not seem to be a way to create a this view, but only to modify one already listed. Also Stored Procedures is not list any where in SQL 2005 Express.
Please advise,
Niles


If you open lsmanage and go to the sql script tab, you can just paste the sql code and execute it.
0 Kudos
baylox
Engaged Sweeper

‎07-02-2008 10:24 AM

A great addition - thank you!
0 Kudos
Hemoco
Lansweeper Alumni

‎06-18-2008 09:50 PM

use the word "AS" after create view TFK_web30lastlogon_computers
0 Kudos
jreimer1
Engaged Sweeper

‎06-18-2008 08:40 PM

Tried it like this

CREATE VIEW 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

and am getting

Msg 156, Level 15, State 1, Procedure TFK_web30lastlogon_computers, Line 2
Incorrect syntax near the keyword 'SELECT'.

Any help is greatly appreciated
0 Kudos
Alex_Viner
Engaged Sweeper

‎06-10-2008 01:03 AM

very nice, thanks!
0 Kudos
Optilan
Engaged Sweeper

‎05-14-2008 03:55 PM

That's cool,

i'm using it 🙂
Optilan Communication Systems www.optilan.com
0 Kudos
Solholm
Engaged Sweeper

‎05-08-2008 04:30 PM

The view and the stored procedure must be created in the database along side the other views and stored procedures that Lansweeper
is delivered with.

here you can see where you need to create these.
see the images
Preview file
16 KB
Preview file
18 KB
0 Kudos

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now