SQL Component Status for two Components on same Node

s-bolyard

Hello,

I'm working on a SQL query and I'm not getting the results that I need.  I'm trying to get the custom table widget to supply the component status for two different component names for the same node.  I'm getting the below results, but it's showing duplicate entries.

SQL Query:

SELECT n.statusled AS 'Status', 
       n.caption   AS 'Device', 
       CASE 
         WHEN Substring(ca.multivaluestatistics, 
              Charindex('A', ca.multivaluestatistics) + 4, 
                     ( ( ( Len(ca.multivaluestatistics) ) - Charindex(':', 
              Reverse(ca.multivaluestatistics)) ) - 
                       Charindex(',', ca.multivaluestatistics) 
              - 5 )) = 'N' THEN '0  minutes' 
         ELSE Concat(Substring(ca.multivaluestatistics, 
                            Charindex(':', ca.multivaluestatistics) + 1, 
                                   ( ( ( Len(ca.multivaluestatistics) ) - 
                                       Charindex(',', 
                     Reverse(ca.multivaluestatistics)) ) - 
                                     Charindex(':', ca.multivaluestatistics) 
                     - 3 )), '  minutes') 
       END         AS Age, 
       CASE 
         WHEN Substring(ca.multivaluestatistics, 
              Charindex('C', ca.multivaluestatistics) + 6, 
                     ( ( ( Len(ca.multivaluestatistics) ) - Charindex('.', 
              Reverse(ca.multivaluestatistics)) ) - 
                       Charindex('C', ca.multivaluestatistics) 
              - 5 )) LIKE 'N%' THEN '0' 
         ELSE Substring(ca.multivaluestatistics, 
              Charindex('C', ca.multivaluestatistics) + 6, 
                     ( ( ( Len(ca.multivaluestatistics) ) - Charindex('.', 
              Reverse(ca.multivaluestatistics)) ) - 
                       Charindex('C', ca.multivaluestatistics) 
              - 5 )) 
       END         AS Count, 
       CASE 
         WHEN Substring(ca.multivaluemessages, 
              Charindex('A', ca.multivaluemessages) + 
              4, ( ( 
                     ( Len(ca.multivaluemessages) ) 
                     - 
                     Charindex(':', Reverse(ca.multivaluemessages)) 
              ) 
              - Charindex('M', ca.multivaluemessages) )) LIKE 'N/A%' THEN 
         '0 files were found in the directory' 
         ELSE Substring(ca.multivaluemessages, 
              Charindex('A', ca.multivaluemessages) + 
              4, ( ( 
                     ( Len(ca.multivaluemessages) ) 
                     - 
                     Charindex(':', Reverse(ca.multivaluemessages)) 
              ) 
              - Charindex('M', ca.multivaluemessages) ) + 100) 
       END         AS Count_Info, 
       n.bca_app_owner, 
       CASE 
         WHEN ad.componentname LIKE 'Custom Windows File Age%' 
              AND ad.componentstatus = 'Down' THEN 'Down.gif' 
         WHEN ad.componentname LIKE 'Custom Windows File Age%' 
              AND ad.componentstatus = 'Critical' THEN 'Critical.gif' 
         WHEN ad.componentname LIKE 'Custom Windows File Age%' 
              AND ad.componentstatus = 'Unreachable' THEN 'Unreachable.gif' 
         WHEN ad.componentname LIKE 'Custom Windows File Age%' 
              AND ad.componentstatus = 'Warning' THEN 'Warning.gif' 
         WHEN ad.componentname LIKE 'Custom Windows File Age%' 
              AND ad.componentstatus = 'Unknown' THEN 'Warning.gif' 
         ELSE 'Up.gif' 
       END         AS 'File Status', 
       ad.componentname, 
       ad.componentstatus, 
       CASE 
         WHEN ad.componentname LIKE '%Epic BCA%' 
              AND ad.componentstatus = 'Down' THEN 'Down.gif' 
         WHEN ad.componentname LIKE '%Epic BCA%' 
              AND ad.componentstatus = 'Critical' THEN 'Critical.gif' 
         WHEN ad.componentname LIKE '%Epic BCA%' 
              AND ad.componentstatus = 'Unreachable' THEN 'Unreachable.gif' 
         WHEN ad.componentname LIKE '%Epic BCA%' 
              AND ad.componentstatus = 'Warning' THEN 'Warning.gif' 
         WHEN ad.componentname LIKE '%Epic BCA%' 
              AND ad.componentstatus = 'Unknown' THEN 'Warning.gif' 
         ELSE 'Up.gif' 
       END         AS 'Service Status', 
       CASE 
         WHEN n.bca_pcs = 'Field Services - DV' THEN 'Deer Valley' 
         WHEN n.bca_pcs = 'Field Services -JCL' THEN 'John C. Lincoln' 
         WHEN n.bca_pcs = 'Field Services - MG' THEN 'Medical Group Office' 
         WHEN n.bca_pcs = 'Field Services - NSSC' THEN 'NSSC' 
         WHEN n.bca_pcs = 'Field Services - OSB' THEN 'Osborn' 
         WHEN n.bca_pcs = 'Field Services - SHEA' THEN 'Shea' 
         WHEN n.bca_pcs = 'Field Services - TPK' THEN 'Thompson Peak' 
       END         AS 'Facility' 
