Device Last Logon Report

[j0ngreen]

Hi

I am currently trying to get a report which lists each asset (in this case, laptop), the users that have logged onto it with dates and also IP addresses they have connected from but really struggling

When I say users that have logged on, I need it in the format the same as when you click on "config" on an asset, then "user info" and the "last logon" to list hsitorical access, not just the last user of the device

Can it be done?

J

[Andy_Sismey]

This should give you a starting point, this lists each asset and the Users Last logged on :

Select Top 1000000 Case
    When Row_Number() Over (Partition By tblassets.AssetName Order By
      web50repwinuserlogonlast.Username) = 1 Then tblassets.AssetName
    Else Null
  End As 'Asset Name',
  web50repwinuserlogonlast.Username,
  tblassets.AssetID,
  tsysassettypes.AssetTypeIcon10 As icon,
  web50repwinuserlogonlast.UserLastLogon
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join lansweeperdb.dbo.web50repwinuserlogonlast On tblassets.AssetID =
      web50repwinuserlogonlast.AssetID
Where tblassetcustom.State = 1

[j0ngreen]

Andy.S wrote:
This should give you a starting point, this lists each asset and the Users Last logged on :

Select Top 1000000 Case
    When Row_Number() Over (Partition By tblassets.AssetName Order By
      web50repwinuserlogonlast.Username) = 1 Then tblassets.AssetName
    Else Null
  End As 'Asset Name',
  web50repwinuserlogonlast.Username,
  tblassets.AssetID,
  tsysassettypes.AssetTypeIcon10 As icon,
  web50repwinuserlogonlast.UserLastLogon
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join lansweeperdb.dbo.web50repwinuserlogonlast On tblassets.AssetID =
      web50repwinuserlogonlast.AssetID
Where tblassetcustom.State = 1

Hi Andy

Yes this is about where I was, the problem is I need to determine a list of logins per asset, so I guess we need a loop in the code to extract the further login data per asset. Its in the system as I can get it singularily, just cannot get all laptops and I have about 200 so it going to take a long time

EG.

PHC03408, Jgreen, 05/04/2022 12:46:30, 10.10.10.1
PHC03408, Jgreen, 06/04/2022 13:45:01, 10.20.10.1
PHC03408, FBlogs, 08/04/2022 23:50:01, 10.20.20.1


Best Regards

Jonathan

[Andy_Sismey]

Hi Jonathan, I must be misunderstanding, so that query should do as you ask, with "Asset Name" "User Name" "Last Login", formatted as below, with each asset and the Last Users logged on?

PC12345 User1 , 1/4/2022 12:00:00
__________User2 , 2/4/2022 12:00:00
__________User3 , 3/4/2022 12:00:00
__________User4 , 4/4/2022 12:00:00
PC12346 User7 , 1/4/2022 12:00:00
__________User8 , 1/4/2022 12:00:00
PC12347 User9 , 1/4/2022 12:00:00
LT12386 User , 1/4/2022 12:00:00
__________User1 , 1/4/2022 12:00:00

[j0ngreen]

Andy.S wrote:
Hi Jonathan, I must be misunderstanding, so that query should do as you ask, with "Asset Name" "User Name" "Last Login", formatted as below, with each asset and the Last Users logged on?

PC12345 User1 , 1/4/2022 12:00:00
__________User2 , 2/4/2022 12:00:00
__________User3 , 3/4/2022 12:00:00
__________User4 , 4/4/2022 12:00:00
PC12346 User7 , 1/4/2022 12:00:00
__________User8 , 1/4/2022 12:00:00
PC12347 User9 , 1/4/2022 12:00:00
LT12386 User , 1/4/2022 12:00:00
__________User1 , 1/4/2022 12:00:00

Hi Andy

Yes the above is what I am looking for but the code reports the below;

BEAFP01 JGadmin 05/04/2022 12:32:45 Virtual Machine 10.22.60.22
BEAKB02 Key 05/04/2022 12:33:02 Virtual Machine 10.22.60.27
CWPONY01 Key1 05/04/2022 12:36:07 Virtual Machine 10.22.40.24
DV00031 train 04/04/2022 12:32:37 HP ProBook 640 G1 10.22.6.16


Regards

J

[Andy_Sismey]

So if you remove the Indent Code, does it work for you now, I think that may be causing the issue ?

Select Top 1000000 tblassets.AssetName,
  web50repwinuserlogonlast.Username,
  tblassets.AssetID,
  tsysassettypes.AssetTypeIcon10 As icon,
  web50repwinuserlogonlast.UserLastLogon,
  tblassets.IPAddress
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join lansweeperdb.dbo.web50repwinuserlogonlast On tblassets.AssetID =
      web50repwinuserlogonlast.AssetID
