ITNinja

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.

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

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?

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

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.