Need a detailed software report
I am trying to get a software report that provides me the following information.
1. Software title (I actually only need this report for one particular peice of software)
2. Software version (there are several version installed on our network)
3. Workstation software is installed (just the asset name)
4. Requester/technician workstation is assigned to
5. A distinct count of installations. (meaning if I as a tech had 4 computers and this peice of software is installed in each computer, I want it to only count as one count, but also display all the know installations)
6. Grouped by requester/technician
I had one of our DBA's generate a SQL query but it will not run in the query editor. I get the error of "
java.lang.Exception: Only select queries can be executed - "
Below is the SQL query he wrote.
-
WITH WORK_STATIONS(NAME,WORKSTATIONID,WORKSTATION_NAME)
-
AS (SELECT u.FIRST_NAME
-
,ro.RESOURCEID AS WORKSTATIONID
-
,r.RESOURCENAME AS WORKSTATION_NAME
-
FROM dbo.ResourceOwner AS ro
-
INNER JOIN dbo.Resources AS r
-
ON ro.RESOURCEID = r.RESOURCEID
-
INNER JOIN dbo.AaaUser AS u
-
ON ro.USERID = u.[USER_ID]
-
WHERE USERID IS NOT NULL),
-
SOFTWARE(SOFTWARENAME,SOFTWAREVERSION,WORKSTATIONID)
-
AS (SELECT 'SnagIt' AS SOFTWARENAME
-
,SOFTWARENAME
-
,WORKSTATIONID
-
FROM dbo.SoftwareList AS sl
-
INNER JOIN dbo.SoftwareInfo AS si
-
ON sl.SOFTWAREID = si.SOFTWAREID
-
WHERE SOFTWARENAME LIKE '%SnagIt%')
-
SELECT SOFTWARENAME
-
,COUNT(DISTINCT NAME) AS SNAGIT_USERS
-
FROM WORK_STATIONS AS ws
-
INNER JOIN SOFTWARE AS s
-
ON ws.WORKSTATIONID = s.WORKSTATIONID
-
GROUP BY SOFTWARENAME ;