→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
07-05-2023 04:56 PM - last edited on 04-01-2024 12:43 PM by Mercedes_O
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]
Solved! Go to Solution.
07-05-2023 06:19 PM
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
04-22-2024 09:32 AM - edited 04-22-2024 09:33 AM
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
04-22-2024 04:30 PM
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))
04-23-2024 08:51 AM
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
07-05-2023 06:19 PM
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
07-05-2023 06:34 PM
This worked perfectly! Thankyou very much for your assistance!
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now