Keep Getting an Error When Running My SQL Code

Question

I keep receiving the dreaded "An expression of non-boolean type specified in a context where a condition is expected." errorr at the bolded "dbo.v_R_System.ResourceID" in the code below in the "From' statement section. And I also get the "Incorrect syntax near the keyword 'WHERE'." error message as well.

Where, in this query of mine, is the error?

SELECT DISTINCT
    dbo.v_GS_COMPUTER_SYSTEM.Name0 AS [Computer Name],
                       [Top Console User] = CASE 
                             WHEN (dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1') 
                             THEN 'Unknown' 
                             ELSE dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 
                             END, 
                        dbo.v_R_System.AD_Site_Name0 AS [Active Directory Site Name],
                        dbo.v_R_System.Resource_Domain_OR_Workgr0 AS [Domain Name], 
                        dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], 
                        dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack],
                        dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Computer Model], 
                        dbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS [OS Install Date]

 FROM         
    dbo.v_GS_COMPUTER_SYSTEM INNER JOIN
                       dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID INNER JOIN 
                       dbo.v_GS_OPERATING_SYSTEM INNER JOIN
                       dbo.v_R_System ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = **dbo.v_R_System.ResourceID**

 WHERE
    dbo.v_GS_OPERATING_SYSTEM.Caption0 NOT LIKE '%Server%'

 GROUP BY
    dbo.v_GS_COMPUTER_SYSTEM.Name0, 
                       dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0,
                       dbo.v_R_System.AD_Site_Name0, 
                       dbo.v_R_System.Resource_Domain_OR_Workgr0, 
                       dbo.v_GS_OPERATING_SYSTEM.CSDVersion0, 
                       dbo.v_GS_PC_BIOS.Manufacturer0, 
                       dbo.v_GS_COMPUTER_SYSTEM.NumberOfProcessors0,
                       dbo.v_GS_OPERATING_SYSTEM.Caption0, 
                       dbo.v_GS_COMPUTER_SYSTEM.Model0,
                       dbo.v_GS_OPERATING_SYSTEM.InstallDate0

Answer 1

Most likely, you are having trouble with a precedence problem and your from clause is not being evaluated the way you think.

Try adding parenthesis, like so:

FROM
    ((dbo.v_GS_COMPUTER_SYSTEM
      INNER JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP
         ON (dbo.v_GS_COMPUTER_SYSTEM.ResourceID =
             dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID)
     )
    INNER JOIN dbo.v_GS_OPERATING_SYSTEM
    )
    INNER JOIN dbo.v_R_System ON (dbo.v_GS_OPERATING_SYSTEM.ResourceID =
                                  dbo.v_R_System.ResourceID)

If that is not what you actually want it to do, you see the problem.

As a note, though it isn't strictly necessary and might be taken care of by the query execution planner, you might be able to create smaller temporary tables if you use a subquery to take care of the condition in your WHERE clause before you start doing joins.

I'm also not entirely certain your CASE syntax is correct. I believe it should be

CASE WHEN
    (dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL
     or dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')
  THEN 'Unknown'
  ELSE dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
END AS [Top Console User]

but, I have been known to be wrong. The error I would expect from this would be something to the effect of "Unknown field [Top Console User]".

Answer 2

SCCM Report Name: Top Console User/General Computer Info on Workstations

Explanation:

This is what I did to fix my initial code that was erroring out for me above so I was able to pull info regarding the top console user as well as some general computer info like manufacturer and model. By the way, the top console user leverages SCCM Asset Intelligence (when it is enabled that is) and requires that the "Audit account logon events" policy is be enabled as well in your environment, which can be done via a GPO. You can read more about enabling this policy here on Microsoft TechNet. Also, I was having issues with this report not showing all the NULL values, but then I played with my Joins and realized that I needed one Left Join with two Right Joins in order for all my data to display for all the workstations in my environment. So, the report below will do two things:

• It will show which systems have the top console user if the Security Log is being audited for successful logons (which again can be done via the GPO already mentioned) or manually per system

and

• It will also show you all the NULL values (in this case the 'Audit Account Logon Events Not Enabled' since this is what I am telling the report to replace the NULL values with) which obviously will tell you which systems dont have the Security Log being audited.

Note:

Some systems may report back the "Top Console User" even if you DO NOT have the "Audit Account Logon Events" enabled via a GPO only if for some reason the Security Log was manually configured to be audited on that particular system by someone, like a Super User, or a local Administrator, etc. So, don't freak out if you see that some systems will report back this info, but the majority of them do not, because this is more than likely the reason why.

SQL Code:

Select Distinct

dbo.v_GS_COMPUTER_SYSTEM.Name0 AS [Computer Name],
                   [Top Console User] = CASE WHEN 
                        (dbo.v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0) is NULL  
                         THEN 'Audit Account Logon Events Not Enabled' 
                         ELSE dbo.v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 
                         END, 
                    dbo.v_R_System.Resource_Domain_OR_Workgr0 AS [Domain Name], 
                    dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], 
                    dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack],
                    dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Computer Manufacturer],
                    dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Computer Model] 

