Skype for Business Report

[mmeetze]

Since Skype for business came through KB2889853 as a Windows Update I was trying to build a report that would list all machines that had this update installed. Is there a better way to query for Skype for Business Clients? I tried the built in Autorun for Skype report but that looks like the personal Skype client and not Skype for Business.



Thanks!

[spallison]

I have used both reports above, but it doesnt show the hotfix in any lists, I know it is installed in places

[Susan_A]

spallison wrote:
I have used both reports above, but it doesnt show the hotfix in any lists, I know it is installed in places

Only system-wide updates are scanned by default. As I briefly mentioned in my previous post as well, I'm not sure the Skype update falls under this category. If it doesn't and if it's not listed in Add/Remove Programs either, your best option would be to scan a registry value associated with it. General registry scanning instructions can be found in this knowledge base article.

[Susan_A · Accepted solution]

Just an FYI for everyone: the "upgrade_" views in eriken's report only exist in Lansweeper installations that were updated from version 4.X to version 5.X. You can't use these in Lansweeper installations that weren't updated from 4.X. To build a report in 5.X that lists machines with a specific hotfix installed, follow the steps below. Keep in mind that you can only report on system-wide updates this way. I'm not sure the Skype update falls under this category. If not, you may be able to report on it with a software (tblSoftware/tblSoftwareUni) report if the software is listed in Add/Remove Programs or through custom registry scanning instead.

• Open the report builder under Reports\Create New Report.
  
• Double-click on tblQuickFixEngineering and tblQuickFixEngineeringUni in the table list on the right to add the tables to your report.
  
• In the visual representation of the report in the upper section of the report builder, tick the HotFixID field in tblQuickFixEngineeringUni.
  
• Give the report a title and hit Save & Run.
  
• Filter the HotFixID column within the report results.

[mmeetze]

Whenever I try to use that query I get an Invalid Select statement for the unknown object of upgrade_tblcomputers

[eriken]

I use this

Select Top 1000000 tblADusers.Displayname,
upgrade_tblComputers.ComputerUnique,
upgrade_tblSoftware.SoftwarePublisher As Publisher,
upgrade_tblSoftware.softwareName As Software,
upgrade_tblSoftware.Lastchanged
From upgrade_tblComputers
Inner Join upgrade_tblSoftware On upgrade_tblComputers.Computername =
upgrade_tblSoftware.ComputerName
Inner Join upgrade_web40ActiveComputers On upgrade_tblComputers.Computername =
upgrade_web40ActiveComputers.Computername
Inner Join tblADusers On upgrade_tblComputers.Username = tblADusers.Username
Where (upgrade_tblSoftware.softwareName Like '%Skype for Business%') Or
(upgrade_tblSoftware.softwareName Like '%Lync%')
Order By tblADusers.Displayname

[mmeetze]

Here is the query I have, however it is returning clients that have Lync 2010 and Office 2007 but do not have Skype for Business on them. I created One custom scan using a registry key in the HKCU\Software\Microsoft\Office\15.0\Lync location and could create a second to look in the Office 16.0 folder for our Office 2016 Users and then merge the two reports. But if theres a SQL guru out there who could possibly spot an error in my query below it would be much simpler to only have one report to encompass all. In the query I am searching for the KB that our WSUS pushed out for the Skype for Business client, I have also excluded any Servers.


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode And tblAssets.OScode =
tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where
tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID Like 'KB2889853') And
tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName