Custom report for corel

[hitechgreg]

I received an audit request from Corel wanting to know what software products we had installed on PCs along with PC names, manufacturer, and software version. I'd like to also give them the model of the PC as well.

I've looked around at the reports here, even tried some but dont seem to be able to get what i'm looking for. I'd greatly appreciate some assistance with this.

They're wanting to know what PCs we have Roxio Creator/Burn installed on as well as WinDVD. Roxio comes with several different installed packages, listed below.

Roxio Activation Module
Roxio Creator Audio
Roxio Creator BDAV Plugin
Roxio Creator Copy
Roxio Creator DE
Roxio Creator Data
Roxio Creator Tools
Roxio Drag-to-Disc
Roxio Update Manager
Roxio Express Labeler
Roxio BackOn Track
Sonic CinePlayer Decoder Pack
Roxio Burn
Roxio Burn Engine
Roxio Creator Starter

Is there a way to give them PCs that this software isn't installed on as well? I want to be fully forth coming on this request.

Thanks in advance!

[IanH50]

Helped me too I have just received the same letter

[hitechgreg]

RC, ABSOLUTELY AWESOME!



Thank you so much! You don't know how much I appreciate this!!

Works like a charm. Exactly what I needed too! THANK YOU!!

[RCorbeil · Accepted solution]

List of all the matching software:

Select Top 1000000
  tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Description,
  tblAssets.Lastseen,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion
From
  tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
  tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
  And ( tblSoftwareUni.softwareName Like 'Roxio%'
        Or tblSoftwareUni.softwareName Like '%CinePlayer%'
        Or tblSoftwareUni.softwareName Like '%WinDVD%')
Order By
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblSoftwareUni.softwareName

List of machines with no matching software:

Select Top 1000000
  tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Description,
  tblAssets.Lastseen,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber
From
  tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
  tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
  And Not Exists (SELECT AssetID
                  FROM   tblSoftware
                         Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.SoftID
                  WHERE tblSoftware.AssetID = tblAssets.AssetID
                        And 
                            ( tblSoftwareUni.softwareName Like 'Roxio%'
                              Or tblSoftwareUni.softwareName Like '%CinePlayer%'
                              Or tblSoftwareUni.softwareName Like '%WinDVD%'
                            )
                 )
Order By
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber

If you change the software match conditions in one, make sure to change the other's conditions to be the same.