→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
MR1371
Engaged Sweeper II
I'm trying to figure out a report that shows a certain amount of software installed (in my case 1) based on the IPaddress location.

So the report will show 3 PC's with a certain IP adress location that have 1 specific type of software installed that all the other PC's in that IP address location do not have.
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
The following report uses two subqueries. One counts the number of assets per location and the other one counts the number of software installations per location. The report lists software installations which are not present on all machines.

Select Top 1000000 tSoftwarePerLocation.IPLocation,
tSoftwarePerLocation.softwareName,
tSoftwarePerLocation.SoftwarePublisher,
tSoftwarePerLocation.Number
From (Select Count(tblAssets.AssetID) As numberAssets,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tsysIPLocations.IPLocation) tAssetsPerLocation
Inner Join (Select Count(tblSoftware.AssetID) As Number,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tsysIPLocations.IPLocation
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Group By tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tsysIPLocations.IPLocation) tSoftwarePerLocation
On tSoftwarePerLocation.IPLocation = tAssetsPerLocation.IPLocation
Where tAssetsPerLocation.numberAssets > tSoftwarePerLocation.Number
Order By tSoftwarePerLocation.IPLocation,
tSoftwarePerLocation.softwareName

View solution in original post

3 REPLIES 3
Daniel_B
Lansweeper Alumni
The following report uses two subqueries. One counts the number of assets per location and the other one counts the number of software installations per location. The report lists software installations which are not present on all machines.

Select Top 1000000 tSoftwarePerLocation.IPLocation,
tSoftwarePerLocation.softwareName,
tSoftwarePerLocation.SoftwarePublisher,
tSoftwarePerLocation.Number
From (Select Count(tblAssets.AssetID) As numberAssets,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tsysIPLocations.IPLocation) tAssetsPerLocation
Inner Join (Select Count(tblSoftware.AssetID) As Number,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tsysIPLocations.IPLocation
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Group By tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tsysIPLocations.IPLocation) tSoftwarePerLocation
On tSoftwarePerLocation.IPLocation = tAssetsPerLocation.IPLocation
Where tAssetsPerLocation.numberAssets > tSoftwarePerLocation.Number
Order By tSoftwarePerLocation.IPLocation,
tSoftwarePerLocation.softwareName
MR1371
Engaged Sweeper II
I explained this terribly, my apologies. I want to have a report that displays PC's that have a software installed on them that no other PC's have in that specific location. Sort of a crude method to see if some PC's have any suspect software installed on them.
Daniel_B
Lansweeper Alumni
IP locations are stored in tsysIPlocations. You can add IP locations to a report by following the instructions on this post. If you only would like to list certain software installations, you need to use a subquery to only list relevant entries from tblSoftware and tblSoftwareUni. Please find an example below.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tSoftware.softwareName,
tSoftware.softwareVersion
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Inner Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName In ('Lansweeper',
'Microsoft SQL Server 2012')) tSoftware On tSoftware.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1 And tsysIPLocations.IPLocation =
'Example IP location'