cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Jono
Champion Sweeper II
Hello,

I have a report that works well. It lists all of our floater (loaner) laptops. Here it is.
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tblAssets.Domain,
tblAssetGroups.AssetGroup,
tblAssetCustom.Comments
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroupLink.AssetGroupID =
tblAssetGroups.AssetGroupID
Where tblAssetGroups.AssetGroup = N'Floaters' And tblAssetCustom.State = 1


I would like to add a column that shows the Office version. I tried adding a column like this:
Or
(tblSoftwareUni.softwareName Like 'Office%')
, but it listed all software if it had Office at all. I want to report to show exactly the same list as the above report, but adding the column to show the Office version. Is that possible?

Thanks,
Jono
1 ACCEPTED SOLUTION
Jono
Champion Sweeper II
Thanks RC62N! Copying and pasting brought in some unseen characters that gave me some errors, but I figured it out. Also, I changed the 1st LIKE to %Office% so it would include LibreOffice and OpenOffice.org, and I had to figure out how to get the column to show, but that wasn't too difficult. Here's what I ended up with.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tblAssets.Domain,
tblAssetGroups.AssetGroup,
tblAssetCustom.Comments,
msoffice.softwareName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroupLink.AssetGroupID =
tblAssetGroups.AssetGroupID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Office%' And
(tblSoftwareUni.softwareName Not Like '%Visio%' And
tblSoftwareUni.softwareName Not Like '%Compatibility%' And
tblSoftwareUni.softwareName Not Like '%Access%' And
tblSoftwareUni.softwareName Not Like '%Validation%')) As msoffice
On msoffice.AssetID = tblAssets.AssetID
Where tblAssetGroups.AssetGroup = N'Floaters' And tblAssetCustom.State = 1


As needed, I'll continue to add NOT LIKE statements, but for now this worked great.

View solution in original post

4 REPLIES 4
Jono
Champion Sweeper II
Thanks for the extra information. The scope of the report is only those assets that are in the Floaters asset group. This is a group of laptops that we use for loaners.

Thank you, also, for the quick code on listing the asset types. I've wondered about that. I've saved that report for use in the future.

Happy Thanksgiving! (if you're in the US, and even if you're not)

Jono
RCorbeil
Honored Sweeper II
Good to hear!

Expanding on it a bit, my own inventory includes more than just office suites with "Office" in the software name, such as HP OfficeJet software, so to extend your exclusion list a bit, mine would read
  LEFT JOIN (SELECT
tblSoftware.AssetID,
tblSoftwareUni.softwareName
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
tblSoftwareUni.softwareName LIKE 'LibreOffice%'
OR tblSoftwareUni.softwareName LIKE 'OpenOffice%'
OR (tblSoftwareUni.softwareName LIKE '%Corel%' AND tblSoftwareUni.softwareName LIKE '%Office%')
OR (tblSoftwareUni.softwareName LIKE 'Microsoft Office%'
AND ( tblSoftwareUni.softwareName NOT LIKE '%Visio%'
AND tblSoftwareUni.softwareName NOT LIKE '%Compatibility%'
AND tblSoftwareUni.softwareName NOT LIKE '%Access%'
AND tblSoftwareUni.softwareName NOT LIKE '%Validation%'
AND tblSoftwareUni.softwareName NOT LIKE '%Add-In%'
AND tblSoftwareUni.softwareName NOT LIKE '%Visual Studio%'
AND tblSoftwareUni.softwareName NOT LIKE '%Time Zone%'
AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
AND tblSoftwareUni.softwareName NOT LIKE '%Assemblies%'
AND tblSoftwareUni.softwareName NOT LIKE '%Meeting%'
AND tblSoftwareUni.softwareName NOT LIKE '%Web Components%'
)
)
) AS msoffice ON msoffice.AssetID = tblAssets.AssetID

And are you intentionally listing all assets? If not, you can add another filter to the WHERE clause
  AND tblAssets.AssetType = -1

for only Windows machines.

If you want select asset types, create a report that just shows you the contents of tSysAssetTypes for a list of the type codes and associated descriptions.
SELECT Top 1000000 *
FROM tsysAssetTypes

Then expand that last condition, e.g.
  AND tblAssets.AssetType IN (-1, 22, 13)

for Windows, WinCE and Mac, for instance.
Jono
Champion Sweeper II
Thanks RC62N! Copying and pasting brought in some unseen characters that gave me some errors, but I figured it out. Also, I changed the 1st LIKE to %Office% so it would include LibreOffice and OpenOffice.org, and I had to figure out how to get the column to show, but that wasn't too difficult. Here's what I ended up with.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tblAssets.Domain,
tblAssetGroups.AssetGroup,
tblAssetCustom.Comments,
msoffice.softwareName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroupLink.AssetGroupID =
tblAssetGroups.AssetGroupID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Office%' And
(tblSoftwareUni.softwareName Not Like '%Visio%' And
tblSoftwareUni.softwareName Not Like '%Compatibility%' And
tblSoftwareUni.softwareName Not Like '%Access%' And
tblSoftwareUni.softwareName Not Like '%Validation%')) As msoffice
On msoffice.AssetID = tblAssets.AssetID
Where tblAssetGroups.AssetGroup = N'Floaters' And tblAssetCustom.State = 1


As needed, I'll continue to add NOT LIKE statements, but for now this worked great.
RCorbeil
Honored Sweeper II
One approach:
SELECT
Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tblAssets.Domain,
tblAssetGroups.AssetGroup,
tblAssetCustom.Comments
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tblAssets.Assettype = tsysAssetTypes.AssetType
INNER JOIN tblAssetGroupLink ON tblAssets.AssetID = tblAssetGroupLink.AssetID
INNER JOIN tblAssetGroups ON tblAssetGroupLink.AssetGroupID = tblAssetGroups.AssetGroupID
LEFT JOIN ( SELECT
tblSoftware.AssetID,
tblSoftwareUni.SoftwareName
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE
tblSoftwareUni.SoftwarePublisher LIKE '%microsoft%'
AND (tblSoftwareUni.softwareName LIKE '%Office 2000%'
OR tblSoftwareUni.softwareName LIKE '%Office Standard%'
OR tblSoftwareUni.softwareName LIKE '%Office Professional%'
OR tblSoftwareUni.softwareName LIKE '%Office Home & Student%'
OR tblSoftwareUni.softwareName LIKE '%Office Home & Business%')
) AS msoffice ON msoffice.AssetID=tblAssets.AssetID
WHERE
tblAssetGroups.AssetGroup = N'Floaters'
AND tblAssetCustom.State = 1

Microsoft has a tendency to prepend "Microsoft Office" on a lot of components that aren't actually Microsoft Office, so you may need to edit the inclusion list or, alternatively, create an exclusion list (LIKE 'Microsoft Office%" AND NOT LIKE '%Viewer%' AND NOT LIKE '%Visio%" AND NOT LIKE etc.).