SCCM Report for computers with service status (started,stopped,disabled)

select Distinct
CS.Name0,
SER.Displayname0,
SER.Started0,
SER.StartMode0,
SER.State0
from
dbo.v_GS_COMPUTER_SYSTEM CS,
dbo.v_GS_SERVICE SER,
dbo.v_FullCollectionMembership FCM
where
CS.ResourceId = SER.ResourceID
and CS.ResourceId = FCM.ResourceID
and SER.displayname0 like ‘%firewall%’
and (SER.State0 != ‘Running’ or Isnull(SER.State0,”)=”)
and FCM.CollectionID = ‘SMS00001′

9 Responses to "SCCM Report for computers with service status (started,stopped,disabled)"

  1. SELECT DISTINCT
    CS.Name0,
    SER.Displayname0,
    SER.Started0,
    SER.StartMode0,
    SER.State0
    FROM
    dbo.v_GS_COMPUTER_SYSTEM CS,
    dbo.v_GS_SERVICE SER,
    dbo.v_FullCollectionMembership FCM
    WHERE
    CS.ResourceId = SER.ResourceID
    AND CS.ResourceId = FCM.ResourceID
    AND SER.displayname0 LIKE '%Service%'
    AND (SER.State0 != 'Running' OR ISNULL(SER.State0,'')='')
    AND FCM.CollectionID = 'SMS00001';

    Reply
  2. Hello,

    I have an issue with the syntax in

    Isnull(SER.State0,”)=”) or Isnull(SER.State0,’)=’)

    the double quotes or single quotes did not work…
    What could be the correct syntax?

    Thanks,
    Dom

    Reply
    1. SELECT DISTINCT
      CS.Name0,
      SER.Displayname0,
      SER.Started0,
      SER.StartMode0,
      SER.State0
      FROM
      dbo.v_GS_COMPUTER_SYSTEM CS,
      dbo.v_GS_SERVICE SER,
      dbo.v_FullCollectionMembership FCM
      WHERE
      CS.ResourceId = SER.ResourceID
      AND CS.ResourceId = FCM.ResourceID
      AND SER.displayname0 LIKE '%Service%'
      AND (SER.State0 != 'Running' OR ISNULL(SER.State0,'')='')
      AND FCM.CollectionID = 'SMS00001';

      Reply

Leave a Reply