I know for sure that it have been part of default values within SMS 2.0 and above. And I'm willing to bet it was there in SMS 1.0 too. Check out v_gs_system.systemrole0
From: [email protected] [mailto:[email protected]] On Behalf Of Marcum, John Sent: Friday, October 31, 2014 2:15 PM To: [email protected] Subject: [mssms] RE: Need help with the case statement in a report query If you are using BDNA this is in the view by default for you. :-) [cid:[email protected]] From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Linkey, Mike Sent: Friday, October 31, 2014 1:03 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Need help with the case statement in a report query 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]> [mailto:[email protected]] On Behalf Of Atkinson, Matt Sent: Friday, October 31, 2014 12:53 PM To: [email protected]<mailto:[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. ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer. ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer.
