Report to pull serial numbers of Cisco switches in stack

CiscoExpert

I have a current report but it doesn't display switches in stack and want to incorporate serial numbers of switches (in stack) onto the same report

could you please tell me what needs adding?

SELECT OrionNodes.Caption
     , OrionNodes.DetailsUrl
     , OrionNodes.STATUS
     , OrionNodes.ChildStatus
     , NcmEntityPhysical.EntityName
     , OrionNodes.IP_Address
     , NcmEntityPhysical.EntityDescription
     , NcmEntityPhysical.Serial
     , NcmEntityPhysical.Manufacturer
     , NcmEntityPhysical.Model
     , NcmNodeProperties.NodeComments
     , OrionNodes.SYSNAME
     , OrionNodes.NodeDescription
     , OrionNodes.Location
     , OrionNodes.Contact
     , OrionNodes.MachineType
     , OrionNodes.LastBoot
     , OrionNodes.IOSVersion
FROM NCM.NodeProperties AS NcmNodeProperties
INNER JOIN Orion.Nodes AS OrionNodes
     ON NcmNodeProperties.CoreNodeID = OrionNodes.NodeID
LEFT JOIN NCM.EntityPhysical AS NcmEntityPhysical
     ON NcmEntityPhysical.NodeID = NcmNodeProperties.NodeID
          AND NcmEntityPhysical.EntityClass LIKE '%(3)%'
WHERE (
          OrionNodes.Vendor = 'Cisco'
          OR OrionNodes.Vendor = 'PANetworks'
          )
     AND OrionNodes.CustomProperties.ZL_Location <> 'Cisco_Inventory'

I see this one works by giving me the serial numbers of switches in stack but how can I incorporate this on the above report?

SELECT ID
     , NodeID
     , MemberID
     , ROLE
     , RoleDescription
     , STATE
     , StateDescription
     , MacAddress
     , Model
     , SoftwareImage
     , SerialNumber
     , HwPriority
     , SwPriority
     , SwitchNumber
     , PowerAvailable
     , PowerUsed
     , PostResult
     , DisplayName
FROM Orion.NPM.SwitchStackMember

Thanks,

KMSigma.SWI

There's already a report I wrote a whole ago for this in the Content Exchange.