cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ccarlisle
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Andy_Sismey
Champion Sweeper III
This should give you a starting point

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

View solution in original post

2 REPLIES 2
ccarlisle
Engaged Sweeper
Elegant. Not only does it work, I can also extrapolate the syntax and learn for future reports. Thank you!
Andy_Sismey
Champion Sweeper III
This should give you a starting point

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