Jeroen 5 Laps
Hello,

I need a report that inlcudes the following:
Software name 1 : "RSLogix 5000 v"
Software name 2 : "Studio 5000 Logix Designer v"
Asset name
Assigned to user
Serial number harddrive

Group by asset

SD version : 9307
Database : MYSQL

Replies (4)

Hi,

Please use the below query.

SELECT SystemInfo.WORKSTATIONNAME "Workstation",
MAX(aaaUser.FIRST_NAME) "User" , 
MAX(resource.SERIALNO) "Serial number",
(SoftwareList.SOFTWARENAME) "Software_Name",
MAX(SoftwareType.SOFTWARETYPE) "Software_Type" FROM SoftwareInfo 
LEFT JOIN SoftwareList ON SoftwareInfo.SOFTWAREID=SoftwareList.SOFTWAREID 
LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID 
LEFT JOIN Systeminfo ON SoftwareInfo.WORKSTATIONID=SystemInfo.WORKSTATIONID 
LEFT JOIN Resources resource ON systeminfo.WORKSTATIONID=resource.RESOURCEID 
LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID 
LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID 
LEFT JOIN SDUser sdUser ON rOwner.USERID=sdUser.USERID 
LEFT JOIN AaaUser aaaUser ON sdUser.USERID=aaaUser.USER_ID  GROUP BY SystemInfo.WORKSTATIONNAME,SoftwareList.SOFTWARENAME order by 1

Jeroen 5 Laps
Almost, it's not searching for the software names:
Software name 1 : "RSLogix 5000 v"
Software name 2 : "Studio 5000 Logix Designer v"

And it's not fetching the harddrive serial number. The first harddrive serial is enough.
Hi,

Please use the below query.

SELECT SystemInfo.WORKSTATIONNAME "Workstation",
MAX(aaaUser.FIRST_NAME) "User" , 
MAX(resource.SERIALNO) "Serial number",
max(hdi.Serialnumber) "Harddisk Serialnumber",
(SoftwareList.SOFTWARENAME) "Software_Name",
MAX(SoftwareType.SOFTWARETYPE) "Software_Type" FROM SoftwareInfo 
LEFT JOIN SoftwareList ON SoftwareInfo.SOFTWAREID=SoftwareList.SOFTWAREID 
LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID 
LEFT JOIN Systeminfo ON SoftwareInfo.WORKSTATIONID=SystemInfo.WORKSTATIONID 
LEFT JOIN Resources resource ON systeminfo.WORKSTATIONID=resource.RESOURCEID 
LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID 
LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID 
LEFT JOIN SDUser sdUser ON rOwner.USERID=sdUser.USERID 
LEFT JOIN AaaUser aaaUser ON sdUser.USERID=aaaUser.USER_ID 
LEFT JOIN HardDiskInfo hdi ON Systeminfo.WORKSTATIONID= hdi.WORKSTATIONID  where (SoftwareList.SOFTWARENAME like '%RSLogix 5000%'  or SoftwareList.SOFTWARENAME like '%Studio 5000 Logix Designer%'  ) GROUP BY SystemInfo.WORKSTATIONNAME,SoftwareList.SOFTWARENAME order by 1

Jeroen 5 Laps
Thanks that was i was looking for.

EDIT: apparently this is not exactly what the customer ment :( 
The harddrive serial number has to the volume serial number from the C drive. And the volum serial number has to be unique.