Software versions for multiple installs

[Jambi]

Hi all, I'm trying to create a report with all installed versions of Citrix Workspace/Receiver/Online Plug-in.  I have that working, but I also need to see if two additional applications are installed.  I have a simple yes/no check working for that, but I'm trying to get the software versions for those two as well and that's where I'm getting stuck.  Here's what I have so far, not sure what to add to check verisons for CitrixAudio and CitrixPowerMic.  Any help is appreciated!

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.IPAddress,
  tblAssets.Username,
  tsysOS.OSname,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion As [Citrix Version],
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End As CitrixSSOEnabled,
  Case
    When CitrixAudio.softID Is Null Then 'No'
    Else 'Yes'
  End As DMOCitrixAudio,
  Case
    When CitrixPowerMic.softID Is Null Then 'No'
    Else 'Yes'
  End As DMOCitrixPowerMic,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As softwareName1
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftware.softID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where
      tblSoftwareUni.softwareName Like 'Nuance Citrix Client Audio Extension%')
  As CitrixAudio On CitrixAudio.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftware.softID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where
      tblSoftwareUni.softwareName Like
      'Nuance PowerMic Citrix Client Extension%') As CitrixPowerMic On
      CitrixPowerMic.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Citrix\ICA Client\SSON'
      And tblRegistry.Valuename = 'Enable') SubQuery1 On SubQuery1.AssetID =
      tblAssets.AssetID And tblSoftwareUni.softwareName Like 'Citrix%'
      And tblAssetCustom.State = 1
Where tblSoftwareUni.softwareName Like 'Citrix%'
Group By tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.IPAddress,
  tblAssets.Username,
  tsysOS.OSname,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  CitrixAudio.softID,
  CitrixPowerMic.softID,
  SubQuery1.Regkey,
  SubQuery1.Valuename,
  SubQuery1.Value,
  SubQuery1.Lastchanged,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName
Order By tblAssets.AssetName

[rader · Accepted solution]

While I don't have any assets with the Citrix plugins like your describing, I ran the report and found one Citrix Online Launcher and could see the Audio/PowerMic yes/no fields you described. Playing around with the report you posted, I found that if you enable the software version in each subreport (audio & powermic), then head back to the main report, you could enable those checkboxes for the versions and rename their fields. While I have no data for the report to show, your mileage might vary.

Try this report with those minor modifications. Good luck.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.IPAddress,
  tblAssets.Username,
  tsysOS.OSname,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion As [Citrix Version],
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End As CitrixSSOEnabled,
  Case
    When CitrixAudio.softID Is Null Then 'No'
    Else 'Yes'
  End As DMOCitrixAudio,
  Case
    When CitrixPowerMic.softID Is Null Then 'No'
    Else 'Yes'
  End As DMOCitrixPowerMic,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As softwareName1,
  CitrixAudio.softwareVersion As AudioVersion,
  CitrixPowerMic.softwareVersion As PowerMicVersion
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftware.softID,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where
      tblSoftwareUni.softwareName Like 'Nuance Citrix Client Audio Extension%')
  As CitrixAudio On CitrixAudio.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftware.softID,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where
      tblSoftwareUni.softwareName Like
      'Nuance PowerMic Citrix Client Extension%') As CitrixPowerMic On
      CitrixPowerMic.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Citrix\ICA Client\SSON'
      And tblRegistry.Valuename = 'Enable') SubQuery1 On SubQuery1.AssetID =
      tblAssets.AssetID And tblSoftwareUni.softwareName Like 'Citrix%'
      And tblAssetCustom.State = 1
Where tblSoftwareUni.softwareName Like 'Citrix%'
Group By tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.IPAddress,
  tblAssets.Username,
  tsysOS.OSname,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  CitrixAudio.softwareVersion,
  CitrixAudio.softID,
  CitrixPowerMic.softID,
  SubQuery1.Regkey,
  SubQuery1.Valuename,
  SubQuery1.Value,
  SubQuery1.Lastchanged,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName,
  CitrixPowerMic.softwareVersion
Order By tblAssets.AssetName

[Jambi]

Worked perfectly, thank you!