Need help with Report (Query)
Hi,
Is anyone able to help me out with a query I am trying to run in SDP.
I am look for a Query that would give me :
Scanned Software - Parent Software - Installed Computer - Assigned User - Allocated License - Allocated License Key - Discovered License Key
I have everything I need but I can't populate Parent Software... If you don't know what I mean by parent software, for exemple if you install Office 2007 and the SD Agent scan Office 2007. Office 2007 is currently child of 2013 because we buy 2013 license. The links are all done is SDP from child to parent have all been done.
Here's my current Query :
select slist.SOFTWARENAME 'Software', res.RESOURCENAME 'Installed Computer', aau.FIRST_NAME 'Assigned User' ,lres.RESOURCENAME 'Allocated License', slic.LICENSEKEY 'Allocated License Key',ins.PRODUCTKEY 'Discovered License Key' from SoftwareInfo sinf LEFT JOIN SoftwareList slist ON sinf.SOFTWAREID = slist.SOFTWAREID LEFT JOIN Resources res ON sinf.WORKSTATIONID = res.RESOURCEID LEFT JOIN ResourceOwner reso ON res.RESOURCEID = reso.RESOURCEID LEFT JOIN AaaUser aau ON reso.USERID = aau.USER_ID LEFT JOIN SoftwareType stype ON slist.SOFTWARETYPEID = stype.SOFTWARETYPEID LEFT JOIN SoftwareManufacturer mfg ON slist.SWMANUFACTURERID = mfg.SWMANUFACTURERID LEFT JOIN SWInstallationKeyInfo ins ON sinf.SOFTWAREINFOID = ins.SOFTWAREINFOID LEFT JOIN SoftwareLicenses slic ON sinf.LICENSEID = slic.RESOURCEID LEFT JOIN Resources lres ON slic.RESOURCEID = lres.RESOURCEID LEFT JOIN ResourceState rest ON res.RESOURCESTATEID = rest.RESOURCESTATEID where stype.SOFTWARETYPE = 'Managed' and (res.RESOURCENAME like '%A342785%') and (rest.STATEDESC = 'In Use' or rest.STATEDESC = 'In Store')
Is anyone able to help me out with a query I am trying to run in SDP.
I am look for a Query that would give me :
Scanned Software - Parent Software - Installed Computer - Assigned User - Allocated License - Allocated License Key - Discovered License Key
I have everything I need but I can't populate Parent Software... If you don't know what I mean by parent software, for exemple if you install Office 2007 and the SD Agent scan Office 2007. Office 2007 is currently child of 2013 because we buy 2013 license. The links are all done is SDP from child to parent have all been done.
Here's my current Query :
select slist.SOFTWARENAME 'Software', res.RESOURCENAME 'Installed Computer', aau.FIRST_NAME 'Assigned User' ,lres.RESOURCENAME 'Allocated License', slic.LICENSEKEY 'Allocated License Key',ins.PRODUCTKEY 'Discovered License Key' from SoftwareInfo sinf LEFT JOIN SoftwareList slist ON sinf.SOFTWAREID = slist.SOFTWAREID LEFT JOIN Resources res ON sinf.WORKSTATIONID = res.RESOURCEID LEFT JOIN ResourceOwner reso ON res.RESOURCEID = reso.RESOURCEID LEFT JOIN AaaUser aau ON reso.USERID = aau.USER_ID LEFT JOIN SoftwareType stype ON slist.SOFTWARETYPEID = stype.SOFTWARETYPEID LEFT JOIN SoftwareManufacturer mfg ON slist.SWMANUFACTURERID = mfg.SWMANUFACTURERID LEFT JOIN SWInstallationKeyInfo ins ON sinf.SOFTWAREINFOID = ins.SOFTWAREINFOID LEFT JOIN SoftwareLicenses slic ON sinf.LICENSEID = slic.RESOURCEID LEFT JOIN Resources lres ON slic.RESOURCEID = lres.RESOURCEID LEFT JOIN ResourceState rest ON res.RESOURCESTATEID = rest.RESOURCESTATEID where stype.SOFTWARETYPE = 'Managed' and (res.RESOURCENAME like '%A342785%') and (rest.STATEDESC = 'In Use' or rest.STATEDESC = 'In Store')