FROM   dbo.apm_component c 
       JOIN dbo.apm_componentalertvariable ca 
         ON ca.componentid = c.id 
       JOIN dbo.apm_application a 
         ON a.id = c.applicationid 
       JOIN dbo.nodes n 
         ON n.nodeid = a.nodeid 
       JOIN dbo.apm_alertsdata ad 
         ON ad.nodeid = n.nodeid 
WHERE  n.caption NOT LIKE 'ARCHIVE-%' 
       AND n.bca_app_owner = 'EMR Ambulatory' 
       AND n.statusled != 'Unmanaged.gif' 
ORDER  BY ( CASE 
              WHEN n.status = 'Down' THEN 1 
              WHEN ad.componentstatus = 'Down' THEN 2 
              WHEN ad.componentstatus = 'Critical' THEN 3 
              WHEN ad.componentstatus = 'Warning' THEN 4 
              WHEN ad.componentstatus = 'Unreachable' THEN 5 
              WHEN ad.componentstatus = 'Unknown' THEN 6 
              WHEN ad.componentstatus = 'Up' THEN 7 
            END ) ASC; 

s-bolyard

I finally figured it out.  The query allows me to display all in one line the ping status, the Windows service status, file age statistic and file count.  Hopefully others will find this useful.  Now I need to figure out to convert it to SWQL. 

Here's the final query:

