Need Assistance with a Registry key report

[Ben-rutherford]

Good Afternoon, 

I am trying to get a report to let me know which channels office is set to update off of, 

I can report on the Key - That is working. But i want to make it easier by replacing the key values with Easily readable values, Or atleast have it in a column 
Here is the report: 

elect Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblRegistry.Valuename,
  tblRegistry.Value
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Where tblRegistry.Valuename = 'UpdateChannel' And tblRegistry.regkey =
  'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration' And
  tblAssetCustom.State = 1

And this is what i want to incorperate 

  Case 
  When tblRegistry.value = "http://officecdn.microsoft.com/pr/55336b82-a18d-4dd6-b5f6-9e5095c314a6" Then 'Monthly Enterprise Channel'
  When tblRegistry.value = "http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60" Then 'Current Channel'
  When tblRegistry.value = "http://officecdn.microsoft.com/pr/64256afe-f5d9-4f86-8936-8840a6a4f5be" Then 'Current Preview'
  When tblRegistry.value = "http://officecdn.microsoft.com/pr/7ffbc6bf-bc32-4f92-8982-f9dd17fd3114" Then 'Semi Annual Enterprise Channel'
  Else 'Unknown'
  End As [Update Channel] 

[SteveHernou]

Hi @KevinA-REJIS 

Is there a way in LS to also include the bitness of the Office apps in the same report? I get that also from the registry scanning of the same reg path HKLM\......\Configuration, but a different value (Platform instead of CDNBaseUrl) which is then either x86 or x64.

Best regards

[KevinA-REJIS]

Hi @SteveHernou 

What I ended up doing was using the install location to determine the bitness. You'll have to add the two file locations to the File Scanning section in Lansweeper (I used Winword.exe):

C:\Program Files\Microsoft Office\root\Office16\Winword.exe

C:\Program Files (x86)\Microsoft Office\root\Office16\Winword.exe

I put this in the Select statement:

Case
    When
      tblFileVersions.FilePathfull =
      'C:\Program Files\Microsoft Office\root\Office16\WINWORD.EXE' And
      tblFileVersions.Found = 1 Then '64-bit'
    When
      tblFileVersions.FilePathfull =
      'C:\Program Files (x86)\Microsoft Office\root\Office16\WINWORD.EXE' And
      tblFileVersions.Found = 1 Then '32-bit'
  End As 'Architecture',

These two lines in the From statement: 

  Inner Join tblSoftware On tblAssets_1.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID

And this in the Where statement:

((tblFileVersions.FilePathfull =
      'C:\Program Files\Microsoft Office\root\Office16\WINWORD.EXE' And
      tblFileVersions.Found = 1) Or (tblFileVersions.FilePathfull =
      'C:\Program Files (x86)\Microsoft Office\root\Office16\WINWORD.EXE' And
      tblFileVersions.Found = 1))

[SteveHernou]

Wonderful. Thank you so much. Didn't occur to me to mix detection types of file and registry.

I had some other requirements so this is what I ended up with

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case
When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop'
End
End As Type,
tblState.Statename As [Asset state],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblOperatingsystem.Caption As OS,
tSoftware.softwareName,
tSoftware.SoftwarePublisher,
tSoftware.softwareVersion,
Case
When
tblRegistry.value =
'http://officecdn.microsoft.com/pr/55336b82-a18d-4dd6-b5f6-9e5095c314a6'
Then 'Monthly Enterprise Channel'
When
tblRegistry.value =
'http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60'
Then 'Current Channel'
When
tblRegistry.value =
'http://officecdn.microsoft.com/pr/64256afe-f5d9-4f86-8936-8840a6a4f5be'
Then 'Current Preview'
When
tblRegistry.value =
'http://officecdn.microsoft.com/pr/7ffbc6bf-bc32-4f92-8982-f9dd17fd3114'
Then 'Semi Annual Enterprise Channel'
Else 'Unknown'
End As [Update Channel],
Case
When
tblFileVersions.FilePathfull =
'C:\Program Files\Microsoft Office\root\Office16\WINWORD.EXE' And
tblFileVersions.Found = 1 Then '64-bit'
When
tblFileVersions.FilePathfull =
'C:\Program Files (x86)\Microsoft Office\root\Office16\WINWORD.EXE' And
tblFileVersions.Found = 1 Then '32-bit'
End As 'Architecture'
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
(tblSoftwareUni.softwareName = 'Microsoft 365 Apps for enterprise - en-us'
Or tblSoftwareUni.softwareName = 'Microsoft Office 365 ProPlus - en-us')
And tblSoftwareUni.SoftwarePublisher = 'Microsoft Corporation') tSoftware
On tSoftware.AssetID = tblAssets.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Where tblState.Statename = 'Active' And tSoftware.softwareVersion Is Not Null
And tblComputersystem.Domainrole < 2 And tblRegistry.Valuename = 'CDNBaseUrl'
And
tblRegistry.regkey =
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration' And
tblAssetCustom.State = 1 And ((tblFileVersions.FilePathfull =
'C:\Program Files\Microsoft Office\root\Office16\WINWORD.EXE' And
tblFileVersions.Found = 1) Or (tblFileVersions.FilePathfull =
'C:\Program Files (x86)\Microsoft Office\root\Office16\WINWORD.EXE' And
tblFileVersions.Found = 1))
Order By tblAssets.Domain,
tblAssets.AssetName,
tSoftware.softwareName

[KevinA-REJIS · Accepted solution]

Give this a try, you could remove the tblRegistry.Valuename and tblRegistry.Value if want/need:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblRegistry.Valuename,
  tblRegistry.Value,
Case 
  When tblRegistry.value = "http://officecdn.microsoft.com/pr/55336b82-a18d-4dd6-b5f6-9e5095c314a6" Then 'Monthly Enterprise Channel'
  When tblRegistry.value = "http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60" Then 'Current Channel'
  When tblRegistry.value = "http://officecdn.microsoft.com/pr/64256afe-f5d9-4f86-8936-8840a6a4f5be" Then 'Current Preview'
  When tblRegistry.value = "http://officecdn.microsoft.com/pr/7ffbc6bf-bc32-4f92-8982-f9dd17fd3114" Then 'Semi Annual Enterprise Channel'
  Else 'Unknown'
  End As [Update Channel]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Where tblRegistry.Valuename = 'UpdateChannel' And tblRegistry.regkey =
  'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration' And
  tblAssetCustom.State = 1

[Ben-rutherford]

This worked perfectly! Thankyou very much for your assistance!