1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
|
SELECT DISTINCT sys.Name0,
CASE WHEN sys.Resource_Domain_OR_Workgr0 =' ' THEN 1 END AS [Not In Domain],
CASE WHEN CS.ClientActiveStatus='0' THEN 1 END AS [Client Active Status],
CASE WHEN sys.client0 is NULL THEN 1 END AS [Client Not Installed],
CASE WHEN sys.Name0 IN (select sys.name0 from v_r_system sys group by sys.name0 having count(sys.name0) >=2) then 1 END AS [En Doublon],
CASE WHEN smsgs.MessageID = '669' THEN 1 END AS [Client Error 669],
CASE WHEN smsgs.MessageID = '10900' THEN 1 END AS [Client Error 10900],
CASE WHEN smsgs.MessageID = '10803' THEN 1 END AS [Client Error 10803],
CASE WHEN smsgs.MessageID = '10601' THEN 1 END AS [Client Error 10601],
CASE WHEN smsgs.MessageID = '10702' THEN 1 END AS [Client Error 10702],
CASE WHEN smsgs.MessageID = '10805' THEN 1 END AS [Client Error 10805],
CASE ER.Result
WHEN 1 THEN 'Not yet evaluated' WHEN 2 THEN 'Not applicable' WHEN 3 THEN 'Evaluation failed'
WHEN 4 THEN 'Evaluated, remediation failed' WHEN 5 THEN 'Not evaluated, dependency failed'
WHEN 6 THEN 'Evaluated, remediation succeeded' WHEN 7 THEN 'Evaluated,succeeded: All tests passed'
ELSE 'Others'END AS[Client Health Description],
CASE WHEN DATEDIFF(dd,WS.LastHWScan,GetDate()) > 5 THEN 1 END AS [HW Scan Date sup 5]
FROM v_R_System sys
JOIN v_StatusMessage smsgs on sys.name0 = smsgs.MachineName
JOIN v_CH_ClientSummary CS on sys.ResourceId=CS.ResourceID
JOIN v_StatMsgWithInsStrings smwis on smsgs.RecordID = smwis.RecordID
JOIN v_StatMsgModuleNames modNames on smsgs.ModuleName = modNames.ModuleName
JOIN v_CH_EvalResults ER on sys.ResourceID = ER.ResourceID
JOIN v_GS_WORKSTATION_STATUS WS on sys.ResourceID = WS.ResourceID
WHERE smsgs.MessageID IN(669,10900,10803,10601,10702)
AND DATEDIFF(dd,sys.Last_Logon_Timestamp0,getdate())<60
AND sys.Name0 like '%' + @prmCoputerName + '%'
AND DATEDIFF(dd,smsgs.Time,GetDate()) < 30
ORDER BY Name0 ASC |
Partager