cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Combining 2 reports Power Plan and Time Out Settings

paulruvalcaba
Engaged Sweeper III
I'm trying to join 2 reports into 1 and keep getting too many results. The following are the 2 reports, the 1 I was able to get without errors, but get too many results from, and the lines that I noticed joined similarly.


Power Plan Report

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
TsysChassisTypes.ChassisName As ComputerType,
tblAssets.Username,
tblADusers.Firstname As [First Name],
tblADusers.Lastname As [Last Name],
tblAssets.IPAddress,
tsysAssetTypes.AssetTypeIcon10 As icon,
Case tblRegistry.Value
When '381b4222-f694-41f0-9685-ff5bb260df2e' Then 'Balanced'
When '8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' Then 'High Performance'
When 'a1841308-3541-4fab-bc81-f71556f20b4a' Then 'Power Saver'
Else 'Unknown/Custom'
End As [Power plan],
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username,
tblAssetGroups
Where tblAssetCustom.State = 1 And tblRegistry.Valuename = 'ActivePowerScheme'
And tblAssetGroups.AssetGroup = '!All Workstations'

Order By tblAssets.AssetName



Timeout Report

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysOS.OSname,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.Lastseen,
tRegistry1.Value As Battery,
tRegistry2.Value As PluggedIn,
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select tblAssetUserRelations.AssetID,
tblAssetUserRelations.Username,
tblAssetUserRelations.Userdomain
From tblAssetUserRelations
Where tblAssetUserRelations.Type = 1) tOwner On tblAssets.AssetID =
tOwner.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Value
From tblRegistry
Where
tblRegistry.Regkey Like
'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\PowerSettings\238C9FA8-0AAD-41ED-83F4-97BE242C8F20\29f6c1db-86da-48c5-9fdb-f2b67b1f44da\DefaultPowerSchemeValues\8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' And tblRegistry.Valuename = 'DcSettingIndex') tRegistry1 On tblAssets.AssetID = tRegistry1.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Value
From tblRegistry
Where tblRegistry.Regkey Like 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\PowerSettings\238C9FA8-0AAD-41ED-83F4-97BE242C8F20\29f6c1db-86da-48c5-9fdb-f2b67b1f44da\DefaultPowerSchemeValues\8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' And
tblRegistry.Valuename = 'AcSettingIndex') tRegistry2 On tblAssets.AssetID =
tRegistry2.AssetID

Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join lansweeperdb.dbo.tblADusers On tblADusers.Username =
tblAssets.Username
Where tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.State = 1





Report I get that gives me no errors, but has way too many results.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysOS.OSname,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.Lastseen,
Case tblRegistry.Value
When '381b4222-f694-41f0-9685-ff5bb260df2e' Then 'Balanced'
When '8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' Then 'High Performance'
When 'a1841308-3541-4fab-bc81-f71556f20b4a' Then 'Power Saver'
Else 'Unknown/Custom'
End As [Power plan],
tRegistry1.Value As Battery,
tRegistry2.Value As PluggedIn
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select tblAssetUserRelations.AssetID,
tblAssetUserRelations.Username,
tblAssetUserRelations.Userdomain
From tblAssetUserRelations
Where tblAssetUserRelations.Type = 1) tOwner On tblAssets.AssetID =
tOwner.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Value
From tblRegistry
Where
tblRegistry.Regkey Like
'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\PowerSettings\238C9FA8-0AAD-41ED-83F4-97BE242C8F20\29f6c1db-86da-48c5-9fdb-f2b67b1f44da\DefaultPowerSchemeValues\8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' And tblRegistry.Valuename = 'DcSettingIndex') tRegistry1 On tblAssets.AssetID = tRegistry1.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Value
From tblRegistry
Where
tblRegistry.Regkey Like
'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\PowerSettings\238C9FA8-0AAD-41ED-83F4-97BE242C8F20\29f6c1db-86da-48c5-9fdb-f2b67b1f44da\DefaultPowerSchemeValues\8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' And tblRegistry.Valuename = 'AcSettingIndex') tRegistry2 On tblAssets.AssetID = tRegistry2.AssetID
Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join lansweeperdb.dbo.tblADusers On tblADusers.Username =
tblAssets.Username
Inner Join lansweeperdb.dbo.tblRegistry On tblAssets.AssetID =
tblRegistry.AssetID
Where tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.State = 1




I did notice that these lines Inner Join to the same tables.
  Inner Join lansweeperdb.dbo.tblADusers On tblADusers.Username =
tblAssets.Username
Inner Join lansweeperdb.dbo.tblRegistry On tblAssets.AssetID =
tblRegistry.AssetID



  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username,
tblAssetGroups



What is the difference between lansweeperdb.tblRegistry and tblRegistry?

Not quite sure what the comma and "tblassetgroups does to that line though.
0 REPLIES 0