Report : relationship between computer and software assets
Hello,
I created a software asset is my software library. example:
Acrobat reader in the category Office (with dynamic label software level)
I would like to create a query that gives me a list of computers with software my software library. But I can not find how to do this.
Example: Computer1 | Acrobat Reader | Office |
But only with my list of my library of software assets.
how to create a query that gives me a list of computers with my list of software present in the asset section.
Thank you for your help.
Answers (4)
I'm not sure 100% what you are asking, but take a look at this. Let me know if it needs to be modified. Change the "Category" and "Display_name" to match what you need.
SELECT M.NAME, S.DISPLAY_NAME, CATEGORY FROM SOFTWARE S LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID) LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID) WHERE CATEGORY = 'OFFICE' AND S.DISPLAY_NAME = 'ACROBAT READER' ORDER BY M.NAME
Thank you very much for your response. But that does not go for the category is not in the inventory asset part.
I am looking for the SQL level that gives me my computer list by software in the active part of the K1000 and not in the inventory part relationship.
I do not know if I am clear in my explanations :)
Comments:
-
I guess I'm still not following. Where is the category coming into play? When you say you created a software asset do you mean you created a new software record under Inventory>Software>Add New Item, or that you created a new an actual new software record?
Do you want a list of machines with the associated software? - dugullett 11 years ago
In Part asset:
I create an entry in software with a dynamic label.
It gives me the number of computers that have this software but I that number.
I would like to have a report that gives me the list of computer names.
Comments:
-
Ok. I misunderstood. Off the top of my head I do not know I way to key off of that label name since it doesn't populate in the Software table. I'll look to see if I can find it somewhere else.
In the meantime could you just do something like this?
SELECT M.NAME, S.DISPLAY_NAME
FROM SOFTWARE S
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID)
LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)
where display_name like '7-Zip %'
ORDER BY S.DISPLAY_NAME, M.NAME - dugullett 11 years ago-
Try this. Change the 2nd to the last line to match your label name.
SELECT DISTINCT M.NAME,
GROUP_CONCAT(DISTINCT S.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE
FROM SOFTWARE S
LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON (MSJT.SOFTWARE_ID = S.ID)
LEFT JOIN MACHINE M ON (M.ID = MSJT.MACHINE_ID)
LEFT JOIN SOFTWARE_LABEL_JT SLJT ON S.ID=SLJT.SOFTWARE_ID
LEFT JOIN LABEL L ON L.ID=SLJT.LABEL_ID
WHERE L.NAME ='<ENTER LABEL NAME HERE>'
GROUP BY M.NAME
ORDER BY M.NAME - dugullett 11 years ago
This is ok but is not good for me :)
When I run this query:
SELECT ASSET.NAME AS ASSET_NAME , (SELECT STRAIGHT_JOIN COUNT(distinct MACHINE_SOFTWARE_JT.MACHINE_ID) FROM SOFTWARE JOIN MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.SOFTWARE_ID=SOFTWARE.ID WHERE (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))) as INSTALLED , (SELECT SUM(ASSET_DATA_7.FIELD_1)
FROM ASSET_ASSOCIATION J22
JOIN ASSET A22 ON A22.ID = J22.ASSET_ID
JOIN ASSET_DATA_7 ON ASSET_DATA_7.ID = A22.ASSET_DATA_ID
WHERE J22.ASSOCIATED_ASSET_ID = ASSET.ID AND J22.ASSET_FIELD_ID=2) AS LICENSES, AD.FIELD_94 as 'Version en prod' , AD.FIELD_91 as 'Categorie', AD.FIELD_96 as 'Version d OS', AD.FIELD_95 as 'Version' FROM ASSET
LEFT JOIN USER U ON U.ID = ASSET.OWNER_ID
LEFT JOIN MACHINE M ON ASSET.MAPPED_ID = M.ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ASSET_DATA_6 AD ON AD.ID = ASSET.ASSET_DATA_ID
where ASSET.ASSET_TYPE_ID = 6
GROUP BY ASSET.ID
ORDER BY ASSET_NAME
i have this :
I get much the number of computers that have this or that software.
So I said that the opposite should be possible to have the list of my computers with the software part of the ASSET (not INVENTORY).
I have 200 in the software part ASSET and 2200 titles in INVENTORY.
Many software does not interest me. that's why I'd like to make the link between asset software and computer name.