In re-reading your original post there is one other variation... The your original query uses an INNER JOIN the sample query uses an LEFT JOIN - you might try that as well.
From: [email protected] [mailto:[email protected]] On Behalf Of Thompson, Steve R Sent: Tuesday, August 27, 2013 8:46 AM To: [email protected] Subject: [mssms] RE: CM 2012 SSRS Reporting Anomaly Hi Andrew, Thanks for that... The reason you are seeing different counts is the way that query is constructed. >From the query you posted: - the v_CH_ClientSummary view uses this as an overall filter: ISNULL(Obsolete0, 0) = 0 AND ISNULL(Decommissioned0, 0) = 0 AND Client0 = 1 Note: client is not obsolete, or decommissioned and a client. - and in order to derive ClientActiveStatus, it uses this examination: CASE WHEN IsActiveDDR = 1 OR IsActiveHW = 1 OR IsActiveSW = 1 OR IsActiveHW = 1 OR IsActivePolicyRequest = 1 OR IsActiveStatusMessages = 1 THEN 1 ELSE 0 END) AS ClientActiveStatus Note: Which means that if any one of those checks is positive, they determine the client is active. Another way to state this, if none of those checks are positive, then the client is determined as non active. This is important because of that filter below: cs.ClientActiveStatus = 0 Does that help? Steve From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: Tuesday, August 27, 2013 6:40 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: CM 2012 SSRS Reporting Anomaly Steve, Sorry for the delay in responding. It's the stock "Inactive Clients Details" report in CM2012. Syntax for that is below: SELECT coll.Name AS NetBiosName, cs.LastMPServerName, cs.LastOnline, coll.SiteCode, cs.LastHealthEvaluation, (N'CH_LastEvaluationHealthy' + CAST(cs.LastEvaluationHealthy AS NCHAR(1))) AS LastResult, (N'CH_ClientState' + CAST(cs.ClientState AS NCHAR(1))) AS ClientStateDescription FROM v_FullCollectionMembership coll LEFT OUTER JOIN v_CH_ClientSummary cs ON coll.ResourceID = cs.ResourceID WHERE coll.CollectionID = @CollID AND cs.ClientActiveStatus = 0 Regards, Andrew From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: Friday, August 23, 2013 3:24 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: CM 2012 SSRS Reporting Anomaly Do you have a copy of the query from the report you mention? From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: Friday, August 23, 2013 12:00 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: CM 2012 SSRS Reporting Anomaly Made the change and it appears it picked up a few systems, but still off from the default report. Current SQL syntax is below: SELECT DISTINCT v_R_System.Name0 ,v_R_System.description0 ,v_R_System.User_Name0 ,v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 ,v_GS_OPERATING_SYSTEM.Caption0 ,v_GS_COMPUTER_SYSTEM.Model0 ,v_GS_COMPUTER_SYSTEM.SystemType0 ,v_R_System.Last_Logon_Timestamp0 ,v_GS_WORKSTATION_STATUS.LastHWScan ,v_CH_ClientSummary.ClientStateDescription ,v_CH_ClientSummary.LastHealthEvaluation FROM v_FullCollectionMembership INNER JOIN v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM ON v_FullCollectionMembership.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN v_GS_COMPUTER_SYSTEM ON v_FullCollectionMembership.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN v_GS_SYSTEM_CONSOLE_USAGE ON v_FullCollectionMembership.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID INNER JOIN v_GS_WORKSTATION_STATUS ON v_FullCollectionMembership.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN v_CH_ClientSummary ON v_FullCollectionMembership.ResourceID = v_CH_ClientSummary.ResourceID WHERE ( v_FullCollectionMembership.CollectionID = @CollID AND v_CH_ClientSummary.ClientActiveStatus = 0 ) OR ISNULL ( v_R_System.Obsolete0, 0 ) = 1 ORDER BY v_R_System.Name0 From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: Friday, August 23, 2013 11:45 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: CM 2012 SSRS Reporting Anomaly Try changing this one: OR ISNULL(v_R_System.Obsolete0, 0) = 1 From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: Friday, August 23, 2013 8:59 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: CM 2012 SSRS Reporting Anomaly Thanks for the reply! When I replace the query WHERE statement with the one below, it returns all records... ? From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: Friday, August 23, 2013 9:32 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: CM 2012 SSRS Reporting Anomaly Could be related to the Obsolete0 flag... for example, in order validate that you have active clients, you'd want to use something like this: SELECT * FROM v_R_System s WHERE ISNULL(s.Obsolete0, 0) < > 1 AND s.Client0 = 1 If you add a condition to your query to include WHERE ( v_FullCollectionMembership.CollectionID = @CollID AND v_CH_ClientSummary.ClientActiveStatus = 0 ) OR ISNULL(v_R_System.Obsolete0, 0) = 0 Does that change the count? From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: Friday, August 23, 2013 6:14 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] CM 2012 SSRS Reporting Anomaly Hello All, I've been working on a custom report that is based on the "Inactive Clients Details" report included in CM 2012, but includes additional info. In looking at the default report, it seems to be getting this data by returning all systems where v_CH_ClientSummary.ClientActiveStatus = 0. But when I write a custom report based on the same, I'm returning a smaller number of systems than the default report (~20% less). Why would this be? For anyone interested, I've included the SQL statement for my custom report below: SELECT DISTINCT v_R_System.Name0 ,v_R_System.description0 ,v_R_System.User_Name0 ,v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 ,v_GS_OPERATING_SYSTEM.Caption0 ,v_GS_COMPUTER_SYSTEM.Model0 ,v_GS_COMPUTER_SYSTEM.SystemType0 ,v_R_System.Last_Logon_Timestamp0 ,v_GS_WORKSTATION_STATUS.LastHWScan ,v_CH_ClientSummary.ClientStateDescription ,v_CH_ClientSummary.LastHealthEvaluation FROM v_FullCollectionMembership INNER JOIN v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM ON v_FullCollectionMembership.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN v_GS_COMPUTER_SYSTEM ON v_FullCollectionMembership.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN v_GS_SYSTEM_CONSOLE_USAGE ON v_FullCollectionMembership.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID INNER JOIN v_GS_WORKSTATION_STATUS ON v_FullCollectionMembership.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN v_CH_ClientSummary ON v_FullCollectionMembership.ResourceID = v_CH_ClientSummary.ResourceID WHERE v_FullCollectionMembership.CollectionID = @CollID AND v_CH_ClientSummary.ClientActiveStatus = 0 ORDER BY v_R_System.Name0 Thanks in advance, Andrew ________________________________ This e-mail is intended only for the person or entity to whom it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. If you are not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are notified that any disclosure, copying, distribution, or the taking of any action in reliance on the contents of this message is prohibited. If you have received this e-mail in error, please contact the sender immediately and delete the original message and all copies from your system. Statements and representations made in this message are not necessarily that of the Company. ________________________________ This e-mail is intended only for the person or entity to whom it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. If you are not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are notified that any disclosure, copying, distribution, or the taking of any action in reliance on the contents of this message is prohibited. If you have received this e-mail in error, please contact the sender immediately and delete the original message and all copies from your system. Statements and representations made in this message are not necessarily that of the Company. ________________________________ This e-mail is intended only for the person or entity to whom it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. If you are not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are notified that any disclosure, copying, distribution, or the taking of any action in reliance on the contents of this message is prohibited. If you have received this e-mail in error, please contact the sender immediately and delete the original message and all copies from your system. Statements and representations made in this message are not necessarily that of the Company. ________________________________ This e-mail is intended only for the person or entity to whom it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. If you are not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are notified that any disclosure, copying, distribution, or the taking of any action in reliance on the contents of this message is prohibited. If you have received this e-mail in error, please contact the sender immediately and delete the original message and all copies from your system. Statements and representations made in this message are not necessarily that of the Company.
