Notification

Icon
Error

non-active computer report

Posted: Thursday, July 14, 2011 4:04:08 PM(UTC)
googoo

googoo

Member Original PosterPosts: 132
1
Like
This issue has been solved! Click here to view the solution
In our environment, computers are occasionally removed from the domain, placed in storage, then reused again at a later date. When this happens, the account in Lansweeper is marked as 'inactive'. However, when the pc is reused, it is likely renamed based on who is using the pc. My goal here is to clean up non-active computers in LS, since some may actually be in use under a different name. I'm no SQL-guy, so I'm hoping someone can provide a report query that will export all computers, their state (active OR non-active), and the service tag. Then I could sort by service tag to find the duplicates. Surely this is possible?
Lansweeper
#1Lansweeper Member Posts: 13,442  
posted: 7/18/2011 11:08:02 AM(UTC)
try this:

Quote:
Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, web40allcompstates.Statename,
web40allcompstates.Thestate, tblComputerSystemProduct.IdentifyingNumber
From tblComputers Inner Join
web40allcompstates On web40allcompstates.Computername =
tblComputers.Computername Inner Join
tblComputerSystemProduct On tblComputers.Computername =
tblComputerSystemProduct.Computername
googoo
#2googoo Member Original PosterPosts: 132  
posted: 7/18/2011 10:18:57 PM(UTC)
that did it, thanks for the help.
jengley
#3jengley Member Posts: 11  
posted: 7/31/2013 6:08:44 PM(UTC)
Our environment runs things almost identically to the original post in this thread for our domain, but it appears that this SQL query no longer functions in the newest iteration of Lansweeper. Is there something I need to change in order for it to function again?

Errors I am receiving:

Invalid object name 'tblComputers'. - I am assuming all instance of this need to be changed to tblADComputers?

Invalid object name 'web40allcompstates'. - I have no idea what the new version of this is.
Lansweeper
#4Lansweeper Member Posts: 13,442  
posted: 7/31/2013 6:22:49 PM(UTC)
jengley wrote:
Our environment runs things almost identically to the original post in this thread for our domain, but it appears that this SQL query no longer functions in the newest iteration of Lansweeper.

Use the report below for Lansweeper 5.0 instead. 4.2 tblComputers = 5.0 tblAssets.
Quote:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
tblAssetCustom.Serialnumber
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
tomscott2340
#5tomscott2340 Member Posts: 10  
posted: 9/28/2017 9:02:03 PM(UTC)
Howdy.. When I use the code in the latest I get more then just Windows devices.. Can I get a report that tells me active/in-active for just Windows devices?

Thank you!
David.G
#6David.G Member Administration Posts: 113  
posted: 10/2/2017 2:35:30 PM(UTC)
This can easily be done by adding the database table tsysAssetTypes, which is included in the default SQL query within Lansweeper, whenever you go to Reports\Create New Report. For your convenience, I have added a report that will provide you with Windows assets and some additional information including the asset state of the asset.

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,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.Serialnumber,
  tblState.Statename As State
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
Where tsysAssetTypes.AssetTypename Like 'Windows%' And tblAssetCustom.State = 1
Apaulcolypse
#7Apaulcolypse Member Posts: 23  
posted: 7/19/2019 5:16:21 PM(UTC)
I understand that Last Changed and Last Seen and what I'm trying to accomplish are all fairly different dates.

I would like to edit the report to say WHEN it went from Active to Non-Active but neither Last Seen or Last Changed show that. Last Seen would only show that if the computers were scanned daily.

Is there a way?


Originally Posted by: David.G Go to Quoted Post
This can easily be done by adding the database table tsysAssetTypes, which is included in the default SQL query within Lansweeper, whenever you go to Reports\Create New Report. For your convenience, I have added a report that will provide you with Windows assets and some additional information including the asset state of the asset.

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,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.Serialnumber,
  tblState.Statename As State
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
Where tsysAssetTypes.AssetTypename Like 'Windows%' And tblAssetCustom.State = 1


Active Discussions

Lansweeper Launch PowerShell remote PSSession
by  Ian   Go to last post Go to first unread
Last post: 8/9/2019 12:07:41 PM(UTC)
Lansweeper lspush smtp direct send
by  Danilo Ferrari  
Go to last post Go to first unread
Last post: 8/1/2019 1:39:26 PM(UTC)
Action Delete old user profiles
by  DaveDischord   Go to last post Go to first unread
Last post: 7/30/2019 6:18:28 PM(UTC)
Lansweeper Best way to delete multiple registries
by  Corcos  
Go to last post Go to first unread
Last post: 7/25/2019 8:18:23 PM(UTC)
Lansweeper Infopath installer help
by  Dave Ward   Go to last post Go to first unread
Last post: 7/23/2019 3:11:38 PM(UTC)
Lansweeper Top 10 Ticket Types Year To Date
by  LGuth  
Go to last post Go to first unread
Last post: 7/10/2019 8:37:48 PM(UTC)
Lansweeper Fonts
by  Spectrum   Go to last post Go to first unread
Last post: 6/25/2019 11:24:19 AM(UTC)
Action Powershell script for WOL on VLAN
by  psmail  
Go to last post Go to first unread
Last post: 5/30/2019 12:00:43 AM(UTC)