→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
googoo
Engaged Sweeper III
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?
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

7 REPLIES 7
David_G
Lansweeper Employee
Lansweeper Employee
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
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
Engaged Sweeper
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
Lansweeper Alumni
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
Engaged Sweeper III
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
Engaged Sweeper III
that did it, thanks for the help.
Hemoco
Lansweeper Alumni
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