Notification

Icon
Error

Count repeated values and add amount to separate column - Count repeated values and add amount to separate column

Posted: Thursday, May 11, 2017 4:10:14 PM(UTC)
dnekrasov

dnekrasov

Member Posts: 2
0
Like
This issue has been solved! Click here to view the solution
Dear All,

I would like to create a report which displays the amount of laptop user was logged on as the last user. For example there will be plenty of machines where the Administrator account was used as the last logged on user.
I would like to display it in 3 columns - Username, Amount, AssetName(hostname of a laptop). Only the users who were logged-on on more that 1 laptop should be displayed.

The end result should look similar to this one:

UserPostedImage

Below is what I've already accomplished to create but unfortunately I can't get CTE to work in the right way.

Quote:
With LaptopsPerUser (UsernameL, Total) As (
Select Top 1000000 tblAssets.Username,
Count(tblAssets.Username) As total
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
Where tblAssets.AssetName Like '%-lt-%' And tblAssets.AssetName Like '%-lt-%'
And tsysAssetTypes.AssetTypename Like '%windows%'
Group By tblAssets.Username
),
OtherData (AssetName) As (
Select Top 1000000 tblAssets.AssetName, tblAssets.Username
From tblAssets
Where tblAssets.AssetName Like '%-lt-%' And tblAssets.AssetName Like '%-lt-%'
And tsysAssetTypes.AssetTypename Like '%windows%'
)
Select * from LaptopsPerUser
Union
Select * from OtherData


Any help will be highly appreciated!
dnekrasov
#1dnekrasov Member  
posted: 5/16/2017 10:50:16 AM(UTC)
Bump
Nick.VDB
#2Nick.VDB Member Administration  
posted: 5/19/2017 4:12:09 PM(UTC)
Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.


Code:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  UserCount.Total As UserLastLogonCount,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join (Select Top 1000000 tblAssets.Username,
    Count(tblAssets.Username) As Total
  From tblAssets
    Inner Join tblPortableBattery
      On tblAssets.AssetID = tblPortableBattery.AssetID
  Group By tblAssets.Username
  Having (tblAssets.Username != '') Or
    (tblAssets.Username Is Not Null)) As UserCount On tblAssets.Username =
    UserCount.Username
  Inner Join tblPortableBattery
    On tblAssets.AssetID = tblPortableBattery.AssetID
Where UserCount.Total > 1 And tblState.Statename = 'Active'

Active Discussions

Lansweeper Lansweeper and Sourcefire Modules
by  iboyd   Go to last post Go to first unread
Last post: Yesterday at 6:15:16 PM(UTC)
Lansweeper Issue with Upgrade to 6.0.100
by  albrigtp  
Go to last post Go to first unread
Last post: Yesterday at 6:07:53 PM(UTC)
Lansweeper Integration with FirePOWER 6.x as an identity source?
by  bubba198   Go to last post Go to first unread
Last post: Yesterday at 4:52:54 PM(UTC)
Lansweeper No report editor option
by  David.G  
Go to last post Go to first unread
Last post: Yesterday at 12:49:53 PM(UTC)
Lansweeper Lansweeper additional Scan Server
by  BenjaminK   Go to last post Go to first unread
Last post: Yesterday at 8:22:35 AM(UTC)
Lansweeper Customizable installer please!
by  AZHockeyNut  
Go to last post Go to first unread
Last post: 10/18/2017 7:48:03 PM(UTC)
Lansweeper Linux servers not listed in 'Server: All Servers' report
by  norbro   Go to last post Go to first unread
Last post: 10/18/2017 4:18:04 PM(UTC)
Lansweeper Report that show assets childs
by  EtienneHB  
Go to last post Go to first unread
Last post: 10/18/2017 4:12:49 PM(UTC)