→ 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: 
AlexMZetec
Engaged Sweeper II
Hello!

I tried to merge 2 reports that works well but now I get 3-4 duplicates for each entries. The report return 1000 lines instead of 400.
Someone can see my problem???
HELP!!!!!!!!



Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
Case
When tblDiskPartition.Type = 'Installable File System' Then 'Legacy'
Else 'UEFI'
End As BiosType,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.DriveLetter,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Username
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskPartition On tblAssets.AssetID = tblDiskPartition.AssetID
Where tblEncryptableVolume.DriveLetter = 'C:'
Order By tblAssets.AssetName
1 ACCEPTED SOLUTION
KrisNelson
Champion Sweeper
This entry is your problem.

Case
When tblDiskPartition.Type = 'Installable File System' Then 'Legacy'
Else 'UEFI'
End As BiosType

It's checking each of the partitions for each asset. And since there are multiple partitions per drive your returning multiple entries.

I would try this and see if it's to your liking; and admittedly there's probably a better way to code this. But, it's only going to check the bootable partitions for the UEFI/Legacy type.


Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID As AssetID1,
tblAssets.AssetName,
Case
When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type =
'Installable File System' Then 'Legacy'
When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type <>
'Installable File System' Then 'UEFI'
End As BiosType,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.DriveLetter,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Username
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskPartition On tblAssets.AssetID = tblDiskPartition.AssetID
Where Case
When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type =
'Installable File System' Then 'Legacy'
When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type <>
'Installable File System' Then 'UEFI'
End Is Not Null And tblEncryptableVolume.DriveLetter = 'C:'
Order By tblAssets.AssetName


-Kris

View solution in original post

2 REPLIES 2
KrisNelson
Champion Sweeper
This entry is your problem.

Case
When tblDiskPartition.Type = 'Installable File System' Then 'Legacy'
Else 'UEFI'
End As BiosType

It's checking each of the partitions for each asset. And since there are multiple partitions per drive your returning multiple entries.

I would try this and see if it's to your liking; and admittedly there's probably a better way to code this. But, it's only going to check the bootable partitions for the UEFI/Legacy type.


Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID As AssetID1,
tblAssets.AssetName,
Case
When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type =
'Installable File System' Then 'Legacy'
When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type <>
'Installable File System' Then 'UEFI'
End As BiosType,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.DriveLetter,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Username
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskPartition On tblAssets.AssetID = tblDiskPartition.AssetID
Where Case
When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type =
'Installable File System' Then 'Legacy'
When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type <>
'Installable File System' Then 'UEFI'
End Is Not Null And tblEncryptableVolume.DriveLetter = 'C:'
Order By tblAssets.AssetName


-Kris
Hello Kris,

A strong thank you to you. You saved my week. It works!

Yes my goal was to get only the bootable partition.

Many thanks!
Alex

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now