Hardware/Software Poll

[dalbright]

The information I am seeking are the following fields:
Last known user
OS
IP address
IP location
Last seen
Last tried
Computer name
CPU

I have basically taken the built in report named "OS: Operating system overview", added in the "tsysIPLocation" table and checked the appropriate boxes to get most of the information I need. The last piece was the CPU information which I can pull from the built in report "Computer: Processor information".

What I can seem to do it find a data source or table that the "Computer: Processor information" report uses called "ProcCapacity". I tried adding in a table called "tblProcessor", but that seemed to duplicate some of my results in the report, which I'm guessing may be pinging for different cores in CPU's, but I'm not sure.

Here is the current working SQL that is missing CPU information about the PC's in the results.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysIPLocations.IPLocation,
tblOperatingsystem.Caption,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1

[dalbright]

I believe the "Distinct" was what was missing from my statement. This returns exactly the result I was looking for. Thanks!

[Nick_VDB · Accepted solution]

Your report is correct and using the tblProcessor table for the CPU is also correct. You may get multiple rows back due to having multiple processors on a single machine this will create multiple rows in the table for the same asset. If the information is identical you can use the 'Distinct' property in your query to only give back one of the rows. We added this to your report and also added the tblprocessor.

Select Distinct Top 1000000 tblAssets.Username,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysIPLocations.IPLocation,
  tblOperatingsystem.Caption As OS,
  tblProcessor.Caption As CPU
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tblAssetCustom.State = 1

[dalbright]

Bumping to see if anyone can help out.