Return detailed user and machine information from a specified collection

Replace {CollectionName} with the name of the collection to query.

Select SD.Name0 'Machine Name', 
SD.Resource_Domain_OR_Workgr0 
'Resource Domain', 
SD.User_Name0 'Login ID', 
SD.User_Domain0 'Account 
Domain', USR.Full_User_Name0 
'Full Name', PCB.SerialNumber0 
'Serial Number', CS.Manufacturer0 
Manufacturer, CS.Model0 Model, 
SAS.SMS_Assigned_Sites0 
'Assigned Site Code' 
From v_R_System SD Join v_FullCollectionMembership
FCM on SD.ResourceID = FCM.ResourceID 
Join v_Collection COL on 
FCM.CollectionID = COL.CollectionID 
Join v_R_User USR on SD.User_Name0 = 
USR.User_Name0 Join v_GS_PC_BIOS PCB 
on SD.ResourceID = PCB.ResourceID 
Join v_GS_COMPUTER_SYSTEM CS on 
SD.ResourceID = CS.ResourceID 
Join v_RA_System_SMSAssignedSites 
SAS on SD.ResourceID = SAS.ResourceID 
Where COL.Name = '{CollectionName}'

Query SQL for rogue printers

SELECT Distinct 
SYS.Netbios_Name0, 
PRT.Name0, PRT.ShareName0, 
PRT.DriverName0, PRT.DeviceID0, 
PRT.PortName0 FROM v_R_System 
SYS JOIN v_GS_PRINTER_DEVICE PRT
on SYS.ResourceID = PRT.ResourceID
WHERE PRT.PortName0 like 'ip%' or 
PRT.PortName0 like 'com%' or 
PRT.PortName0 like 'lpt%' or 
PRT.PortName0 like 'usb%' ORDER 
BY SYS.Netbios_Name0