→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dalbright
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
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

View solution in original post

3 REPLIES 3
dalbright
Engaged Sweeper
I believe the "Distinct" was what was missing from my statement. This returns exactly the result I was looking for. Thanks!
Nick_VDB
Champion Sweeper III
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
Engaged Sweeper
Bumping to see if anyone can help out.