→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎08-24-2017 06:21 PM
‎08-30-2017 06:34 PM
Select Top 1000000 Totaux,
Nombres,
AssetID,
AssetName,
IPAddress,
Location,
Serialnumber,
Model,
Description,
From (Select tsysIPLocations.IPLocation As 'Totaux',
0 As 'Ordre',
Null As 'Nombres',
Null As 'AssetID',
'' As 'AssetName',
'' As 'IPAddress',
'' As 'Location',
'' As 'Serialnumber',
Null As 'Model',
Null As 'Description',
tsysIPLocations.LocationID As 'LocationID'
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
Where tblAssets.Assettype = -1 And tblAssets.AssetName Like '%ORD%' And
tblAssetCustom.State = 1
Group By tsysIPLocations.IPLocation,
tsysIPLocations.LocationID
Union All
Select '' As 'Totaux',
1 As 'Ordre',
Null As 'Nombres',
tblAssets.AssetID As 'AssetID',
tblAssets.AssetName As 'AssetName',
tblAssets.IPAddress As 'IPAddress',
tblAssetCustom.Location As 'Location',
tblAssetCustom.Serialnumber As 'Serialnumber',
tblAssetCustom.Model As 'Model',
tblAssets.Description As 'Description',
tsysIPLocations.LocationID As 'LocationID'
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
Where tblAssets.AssetName Like '%ORD%' And tblAssets.Assettype = -1 And
tblAssetCustom.State = 1
Union All
Select 'Sous-total' As 'Totaux',
2 As 'Ordre',
(Select Count(a.AssetName) From tblAssets a
Where tsysIPLocations.LocationID = a.LocationID And a.Assettype = -1 And
a.AssetName Like '%ORD%') As 'Nombres',
Null As 'AssetID',
'' As 'AssetName',
'' As 'IPAddress',
'' As 'Location',
'' As 'Serialnumber',
Null As 'Model',
Null As 'Description',
tsysIPLocations.LocationID As 'LocationID'
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
Where tblAssets.Assettype = -1 And tblAssets.AssetName Like '%ORD%' And
tblAssetCustom.State = 1
Group By tsysIPLocations.LocationID
Union All
Select 'Total' As 'Totaux',
3 As 'Ordre',
count(*) As 'Nombres',
Null As 'AssetID',
Null As 'AssetName',
Null As 'IPAddress',
Null As 'Location',
Null As 'Serialnumber',
Null As 'Model',
Null As 'Description',
999999999 As 'IPLocation'
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
Where tblAssets.Assettype = -1 And tblAssets.AssetName Like '%ORD%' And
tblAssetCustom.State = 1) As t
Order By LocationID,
Ordre,
Totaux
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now