Hi,

I'm wondering how I would add the username and full name to the 'Software license Compliance Complete report' I've copied the report sql below but I can't figure out how and where to include the user name in the code. Any help would be awesome.

SELECT ASSET.NAME AS NAME,
COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) AS INSTALLED,
ASSET_DATA_7.FIELD_1 AS OWNED,
ASSET_DATA_7.FIELD_1 - COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) AS REMAINING,
ASSET_DATA_7.FIELD_9 AS PURCHASE_ORDER_NUM,
MID(GROUP_CONCAT(DISTINCT A3.NAME SEPARATOR '\n'), 1, LENGTH(GROUP_CONCAT(DISTINCT A3.NAME SEPARATOR '\n'))) AS MACHINES,
MID(GROUP_CONCAT(DISTINCT A2.NAME SEPARATOR '\n'), 1, LENGTH(GROUP_CONCAT(DISTINCT A2.NAME SEPARATOR '\n'))) AS SOFTWARES,
ASSET_DATA_7.FIELD_11 AS NOTES
FROM ASSET_DATA_7
LEFT JOIN ASSET ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=7
LEFT JOIN ASSET_ASSOCIATION J3 ON J3.ASSET_ID = ASSET.ID AND J3.ASSET_FIELD_ID=3
LEFT JOIN ASSET A3 ON A3.ID = J3.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2
LEFT JOIN ASSET A2 ON A2.ID = J2.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION JX2 ON JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2
LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID
LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT
ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID))
GROUP BY ASSET_DATA_7.ID
ORDER BY REMAINING,NAME asc
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Are you after the username that was logged into the machines? If so then you want it as a comma list like the machines? A mockup example of desired output would be great here.
Answered 01/16/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Sorry I copied the wrong report, I just want to add the username to the unapproved software installation report. So my report would look the same as that one just with the username and full name added beside the computer name. Here's the sql code, I'm just not sure where to put the username or full name?

select ASSET.NAME,
A21.NAME AS VENDOR,
GROUP_CONCAT(DISTINCT A2.NAME SEPARATOR '\n') AS LICENSES,
COUNT(DISTINCT J3.ASSOCIATED_ASSET_ID) AS APPROVED_COUNT,
COUNT(DISTINCT MACHINE.ID) AS UNAPPROVED_COUNT,
GROUP_CONCAT(DISTINCT MACHINE.NAME SEPARATOR '\n') AS MACHINES
from ASSET
LEFT JOIN ASSET_ASSOCIATION J21 ON J21.ASSET_ID = ASSET.ID AND J21.ASSET_FIELD_ID=21
LEFT JOIN ASSET A21 ON A21.ID = J21.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSOCIATED_ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2
LEFT JOIN ASSET A2 ON A2.ID = J2.ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J3 ON
J3.ASSET_ID = A2.ID
AND J3.ASSET_FIELD_ID = 3
LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = ASSET.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT
ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = ASSET.ASSET_DATA_ID))
LEFT JOIN MACHINE on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
where (ASSET.ASSET_TYPE_ID = 6)
AND NOT EXISTS (SELECT 1 FROM ASSET C, ASSET_ASSOCIATION J3, ASSET_ASSOCIATION J2
WHERE C.ASSET_TYPE_ID=5 AND C.MAPPED_ID=MACHINE.ID
AND J3.ASSET_ID=J2.ASSET_ID
AND J3.ASSOCIATED_ASSET_ID = C.ID
AND J3.ASSET_FIELD_ID = 3
AND J2.ASSOCIATED_ASSET_ID = ASSET.ID
AND J2.ASSET_FIELD_ID = 2)
GROUP BY ASSET.ID
HAVING LICENSES IS NOT NULL
order by ASSET.NAME
Answered 01/16/2012 by: ms01ak
Tenth Degree Black Belt

Please log in to comment
0
In this report the machines are presented as command separated lists beside the given software asset. How do you want to match up a user to a list of machines? Could you provide a mock-up as a screenshot?
Answered 01/16/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Here you go (sorry I had to remove most of the information) but I just want the username and fullname right after the machine name

Thanks a lot,

Mike

Attachment

Answered 01/16/2012 by: ms01ak
Tenth Degree Black Belt

Please log in to comment
0
To avoid assumptions about the data: the only way to keep it in this one software asset per line format is to concat the user information into the machines record:

select ASSET.NAME,
A21.NAME AS VENDOR,
GROUP_CONCAT(DISTINCT A2.NAME SEPARATOR '\n') AS LICENSES,
COUNT(DISTINCT J3.ASSOCIATED_ASSET_ID) AS APPROVED_COUNT,
COUNT(DISTINCT MACHINE.ID) AS UNAPPROVED_COUNT,
GROUP_CONCAT(DISTINCT CONCAT(MACHINE.NAME,' (',MACHINE.USER,' - ',MACHINE.USER_FULLNAME,')') ORDER BY MACHINE.NAME SEPARATOR '\n') AS MACHINES
from ASSET
LEFT JOIN ASSET_ASSOCIATION J21 ON J21.ASSET_ID = ASSET.ID AND J21.ASSET_FIELD_ID=21
LEFT JOIN ASSET A21 ON A21.ID = J21.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSOCIATED_ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2
LEFT JOIN ASSET A2 ON A2.ID = J2.ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J3 ON
J3.ASSET_ID = A2.ID
AND J3.ASSET_FIELD_ID = 3
LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = ASSET.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT
ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = ASSET.ASSET_DATA_ID))
LEFT JOIN MACHINE on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
where (ASSET.ASSET_TYPE_ID = 6)
AND NOT EXISTS (SELECT 1 FROM ASSET C, ASSET_ASSOCIATION J3, ASSET_ASSOCIATION J2
WHERE C.ASSET_TYPE_ID=5 AND C.MAPPED_ID=MACHINE.ID
AND J3.ASSET_ID=J2.ASSET_ID
AND J3.ASSOCIATED_ASSET_ID = C.ID
AND J3.ASSET_FIELD_ID = 3
AND J2.ASSOCIATED_ASSET_ID = ASSET.ID
AND J2.ASSET_FIELD_ID = 2)
GROUP BY ASSET.ID
HAVING LICENSES IS NOT NULL
order by ASSET.NAME


If you want to separate them, it is possible to have a software line for each unique machine-user-username 3 column combo. E.g. This means you'll see "advanced server" listed n times.
Answered 01/16/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity