
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-10-2012 01:26 PM
I've been tasked by our support teams to create a report showing the number of computers / laptops which are in each location. We've populated the IP Locations with the IP ranges and Locations names in the Lansweeper Configuration. I'm definitely not a query expert, actually hardly even a novice but I have modified queries before successfully.
I thank anyone for their help and expertise in this.
Yours,
Michael
LTHT
Solved! Go to Solution.
- Labels:
-
Report Center
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-12-2013 09:28 PM
SELECT TOP 1000000
tsysAssetTypes.AssetTypename,
tsysIPLocations.IPLocation,
Count(tblAssets.AssetID) As [Count]
FROM
tsysIPLocations
INNER JOIN tblAssets ON tblAssets.IPNumeric >= tsysIPLocations.StartIP AND tblAssets.IPNumeric <= tsysIPLocations.EndIP
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE
tblAssetCustom.State = 1
GROUP BY
tsysAssetTypes.AssetTypename,
tsysIPLocations.IPLocation
If you want to limit the asset types reported, just add a condition to the WHERE clause, e.g.
WHERE
tblAssetCustom.State = 1
AND tsysAssetTypes.AssetTypename IN ('Windows','Linux','Macintosh')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-18-2013 12:03 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-13-2013 01:33 AM
If you're not sure, try this:
SELECT DISTINCT
tsysAssetTypes.AssetTypename,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Count(tblAssets.AssetID) As [Count]
FROM
tsysIPLocations
INNER JOIN tblAssets ON tblAssets.IPNumeric >= tsysIPLocations.StartIP AND tblAssets.IPNumeric <= tsysIPLocations.EndIP
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblComputerSystem ON tblComputerSystem.AssetID = tblAssets.AssetID
WHERE
tblAssetCustom.State = 1
AND tblComputerSystem.Domainrole <= 1
GROUP BY
tsysAssetTypes.AssetTypename,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model
I have a couple of users with VMWare Workstation, so there are a few entries where the Manufacturer/Model fields reflect some virtual machines that have been noticed by LANSweeper. I'm not sure how VirtualBox, VirtualPC, Parallels and any other VMs are distinguished, but there's probably something you can use in either the Manufacturer or Model field.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-13-2013 01:20 AM
SELECT TOP 1000000
tsysAssetTypes.AssetTypename,
tsysIPLocations.IPLocation,
Count(tblAssets.AssetID) As [Count]
FROM
tsysIPLocations
INNER JOIN tblAssets ON tblAssets.IPNumeric >= tsysIPLocations.StartIP AND tblAssets.IPNumeric <= tsysIPLocations.EndIP
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblComputerSystem ON tblComputerSystem.AssetID = tblAssets.AssetID
WHERE
tblAssetCustom.State = 1
AND tblComputerSystem.Domainrole <= 1
GROUP BY
tsysAssetTypes.AssetTypename,
tsysIPLocations.IPLocation
The domain roles are defined in tblDomainRoles.
- -
0: Stand-alone workstation
1: Member workstation
- -
2: Stand-alone server
3: Member server
4: Backup domain controller
5: Primary domain controller

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-13-2013 12:33 AM
Would it be possible to have it filter out servers and only show pcs?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-12-2013 09:28 PM
SELECT TOP 1000000
tsysAssetTypes.AssetTypename,
tsysIPLocations.IPLocation,
Count(tblAssets.AssetID) As [Count]
FROM
tsysIPLocations
INNER JOIN tblAssets ON tblAssets.IPNumeric >= tsysIPLocations.StartIP AND tblAssets.IPNumeric <= tsysIPLocations.EndIP
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE
tblAssetCustom.State = 1
GROUP BY
tsysAssetTypes.AssetTypename,
tsysIPLocations.IPLocation
If you want to limit the asset types reported, just add a condition to the WHERE clause, e.g.
WHERE
tblAssetCustom.State = 1
AND tsysAssetTypes.AssetTypename IN ('Windows','Linux','Macintosh')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-12-2013 06:22 PM
Invalid object name 'web40AllIPLocations'
Would it be possible to get this report code updated so it will work correctly in Lansweeper 5.X?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-10-2012 05:48 PM
BTW, absolutely excellent product. It's been a godsend in the Trust for getting things straightened out.
All the best,
Michael

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-10-2012 03:15 PM
Select web40AllIPLocations.IPLocation, Count(web40AllIPLocations.Computername)
As [Computer Count]
From web40AllIPLocations Inner Join
web40ActiveComputers On web40ActiveComputers.Computername =
web40AllIPLocations.Computername
Group By web40AllIPLocations.IPLocation
Having web40AllIPLocations.IPLocation <> ''
Order By Count(web40AllIPLocations.Computername)
To use the specified report, do the following:
• Open the Lansweeper configuration console and select Reports & Alerts\Report Builder. Hit the “New” button.
• Copy the SQL code provided and paste it at the bottom of the newly created report, replacing the default SQL code.
• Click somewhere near tblComputers so the new code applies.
• Give the report a “View name” and a “Report name” and hit the “Save” button.
• Double-click on the report in the report list to see its results and export options.