SELECT n.statusled AS 'Status', 
       n.caption   AS 'Device', 
       CASE 
         WHEN Substring(ca.multivaluestatistics, 
              Charindex('A', ca.multivaluestatistics) + 4, 
                     ( ( ( Len(ca.multivaluestatistics) ) - Charindex(':', 
              Reverse(ca.multivaluestatistics)) ) - 
                       Charindex(',', ca.multivaluestatistics) 
              - 5 )) = 'N' THEN '0  minutes' 
         ELSE Concat(Substring(ca.multivaluestatistics, 
                            Charindex(':', ca.multivaluestatistics) + 1, 
                                   ( ( ( Len(ca.multivaluestatistics) ) - 
                                       Charindex(',', 
                     Reverse(ca.multivaluestatistics)) ) - 
                                     Charindex(':', ca.multivaluestatistics) 
                     - 3 )), '  minutes') 
       END         AS Age, 
       CASE 
         WHEN Substring(ca.multivaluestatistics, 
              Charindex('C', ca.multivaluestatistics) + 6, 
                     ( ( ( Len(ca.multivaluestatistics) ) - Charindex('.', 
              Reverse(ca.multivaluestatistics)) ) - 
                       Charindex('C', ca.multivaluestatistics) 
              - 5 )) LIKE 'N%' THEN '0' 
         ELSE Substring(ca.multivaluestatistics, 
              Charindex('C', ca.multivaluestatistics) + 6, 
                     ( ( ( Len(ca.multivaluestatistics) ) - Charindex('.', 
              Reverse(ca.multivaluestatistics)) ) - 
                       Charindex('C', ca.multivaluestatistics) 
              - 5 )) 
       END         AS Count, 
       CASE 
         WHEN Substring(ca.multivaluemessages, 
              Charindex('A', ca.multivaluemessages) + 
              4, ( ( 
                     ( Len(ca.multivaluemessages) ) 
                     - 
                     Charindex(':', Reverse(ca.multivaluemessages)) 
              ) 
              - Charindex('M', ca.multivaluemessages) )) LIKE 'N/A%' THEN 
         '0 files were found in the directory' 
         ELSE Substring(ca.multivaluemessages, 
              Charindex('A', ca.multivaluemessages) + 
              4, ( ( 
                     ( Len(ca.multivaluemessages) ) 
                     - 
                     Charindex(':', Reverse(ca.multivaluemessages)) 
              ) 
              - Charindex('M', ca.multivaluemessages) ) + 100) 
       END         AS Count_Info, 
       n.bca_app_owner, 
       CASE 
         WHEN fileage.componentstatus = 'Down' THEN 'Down.gif' 
         WHEN fileage.componentstatus = 'Critical' THEN 'Critical.gif' 
         WHEN fileage.componentstatus = 'Unreachable' THEN 'Warning.gif' 
         WHEN fileage.componentstatus = 'Warning' THEN 'Warning.gif' 
         WHEN fileage.componentstatus = 'Unknown' THEN 'Warning.gif' 
         ELSE 'Up.gif' 
       END         AS 'File Status', 
       fileage.componentname, 
       fileage.componentstatus, 
       CASE 
         WHEN epicbca.componentstatus = 'Down' THEN 'Down.gif' 
         WHEN epicbca.componentstatus = 'Critical' THEN 'Critical.gif' 
         WHEN epicbca.componentstatus = 'Unreachable' THEN 'Warning.gif' 
         WHEN epicbca.componentstatus = 'Warning' THEN 'Warning.gif' 
         WHEN epicbca.componentstatus = 'Unknown' THEN 'Warning.gif' 
         ELSE 'Up.gif' 
       END         AS 'Service Status', 
       epicbca.componentname, 
       epicbca.componentstatus, 
       n.primary_location 
FROM   dbo.apm_component c 
       JOIN dbo.apm_componentalertvariable ca 
         ON ca.componentid = c.id 
       JOIN dbo.apm_application a 
         ON a.id = c.applicationid 
       JOIN dbo.nodes n 
         ON n.nodeid = a.nodeid 
       JOIN dbo.apm_alertsdata fileage 
         ON fileage.nodeid = n.nodeid 
            AND fileage.componentname = 
                'Custom Windows File Age and Count Monitor' 
       JOIN dbo.apm_alertsdata epicbca 
         ON epicbca.nodeid = n.nodeid 
            AND epicbca.componentname LIKE '%Epic BCA%' 
WHERE  n.caption NOT LIKE 'ARCHIVE-%' 
       AND n.bca_app_owner = 'EMR Inpatient/Stork OB' 
       AND n.statusled != 'Unmanaged.gif' 
ORDER  BY ( CASE 
              WHEN n.status = 'Down' THEN 1 
              WHEN epicbca.componentstatus = 'Down' THEN 2 
              WHEN epicbca.componentstatus = 'Critical' THEN 3 
              WHEN epicbca.componentstatus = 'Warning' THEN 4 
              WHEN epicbca.componentstatus = 'Unreachable' THEN 5 
              WHEN epicbca.componentstatus = 'Unknown' THEN 6 
              WHEN epicbca.componentstatus = 'Up' THEN 7 
            END ) ASC, 
          ( CASE 
              WHEN n.status = 'Down' THEN 1 
              WHEN fileage.componentstatus = 'Down' THEN 2 
              WHEN fileage.componentstatus = 'Critical' THEN 3 
              WHEN fileage.componentstatus = 'Warning' THEN 4 
              WHEN fileage.componentstatus = 'Unreachable' THEN 5 
              WHEN fileage.componentstatus = 'Unknown' THEN 6 
              WHEN fileage.componentstatus = 'Up' THEN 7 
            END ) ASC;