cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
WildcatLeeds
Engaged Sweeper
Hi there

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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Try this out:
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')

View solution in original post

8 REPLIES 8
kcovingt
Engaged Sweeper III
You sir get a gold star for the day . Thank you very much for the reports and I was actually looking for just physical desktops so thanks for creating that second report as well.
RCorbeil
Honored Sweeper II
Be aware that if you have virtual machines in your database they will be incorporated into the count. If you're just looking for physical machines, you'll need to do a little more filtering.

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.
RCorbeil
Honored Sweeper II
Add one more table into the mix.
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
kcovingt
Engaged Sweeper III
Thank you very much for the updated code. That is definitely getting us on the right track. Looking through that we realized that desktops and servers are being lumped together in this count since they both are Windows based.

Would it be possible to have it filter out servers and only show pcs?
RCorbeil
Honored Sweeper II
Try this out:
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')
kcovingt
Engaged Sweeper III
I also need this report for a project I'm working on and it appears that the code for this report does not work in Lansweeper 5.X as it is getting the following error when I attempt to save it.

Invalid object name 'web40AllIPLocations'

Would it be possible to get this report code updated so it will work correctly in Lansweeper 5.X?
WildcatLeeds
Engaged Sweeper
Thanks much! That's exactly what he was asking for.

BTW, absolutely excellent product. It's been a godsend in the Trust for getting things straightened out.

All the best,
Michael
Hemoco
Lansweeper Alumni
Please try the report below. It lists counts per IP location of the number of (active) computers.

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.