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

Hi all,

 If I try to edit the old query Active Directory: Enabled/disabled computers it goes into error

mgas_0-1666009093062.png

 

Select Top 1000000 Coalesce(os.Image, at.AssetTypeIcon10) As Icon,
a.AssetID,
a.AssetName,
Case
When Coalesce(adc.IsEnabled, 0) = 0 Then 'False'
Else 'True'
End As EnabledInAD,
at.AssetTypename As Type,
a.Domain,
a.Username,
a.Userdomain,
a.IPAddress,
a.Description,
ac.Manufacturer,
ac.Model,
Case (a.Assettype)
When -1 Then os.OSname
When 11 Then ls.OSRelease
When 13 Then mi.SystemVersion
Else ''''
End As OS,
a.SP As SP,
ac.Location,
tsysIPLocations.IPLocation,
a.Firstseen,
a.Lastseen
From tblAssets a
Inner Join tblADComputers adc On a.AssetID = adc.AssetID
Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
Inner Join tsysAssetTypes at On at.AssetType = a.Assettype
Inner Join tblState s On s.State = ac.State
Left Join tsysOS os On a.OScode = os.OScode
Left Join tblLinuxSystem ls On ls.AssetID = a.AssetID
Left Join tblMacOSInfo mi On mi.AssetID = a.AssetID
Left Outer Join tsysIPLocations On a.LocationID = tsysIPLocations.LocationID
Where s.Statename = 'Active'
Order By adc.IsEnabled Desc,
a.Assettype,
a.AssetName

 do you have any suggestions? thank you!

 

1 ACCEPTED SOLUTION
KevinA-REJIS
Champion Sweeper III

This is what the code ended up looking like to get it running again in our environment:

Select Top (1000000) Coalesce(os.Image, t.AssetTypeIcon10) As Icon,
  a.AssetID,
  a.AssetName,
  Case
    When Coalesce(adc.IsEnabled, 0) = 0 Then 'False'
    Else 'True'
  End As EnabledInAD,
  t.AssetTypename As Type,
  a.Domain,
  a.Username,
  a.Userdomain,
  a.IPAddress,
  a.Description,
  ac.Manufacturer,
  ac.Model,
  Case (t.Assettype)
    When -1 Then os.OSname
    When 11 Then ls.OSRelease
    When 13 Then mi.SystemVersion
    Else ''
  End As OS,
  a.SP,
  ac.Location,
  tsysIPLocations.IPLocation,
  a.Firstseen As [Created at],
  a.Lastseen As [Last successful scan]
From tblAssets As a
  Inner Join tblADComputers As adc On a.AssetID = adc.AssetID
  Inner Join tblAssetCustom As ac On a.AssetID = ac.AssetID
  Inner Join tsysAssetTypes As t On a.AssetType = t.Assettype
  Inner Join tblState As s On ac.State = ac.State
  Left Outer Join tsysOS As os On a.OScode = os.OScode
  Left Outer Join tblLinuxSystem As ls On ls.AssetID = a.AssetID
  Left Outer Join tblMacOSInfo As mi On mi.AssetID = a.AssetID
  Left Outer Join tsysIPLocations On a.LocationID = tsysIPLocations.LocationID
Where s.Statename = 'Active'
Order By adc.IsEnabled Desc,
  a.Assettype,
  a.AssetName

View solution in original post

2 REPLIES 2
KevinA-REJIS
Champion Sweeper III

This is what the code ended up looking like to get it running again in our environment:

Select Top (1000000) Coalesce(os.Image, t.AssetTypeIcon10) As Icon,
  a.AssetID,
  a.AssetName,
  Case
    When Coalesce(adc.IsEnabled, 0) = 0 Then 'False'
    Else 'True'
  End As EnabledInAD,
  t.AssetTypename As Type,
  a.Domain,
  a.Username,
  a.Userdomain,
  a.IPAddress,
  a.Description,
  ac.Manufacturer,
  ac.Model,
  Case (t.Assettype)
    When -1 Then os.OSname
    When 11 Then ls.OSRelease
    When 13 Then mi.SystemVersion
    Else ''
  End As OS,
  a.SP,
  ac.Location,
  tsysIPLocations.IPLocation,
  a.Firstseen As [Created at],
  a.Lastseen As [Last successful scan]
From tblAssets As a
  Inner Join tblADComputers As adc On a.AssetID = adc.AssetID
  Inner Join tblAssetCustom As ac On a.AssetID = ac.AssetID
  Inner Join tsysAssetTypes As t On a.AssetType = t.Assettype
  Inner Join tblState As s On ac.State = ac.State
  Left Outer Join tsysOS As os On a.OScode = os.OScode
  Left Outer Join tblLinuxSystem As ls On ls.AssetID = a.AssetID
  Left Outer Join tblMacOSInfo As mi On mi.AssetID = a.AssetID
  Left Outer Join tsysIPLocations On a.LocationID = tsysIPLocations.LocationID
Where s.Statename = 'Active'
Order By adc.IsEnabled Desc,
  a.Assettype,
  a.AssetName

Thank You!