Notification

Icon
Error

Report Merge - Merge two reports

Posted: Thursday, July 29, 2021 5:31:06 PM(UTC)
EnjetIT

EnjetIT

Member Original PosterPosts: 2
1
Like
This issue has been solved! Click here to view the solution
I would like to merge the following two reports. One will report the installed version of Office, while the other will show the value of a specific registry dword. The purpose is to show which workstations have Modern Authentication turned on (or off) against their installed version of Office. Thanks in advance!


##############Repport 1:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tblADusers.Displayname As [User],
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.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
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where (tblSoftwareUni.softwareName Like '%Office% %2013%' And
tblAssetCustom.State = 1) Or
(tblAssetCustom.State = 0)
Order By tblAssets.AssetName,
software,
version

################Report 2:

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,
TsysLastscan.Lasttime As LastRegistryScan,
Case
When TsysLastscan.Lasttime < GetDate() - 1 Then
'Last registry scan more than 24 hours ago! Scanned registry information may not be up-to-date. Try rescanning this machine.'
End As Comment,
Case
When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
Then 'Yes'
Else 'No'
End As ValuenameFound,
SubQuery1.Regkey,
SubQuery1.Valuename,
SubQuery1.Value,
SubQuery1.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where
tblRegistry.Regkey Like
'%SOFTWARE\Microsoft\Office\15.0\Common\Identity' And
tblRegistry.Valuename = 'EnableADAL') SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName
Andy.S
#1Andy.S Member Posts: 109  
posted: 8/2/2021 9:59:44 AM(UTC)
This should give you a starting point

Code:
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,
  TsysLastscan.Lasttime As LastRegistryScan,
  Case
    When TsysLastscan.Lasttime < GetDate() - 1 Then
      'Last registry scan more than 24 hours ago! Scanned registry information may not be up-to-date. Try rescanning this machine.'
  End As Comment,
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End As ValuenameFound,
  SubQuery1.Regkey,
  SubQuery1.Valuename,
  SubQuery1.Value,
  SubQuery1.Lastchanged,
  Query1.softwareName
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
  Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
  Left Join (Select Top 1000000 tblRegistry.AssetID,
        tblRegistry.Regkey,
        tblRegistry.Valuename,
        tblRegistry.Value,
        tblRegistry.Lastchanged
      From tblRegistry
      Where
        tblRegistry.Regkey Like
        '%SOFTWARE\Microsoft\Office\15.0\Common\Identity' And
        tblRegistry.Valuename = 'EnableADAL') SubQuery1 On SubQuery1.AssetID =
    tblAssets.AssetID
  Left Join (Select tblSoftwareUni.softwareName,
        tblSoftware.AssetID
      From tblSoftwareUni
        Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
      Where tblSoftwareUni.softwareName Like '%Office% %2013%') Query1
    On Query1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
  tblAssets.AssetName
EnjetIT
#2EnjetIT Member Original PosterPosts: 2  
posted: 8/2/2021 1:49:51 PM(UTC)
Elegant. Not only does it work, I can also extrapolate the syntax and learn for future reports. Thank you!

Active Discussions

Lansweeper History of relationship between pc and monitor
by  hfaddy  
Go to last post Go to first unread
Last post: Yesterday at 1:44:38 PM(UTC)
Lansweeper New Hire Login Request Report
by  Scott Davis   Go to last post Go to first unread
Last post: 9/16/2021 7:16:46 PM(UTC)
Lansweeper SCCM end of life
by  Esben.D  
Go to last post Go to first unread
Last post: 9/16/2021 2:56:23 PM(UTC)
Lansweeper Microsoft Patch Tuesday – September 2021
by  Esben.D  
Go to last post Go to first unread
Last post: 9/14/2021 8:27:06 PM(UTC)
Lansweeper Apple “FORCEDENTRY” Zero-Day Vulnerability
by  Esben.D   Go to last post Go to first unread
Last post: 9/14/2021 12:07:54 PM(UTC)
Lansweeper ALL laptops HP 340S G7 Notebook PC and Bios version
by  Carlos Montes  
Go to last post Go to first unread
Last post: 9/13/2021 3:17:19 PM(UTC)