cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Davevaper
Engaged Sweeper
Still very new to LanSweeper, I was just given access not long ago.

I am trying to get guidance on creating a report for 30 predefined hostnames I am monitoring (Daily) to show their Windows 10 O/S version (20H2,1903,1909....) 30 is not all that hard to search, but when i have 5000+ hosts it can get time consuming.

Hostname...
Last user..
OS...
Build..
Version..

I looked for a few samples reports and searched, but hitting a wall
1 ACCEPTED SOLUTION
Andy_Sismey
Champion Sweeper III
Hi,

So one option, if you don't want to add a list of Asset names to a report, is to drop a tag file on all the assets you want to monitor, create a new file scan for the file in "scanning" , this report will only display assets with the tag file 'c:\tagfile.txt' .

Select Top 1000000 tblassets.AssetName,
tblassets.IPAddress,
tsysOS.OSname,
tblassets.Version,
tblassets.AssetID
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tblFileVersions On tblassets.AssetID = tblFileVersions.AssetID
Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
Where tblFileVersions.FilePathfull = 'c:\tagfile.txt' And
tblFileVersions.Found = 'true' And tblassetcustom.State = 1
Group By tblassets.AssetName,
tblassets.IPAddress,
tsysOS.OSname,
tblassets.Version,
tblassets.AssetID,
tblFileVersions.FilePathfull,
tblFileVersions.Found


You could also setup a lansweeper deployment to install the file on all the assets you want , just create a command style install with
echo 'Scan File'  > "c:\tagfile.txt"


Option2
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.Username,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tsysOS.OSname,
tblassets.Version,
tblAssetComments.Comment,
tblADComputers.OU
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join lansweeperdb.dbo.tsysOS On tblassets.OScode = tsysOS.OScode
Left Join lansweeperdb.dbo.tblAssetComments On tblassets.AssetID =
tblAssetComments.AssetID
Inner Join tblADComputers On tblassets.AssetID = tblADComputers.AssetID
Where (tblassets.AssetName = 'PC1' Or tblassets.AssetName = 'PC2' Or
tblassets.AssetName = 'PC3' Or tblassets.AssetName = 'PC4' Or
tblassets.AssetName = 'PC5' Or tblassets.AssetName = 'PC6' Or
tblassets.AssetName = 'PC7') And tblassetcustom.State = 1

View solution in original post

3 REPLIES 3
Davevaper
Engaged Sweeper
Awesome, Thanks Guy's

Option #2 was the way to go for my needs. This gave me a better understanding how it all works now.

Again Thanks!

Dave V
RCorbeil
Honored Sweeper II
I'm with Andy.S on option 2. A list of 30 machines is short enough that it should be simple to include in the WHERE clause on your query. The one suggestion I'd make, unless you intend to use wildcards, is to use IN instead of a list of OR'd comparisons. The net result is the same, but it's shorter and, in my opinion, a little easier to create and read.
Where
tblassets.AssetName IN ('PC1', 'PC2','PC3', 'PC4', 'PC5', 'PC6', 'PC7')
And tblassetcustom.State = 1
Andy_Sismey
Champion Sweeper III
Hi,

So one option, if you don't want to add a list of Asset names to a report, is to drop a tag file on all the assets you want to monitor, create a new file scan for the file in "scanning" , this report will only display assets with the tag file 'c:\tagfile.txt' .

Select Top 1000000 tblassets.AssetName,
tblassets.IPAddress,
tsysOS.OSname,
tblassets.Version,
tblassets.AssetID
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tblFileVersions On tblassets.AssetID = tblFileVersions.AssetID
Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
Where tblFileVersions.FilePathfull = 'c:\tagfile.txt' And
tblFileVersions.Found = 'true' And tblassetcustom.State = 1
Group By tblassets.AssetName,
tblassets.IPAddress,
tsysOS.OSname,
tblassets.Version,
tblassets.AssetID,
tblFileVersions.FilePathfull,
tblFileVersions.Found


You could also setup a lansweeper deployment to install the file on all the assets you want , just create a command style install with
echo 'Scan File'  > "c:\tagfile.txt"


Option2
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.Username,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tsysOS.OSname,
tblassets.Version,
tblAssetComments.Comment,
tblADComputers.OU
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join lansweeperdb.dbo.tsysOS On tblassets.OScode = tsysOS.OScode
Left Join lansweeperdb.dbo.tblAssetComments On tblassets.AssetID =
tblAssetComments.AssetID
Inner Join tblADComputers On tblassets.AssetID = tblADComputers.AssetID
Where (tblassets.AssetName = 'PC1' Or tblassets.AssetName = 'PC2' Or
tblassets.AssetName = 'PC3' Or tblassets.AssetName = 'PC4' Or
tblassets.AssetName = 'PC5' Or tblassets.AssetName = 'PC6' Or
tblassets.AssetName = 'PC7') And tblassetcustom.State = 1