cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Samane_rashidi
Engaged Sweeper
I produce a report for users who have Microsoft office software, but each user is repeated more than 10 times because they have different types of Microsoft office software. Is there a way that I can limit them. I want each user will be appear only once.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
LTrim(RTrim(Coalesce(tblAssetCustom.Manufacturer, N'') + N' ' +
Coalesce(tblAssetCustom.Model, N''))) As CompModel,
tsysOS.OSname,
tblLanguages.Language,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Memory,
tblAssets.Processor,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
[HDD Free],
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
[HDD Size],
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblLanguages On tblLanguages.LanguageCode =
tblOperatingsystem.OSLanguage
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID,
tblSoftwareUni
Where tblAssets.Lastseen > GetDate() - 60 And tblSoftwareUni.softwareName Like
'%Microsoft Office%' And tblSoftwareUni.softwareName Not Like
'Microsoft Office Professional Plus 2016' And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole <= 1 And tblDiskdrives.Caption = 'C:'
1 REPLY 1
RCorbeil
Honored Sweeper II
You've got two problems. First, you haven't linked tblAssets to tblSoftwareUni, so you're going to get a mess of results. You need to link tblAssets -> tblSoftware -> tblSoftwareUni.
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblLanguages On tblLanguages.LanguageCode = tblOperatingsystem.OSLanguage
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID

Second, too many pieces of software are named "Microsoft Office something-or-other". Blame Microsoft's lack of creativity for that one. You're going to need to either set your sites on specific names or create a bunch of "not X", "not Y", "not Z" conditions to eliminate the stuff you know you don't want to see. This has come up more than once before. Take at look at this thread, for example.