I don't have all of your values, but try this. I only put in XP and Windows 7,
but you get the idea.
SELECT SMS_R_System.ItemKey, SMS_R_System.Netbios_Name0,
SMS_R_System.Operating_System_Name_and0, SMS_R_System.Full_Domain_Name0,
SMS_G_System_CH_ClientSummary.LastPolicyRequest AS
'Last Policy Check',
CASE (dbo.v_GS_OPERATING_SYSTEM.Caption0)
WHEN 'Microsoft Windows XP Professional' THEN
'Workstation'
WHEN 'Microsoft Windows 7 Enterprise' THEN
'Workstation'
ELSE 'Server'
END as 'role'
FROM dbo.vSMS_R_System AS SMS_R_System INNER JOIN
dbo.v_CH_ClientSummary AS
SMS_G_System_CH_ClientSummary ON SMS_G_System_CH_ClientSummary.ResourceID =
SMS_R_System.ItemKey INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON
SMS_G_System_CH_ClientSummary.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
where SMS_R_System.Full_Domain_Name0 LIKE @domain AND
dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE @OS
AND SMS_R_System.Netbios_Name0 LIKE @hostname AND
SMS_R_System.Operating_System_Name_and0 LIKE @ServerWorkstations
From: [email protected] [mailto:[email protected]] On
Behalf Of Atkinson, Matt
Sent: Friday, October 31, 2014 12:53 PM
To: [email protected]
Subject: [mssms] Need help with the case statement in a report query
Hi All,
I've built this report query, but I think I need to use a CASE statement to
change the displayed value in the last column to just read server or
workstation based on the OS. Everything I've tried so far has just given me a
syntax error.
SELECT SMS_R_System.ItemKey, SMS_R_System.Netbios_Name0,
SMS_R_System.Operating_System_Name_and0, SMS_R_System.Full_Domain_Name0,
SMS_G_System_CH_ClientSummary.LastPolicyRequest AS
'Last Policy Check', dbo.v_GS_OPERATING_SYSTEM.Caption0
FROM dbo.vSMS_R_System AS SMS_R_System INNER JOIN
dbo.v_CH_ClientSummary AS
SMS_G_System_CH_ClientSummary ON SMS_G_System_CH_ClientSummary.ResourceID =
SMS_R_System.ItemKey INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON
SMS_G_System_CH_ClientSummary.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
where SMS_R_System.Full_Domain_Name0 LIKE @domain AND
dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE @OS
AND SMS_R_System.Netbios_Name0 LIKE @hostname AND
SMS_R_System.Operating_System_Name_and0 LIKE @ServerWorkstations
Any suggestions how to structure the query?
Matt Atkinson
Client Systems Engineer
________________________________
This message is intended for the sole use of the addressee, and may contain
information that is privileged, confidential and exempt from disclosure under
applicable law. If you are not the addressee you are hereby notified that you
may not use, copy, disclose, or distribute to anyone the message or any
information contained in the message. If you have received this message in
error, please immediately advise the sender by reply email and delete this
message.