From

dbo.v_R_System 
Left Join
dbo.v_GS_SYSTEM_CONSOLE_USAGE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_CONSOLE_USAGE.ResourceID
Right Join 
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM_CONSOLE_USAGE.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
Right Join
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID 

Where

(Not (dbo.v_GS_OPERATING_SYSTEM.Caption0 Like '%Server%'))

Group By

dbo.v_GS_COMPUTER_SYSTEM.Name0, 
dbo.v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0,
dbo.v_R_System.Resource_Domain_OR_Workgr0, 
dbo.v_GS_OPERATING_SYSTEM.Caption0,
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0,                     
dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0,
dbo.v_GS_COMPUTER_SYSTEM.Model0

Order By

[Top Console User]

SCCM Report Name: Last Logged On User/SCCM Client Check-In on Workstations

Explanation:

Now here is the code I finally needed to use after I spoke with the Asset Management Department and they told me that they did not actually need the Top Console User, but rather the Last Logged On User. They did not understand the difference between the two, so I had to explain that to them, then afterward, they did an "Oh, we get it now. No, we need the Last Logged On User instead as well as the last time a workstation has checked in or online, thanks." So, then I worked on this code that they were able to use. This code does not leverage SCCM Asset Intelligence by the way and no Security Log auditing needs to be enabled for this to work. Also, in order to get the last time a workstation "Checked-In", I just used the Last Hardware Inventory Scan to pull this info. Another thing, to get the true last logged on user from the "dbo.v_GS_COMPUTER_SYSTEM.UserName0", Hardware Inventory Scanning should be enabled every day, which is how I have it configured in my SCCM environment, so that's why I use it. However, if you do not have Hardware Inventory enabled to run on a daily basis, then you should get the last logged on user data from the "V_GS_NETWORK_LOGIN_PROFILE.Name0" instead.

SQL Code:

Select Distinct

dbo.v_GS_COMPUTER_SYSTEM.Name0 AS [Computer Name],
dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Computer Manufacturer],
dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Computer Model],
    [Last Logged On User] = CASE WHEN 
                MAX(dbo.v_GS_COMPUTER_SYSTEM.UserName0) is NULL  
                        THEN 'Uknown' 
                        ELSE dbo.v_GS_COMPUTER_SYSTEM.UserName0 
                        END, 
dbo.v_R_SYSTEM.Resource_Domain_OR_Workgr0 AS [Domain], 
dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], 
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack],
dbo.v_GS_WORKSTATION_STATUS.LastHWScan AS [Last SCCM Client Check-In] 

From

dbo.v_GS_COMPUTER_SYSTEM 
INNER JOIN
dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
INNER JOIN
dbo.v_GS_WORKSTATION_STATUS ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_GS_WORKSTATION_STATUS.ResourceID

Where

dbo.v_GS_OPERATING_SYSTEM.Caption0 Not Like '%Server%' and dbo.v_R_SYSTEM.Name0 Not Like 'WN%' 

Group By

dbo.v_GS_COMPUTER_SYSTEM.Name0, 
dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0,
dbo.v_GS_COMPUTER_SYSTEM.Model0,
dbo.v_GS_COMPUTER_SYSTEM.UserName0, 
dbo.v_R_System.Resource_Domain_OR_Workgr0, 
dbo.v_GS_OPERATING_SYSTEM.Caption0, 
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0,
dbo.v_GS_WORKSTATION_STATUS.LastHWScan

Order By

[Computer Name]