‎08-19-2024 06:01 PM
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
Solved! Go to Solution.
‎08-27-2024 06:58 PM
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
‎08-27-2024 06:58 PM
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
‎08-27-2024 09:41 PM
Worked perfectly, thank you!
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now