Report by Location

[WildcatLeeds]

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

[kcovingt]

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]

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]

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]

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 · Accepted solution]

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]

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]

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]

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.