OU-based report of computers

[Rpalacio2024]

I need to create a computer report that contains a column with the OU and another where it indicates Win11, Anterior or X depending on the OU and sub OU:

OU=Coworking, OU=Workstations Win11, DC=dominio, DC=local
OU=Coworking, OU=Workstations, DC=dominio, DC=local
OU=Coworking, OU=Otra, DC=dominio, DC=local

OU=Workstations Win11, DC=dominio, DC=local = Win11
OU=Workstations, DC=dominio, DC=local = Anterior
OU=Otra, DC=dominio, DC=local = X

Select Top (1000000) Case
When Coalesce(tblADComputers.IsEnabled, 0) = 0 Then 'False'
Else 'True'
End As Enabled,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress As IP,
tblAssets.Username As [Usuario firmado],
tsysOS.OSname As OS,
tblAssets.OScode,
tblAssets.Version,
Case
When
tblADComputers.OU In
('%OU=Workstations Win11, DC=domain, DC=local%') Then 'Win 11'
When tblADComputers.OU In ('%OU=Workstations, DC=domain, DC=local%')
Then 'Anterior'
Else 'X'
End As Hardening,
tblADComputers.OU,
tsysIPLocations.IPLocation,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.Domain,
tblAssets.Lastseen As [Last successful scan],
tblAssets.Firstseen As [Created at],
tblAssets.Lasttried,
tblAssetCustom.PurchaseDate
From tblAssets
Left Outer Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tblLinuxSystem On tblLinuxSystem.AssetID = tblAssets.AssetID
Left Outer Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblComputersystem.Domainrole < 2
Order By IP

In this report everything comes out with X

[ASismey · Accepted solution]

Hi,

I think the commas in the OU path are causing issues try a like statement something like

 Case
    When
      tblADComputers.OU Like
      'OU=Workstation%OU=Windows11%DC=Domain%DC=local' Then 'Win 11'
    When tblADComputers.OU Like 'OU=workstations%DC=domain%DC=local' Then
      'Anterior'
    Else 'X'
  End As Hardening,

[ASismey · Accepted solution]

Hi,

I think the commas in the OU path are causing issues try a like statement something like

 Case
    When
      tblADComputers.OU Like
      'OU=Workstation%OU=Windows11%DC=Domain%DC=local' Then 'Win 11'
    When tblADComputers.OU Like 'OU=workstations%DC=domain%DC=local' Then
      'Anterior'
    Else 'X'
  End As Hardening,

[Rpalacio2024 · Accepted solution]

It works:

Case
When
tblADComputers.OU Like
'%OU=Workstations Win11%DC=domainf%DC=local%' Then 'Win 11'
When tblADComputers.OU Like '%OU=Workstations%DC=domain%DC=local%'
Then 'Anterior'
Else 'X'
End As Hardening,

[Rpalacio2024]

Continue the result of X in all fields

[ASismey]

Have you got your new code for me to see ?