That was it! In some cases, not all views matched up. Changing to LEFT OUTER
JOIN on the view joins did the trick.
Thanks for your help Steve!
BTW, for those playing along at home, final SQL statement is below (this will
also report on any obsolete systems as well as any that aren't explicitly
marked active):
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
LEFT OUTER JOIN v_R_System
ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_OPERATING_SYSTEM
ON v_FullCollectionMembership.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM
ON v_FullCollectionMembership.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE
ON v_FullCollectionMembership.ResourceID =
v_GS_SYSTEM_CONSOLE_USAGE.ResourceID
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS
ON v_FullCollectionMembership.ResourceID =
v_GS_WORKSTATION_STATUS.ResourceID
LEFT OUTER JOIN v_CH_ClientSummary
ON v_FullCollectionMembership.ResourceID = v_CH_ClientSummary.ResourceID
WHERE ( v_FullCollectionMembership.CollectionID = @CollID
AND ( v_CH_ClientSummary.ClientActiveStatus != 1 OR
v_R_System.Obsolete0 = 1 )
)
ORDER BY v_R_System.Name0
From: [email protected] [mailto:[email protected]] On
Behalf Of [email protected]
Sent: Tuesday, August 27, 2013 10:56 AM
To: [email protected]
Subject: [mssms] RE: CM 2012 SSRS Reporting Anomaly
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]>
[mailto:[email protected]] On Behalf Of Thompson, Steve R
Sent: Tuesday, August 27, 2013 8:46 AM
To: [email protected]<mailto:[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.
________________________________
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.
