Anyone have a report or assist with a report that shows the date of when a piece of software showed up in someone inventory?

Similar to the installed on section in add/remove.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

4

 In 5.4.

SELECT NAME, TIME, VALUE1 as Software, VALUE2 as Version

FROM ASSET_HISTORY A

WHERE FIELD_NAME = 'SOFTWARE'

AND CHANGE_TYPE = 'DETECTED'

ORDER BY NAME, Software
Answered 04/19/2013 by: dugullett
Red Belt

  • Not working, in MYSQL Workbench or KACE. I need the report to show machine name etc of when software was detected.

    mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Software LIMIT 0' at line 5] in EXECUTE(
    "SELECT NAME, TIME, VALUE1 as Software, VALUE2 as Version
    FROM ASSET_HISTORY A
    WHERE FIELD_NAME = 'SOFTWARE'
    AND CHANGE_TYPE = 'DETECTED'
    ORDER BY NAME Software LIMIT 0")
    • You are missing a , on the ORDER BY portion.

      Should be:
      ORDER BY NAME, Software
  • I just now realized you said in 5.4

    I am on version 5.3 currently.
    • I tested this on my 5.3 box. Try this and let me know.

      SELECT A.NAME, AH.TIME, AH.DESCRIPTION
      FROM ASSET_HISTORY AH
      LEFT JOIN ASSET A ON A.ID=AH.ASSET_ID
      WHERE AH.DESCRIPTION LIKE 'FOUND SOFTWARE%'
      ORDER BY A.NAME

      You might want to start by limiting the results. It returns a lot.
      • You can also add "AND TIME > DATE_SUB(NOW(), INTERVAL 5 DAY)" to limit the results to changes made in the last few days.
      • Personally I would update. It's a lot cleaner.
  • We are looking to update, have to go through change management process, testing etc.

    I am needing more of:

    MACHINE_NAME and I plug in the name of a piece of software %FLASH% and it tells me when it was installed/detected for all MACHINEs
    • Yeah I love change management. Try this out. Change Flash, and <MACHINE_NAME> to match what you need.

      SELECT DISTINCT A.NAME, AH.TIME, AH.DESCRIPTION
      FROM ASSET_HISTORY AH
      LEFT JOIN ASSET A ON A.ID=AH.ASSET_ID
      WHERE AH.DESCRIPTION LIKE 'FOUND SOFTWARE%' AND
      AH.DESCRIPTION LIKE '%FLASH%'
      AND TIME > DATE_SUB(NOW(), INTERVAL 5 DAY)
      AND A.NAME LIKE '%<MACHINE_NAME>%'
      ORDER BY A.NAME
      • Getting closer.

        Instead of plugging in machine name, use machine table and list NAME next to the software so we have the name of software + machine name for all of FLASH and when it was installed.
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment
Answer this question or Comment on this question for clarity