non-active computer report

[googoo]

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?

[David_G]

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.

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

[paulruvalcaba]

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?

David.G wrote:
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.

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

[jengley]

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.

[Hemoco · Accepted solution]

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.

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]

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!

[googoo]

that did it, thanks for the help.

[Hemoco]

try this:

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