Notification

Icon
Error

Duplicates (3-4) in report please helppppp!

Posted: Wednesday, July 1, 2020 12:29:46 AM(UTC)
AlexMZetec

AlexMZetec

Member Original PosterPosts: 2
0
Like
This issue has been solved! Click here to view the solution
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!!!!!!!!
Brick wall


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
KrisNelson
#1KrisNelson Member Posts: 45  
posted: 7/1/2020 3:09:19 PM(UTC)
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.


Code:
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
AlexMZetec
#2AlexMZetec Member Original PosterPosts: 2  
posted: 7/2/2020 3:15:16 PM(UTC)
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

Active Discussions

Lansweeper Help Desk Workflow
by  lswanson   Go to last post Go to first unread
Last post: Yesterday at 11:12:21 PM(UTC)
Lansweeper Changing to remote scanning due to COVID
by  FrankSc  
Go to last post Go to first unread
Last post: Yesterday at 9:35:26 PM(UTC)
Lansweeper Not giving hackers the Domain Admin password / account
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 9:27:13 PM(UTC)
Lansweeper LsAgent failing - Lansweeper SSL Expired
by  lansweeper25t34  
Go to last post Go to first unread
Last post: Yesterday at 8:33:28 PM(UTC)
Lansweeper Is there a chance to get the firewall off via Lansweeper?
by  EDV_OHZ   Go to last post Go to first unread
Last post: Yesterday at 4:57:26 PM(UTC)
Lansweeper Merge Asset button
by  KeithBecker  
Go to last post Go to first unread
Last post: 8/5/2020 9:27:54 PM(UTC)
Lansweeper Merge Two Assets or Update Based on Serial Number
by  KeithBecker   Go to last post Go to first unread
Last post: 8/5/2020 9:25:16 PM(UTC)
Lansweeper Hyper-V guest assets
by  bgstein  
Go to last post Go to first unread
Last post: 8/5/2020 5:50:24 PM(UTC)