05-11-2023 12:00 PM - last edited on 04-02-2024 09:18 AM by Mercedes_O
Hello everyone, please help!
Is it possible to split the values in a column into different columns?
Выберите Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets.IPAddress,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblOIDData.Label As [OID Label],
tblO IDData.[Key] Как OID,
tblOIDData.Data As [Данные OID],
tblAssetCustom.Location,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblOIDData On tblAssets.AssetID
= tblOIDData.AssetID
Inner Join Левое внешнее соединение tblAssetCustom на tblAssets .AssetID = tblAssetCustom.AssetID
Левое внешнее соединение tsysOS On tsysOS.OScode = tblAssets.OScode
Левое соединение tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
So that the CPU and Memory values are displayed in separate columns
Solved! Go to Solution.
05-15-2023 11:28 AM
Select Distinct Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets.AssetName,
(Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'% Avg CPU usage (last 5 minutes)') As [% Avg CPU usage (last 5 minutes)],
(Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'Free flash memory (bytes)') As [Free flash memory (bytes)],
(Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'iOS version') As [iOS version],
(Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'Total flash memory (bytes)') As [Total flash memory (bytes)],
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan]
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblOIDData On tblAssets.AssetID = tblOIDData.AssetID
05-16-2023 05:08 AM
Report 2.0
Select Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets.AssetName,
eOIDData.[% Avg CPU usage (last 5 minutes)],
eOIDData.[Free flash memory (bytes)],
eOIDData.[iOS version],
eOIDData.[Total flash memory (bytes)],
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan]
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join (Select tblOIDData.AssetID,
Max(Cast(Case
When tblOIDData.Label = '% Avg CPU usage (last 5 minutes)' Then
tblOIDData.Data
Else Null
End As nvarchar)) As [% Avg CPU usage (last 5 minutes)],
Max(Cast(Case
When tblOIDData.Label = 'Free flash memory (bytes)' Then tblOIDData.Data
Else Null
End As nvarchar)) As [Free flash memory (bytes)],
Max(Cast(Case
When tblOIDData.Label = 'iOS version' Then tblOIDData.Data
Else Null
End As nvarchar)) As [iOS version],
Max(Cast(Case
When tblOIDData.Label = 'Total flash memory (bytes)' Then
tblOIDData.Data
Else Null
End As nvarchar)) As [Total flash memory (bytes)]
From tblOIDData
Group By tblOIDData.AssetID) As eOIDData On tblAssets.AssetID =
eOIDData.AssetID
05-16-2023 05:08 AM
Report 2.0
Select Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets.AssetName,
eOIDData.[% Avg CPU usage (last 5 minutes)],
eOIDData.[Free flash memory (bytes)],
eOIDData.[iOS version],
eOIDData.[Total flash memory (bytes)],
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan]
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join (Select tblOIDData.AssetID,
Max(Cast(Case
When tblOIDData.Label = '% Avg CPU usage (last 5 minutes)' Then
tblOIDData.Data
Else Null
End As nvarchar)) As [% Avg CPU usage (last 5 minutes)],
Max(Cast(Case
When tblOIDData.Label = 'Free flash memory (bytes)' Then tblOIDData.Data
Else Null
End As nvarchar)) As [Free flash memory (bytes)],
Max(Cast(Case
When tblOIDData.Label = 'iOS version' Then tblOIDData.Data
Else Null
End As nvarchar)) As [iOS version],
Max(Cast(Case
When tblOIDData.Label = 'Total flash memory (bytes)' Then
tblOIDData.Data
Else Null
End As nvarchar)) As [Total flash memory (bytes)]
From tblOIDData
Group By tblOIDData.AssetID) As eOIDData On tblAssets.AssetID =
eOIDData.AssetID
05-15-2023 12:58 PM
Fix for duplicate data - return only Maximum
Select Distinct Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets.AssetName,
(Select Max(Cast(tblOIDData.Data As nvarchar)) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'% Avg CPU usage (last 5 minutes)') As [% Avg CPU usage (last 5 minutes)],
(Select Max(Cast(tblOIDData.Data As nvarchar)) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'Free flash memory (bytes)') As [Free flash memory (bytes)],
(Select Max(Cast(tblOIDData.Data As nvarchar)) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'iOS version') As [iOS version],
(Select Max(Cast(tblOIDData.Data As nvarchar)) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'Total flash memory (bytes)') As [Total flash memory (bytes)],
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan]
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblOIDData On tblAssets.AssetID = tblOIDData.AssetID
05-15-2023 11:28 AM
Select Distinct Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets.AssetName,
(Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'% Avg CPU usage (last 5 minutes)') As [% Avg CPU usage (last 5 minutes)],
(Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'Free flash memory (bytes)') As [Free flash memory (bytes)],
(Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'iOS version') As [iOS version],
(Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
'Total flash memory (bytes)') As [Total flash memory (bytes)],
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan]
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblOIDData On tblAssets.AssetID = tblOIDData.AssetID
05-15-2023 11:36 AM
thank you, very helpful!🤗
05-15-2023 06:14 AM
Could you show what you want?
*Screenshot of excel table, for example.
05-15-2023 06:39 AM
source report
what kind of report is needed in the end
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now