Where tblassetcustom.State = 1

[j0ngreen]

Andy.S wrote:
So if you remove the Indent Code, does it work for you now, I think that may be causing the issue ?

Select Top 1000000 tblassets.AssetName,
  web50repwinuserlogonlast.Username,
  tblassets.AssetID,
  tsysassettypes.AssetTypeIcon10 As icon,
  web50repwinuserlogonlast.UserLastLogon,
  tblassets.IPAddress
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join lansweeperdb.dbo.web50repwinuserlogonlast On tblassets.AssetID =
      web50repwinuserlogonlast.AssetID
Where tblassetcustom.State = 1

Andy

I am so sorry, I have misread the report, I missed the dashes expecting the machine names, we are nearly there with the first code, what I am getting is what your details below;

PC12345 User1 , 1/4/2022 12:00:00
__________User2 , 2/4/2022 12:00:00
__________User3 , 3/4/2022 12:00:00
__________User4 , 4/4/2022 12:00:00

But what I actually need is not just the last users login but everyones logins eg;

PC12345 User1 , 1/4/2022 12:00:00
__________User1 , 1/4/2022 13:00:00
__________User1 , 1/4/2022 14:00:00
__________User2 , 2/4/2022 12:00:00
__________User3 , 3/4/2022 12:00:00
__________User4 , 4/4/2022 12:00:00


Best Regards

J

[Andy_Sismey]

So when I run the report the latest one , and filter down to one VM where multiple User login, its exactly the same list as I see in the Config --> Last Logged On around 30 Users as the report ?

[j0ngreen]

Andy.S wrote:
So when I run the report the latest one , and filter down to one VM where multiple User login, its exactly the same list as I see in the Config --> Last Logged On around 30 Users as the report ?

Hi

I just get one line item per user, and I know that they have logged in multiple times

Lansweeper, Asset Config List
rhutchinson SCG-HOTELS Hutchinson, Rhys 10.22.80.115 05/04/2022 17:08:00
rhutchinson SCG-HOTELS Hutchinson, Rhys 10.22.80.115 04/04/2022 16:46:32
rhutchinson SCG-HOTELS Hutchinson, Rhys 10.22.80.115 04/04/2022 16:45:03
TCReception SCG-HOTELS Tortworth, Reception 10.22.80.115 04/04/2022 12:27:18
SGedik SCG-HOTELS Gedik, Somer 10.22.80.115 04/04/2022 12:27:18
RHutchinson SCG-HOTELS Hutchinson, Rhys 10.22.80.115 04/04/2022 12:27:18
rhutchinson SCG-HOTELS Hutchinson, Rhys 10.22.80.115 03/04/2022 16:46:24
CKaradag SCG-HOTELS Karadag, Charlotte 10.22.80.115 02/04/2022 16:42:42
CKaradag SCG-HOTELS Karadag, Charlotte 10.22.80.115 01/04/2022 16:20:46
rhutchinson SCG-HOTELS Hutchinson, Rhys 10.22.80.115 01/04/2022 12:27:03
LBrain SCG-HOTELS 10.22.80.115 31/03/2022 16:20:30
tcreception SCG-HOTELS Tortworth, Reception 10.22.80.115 31/03/2022 12:27:36
rhutchinson SCG-HOTELS Hutchinson, Rhys 10.22.80.115 30/03/2022 15:46:50
NRobertson SCG-HOTELS Robertson, Nicola 10.22.80.115 30/03/2022 14:59:27



Code generates
DV00202 DTighe 21/03/2022 15:32:40 10.22.80.115
DV00202 gsinker 04/02/2022 17:02:39 10.22.80.115
DV00202 LBrain 31/03/2022 16:20:30 10.22.80.115
DV00202 NRobertson 30/03/2022 14:59:27 10.22.80.115
DV00202 rhutchinson 05/04/2022 17:08:00 10.22.80.115
DV00202 SGedik 04/04/2022 12:27:18 10.22.80.115
DV00202 zsevsek 08/03/2022 00:09:52 10.22.80.115

Best Regards

Jonathan

[Andy_Sismey · Accepted solution]

Sorry I get what you want now,sorry, I believe you need the table tblCPlogoninfo, you may need to add additional filters as there will be lots of results , but this will return all assets and all Users logged on with each logon date

Select Top 1000000 tblassets.AssetName,
  tblassets.AssetID,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblCPlogoninfo.Username,
  tblCPlogoninfo.logontime,
  tblassets.IPAddress
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join lansweeperdb.dbo.tblCPlogoninfo On tblassets.AssetID =
      tblCPlogoninfo.AssetID
Where tblassetcustom.State = 1