Get systems list from SIM database

[Pagnotta]

HI everybody,

I'm wondering how I could get via a SQL query against SIM Database, the list of systems belonging to a particular Systems' list ?


Thanks
Angelo

[David Claypool]

Two suggestions:

The MXQUERY command might have everything you need.

mxquery -e "All Systems"

will print to the screen the results of the "All Systems" query.

Another way to get at this would be to create a report that has the info you're looking for in it, run the report and then click on 'Show SQL' and you'll get the SQL query that the system generated and you can cut and paste that, modifying it as you want. I created a simple report with a few fields for the "All Servers" query and the result is:

select R_Inventory.DeviceName, R_Inventory.IPAddress, R_Inventory.ProductType, R_Inventory.ProductSubType, R_Inventory.ProductName, R_Inventory.SerialNumber from R_Inventory WHERE ((R_Inventory.SnapShotID=-1) OR (R_Inventory.SnapShotID is NULL)) AND DeviceKey in (select devices.deviceKey from devices JOIN notices ON devices.deviceKey = notices.deviceKey and notices.NoticeType = 1 LEFT OUTER JOIN DB_deviceInfoEx ON devices.deviceKey = DB_deviceInfoEx.deviceKey LEFT OUTER JOIN IPAddress ON devices.deviceKey = IPAddress.deviceKey and IPAddress.ipIndex = 0 LEFT OUTER JOIN IPXAddress ON devices.deviceKey = IPXAddress.deviceKey and IPXAddress.ipxIndex = 0 LEFT OUTER JOIN associated_device_data ON devices.deviceKey = associated_device_data.deviceKey LEFT OUTER JOIN deviceProtocolInfo ON devices.deviceKey = deviceProtocolInfo.deviceKey LEFT OUTER JOIN DeviceStatusValue ON devices.deviceKey = DeviceStatusValue.deviceKey and DeviceStatusValue.StatusSourceId= 6 JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '00000000864162de0000000400000001' WHERE ( ( devices.productType = 1) ) ) order by R_Inventory.DeviceName ASC