Try changing this one: OR ISNULL(v_R_System.Obsolete0, 0) = 1
From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Friday, August 23, 2013 8:59 AM To: [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.
