I need to create a report that lists the version of Microsoft Office installed on each computer in my inventory as well as Microsoft Office's install date. I've kind of been able to gather what needs to be done to achieve this in regards to creating a custom inventory rule that reads the install date from the registry from some of the other questioned asked here, but I'm encountering a few issues:

If I already know that we only have Microsoft Professional Plus 2007/2010/2013 on our machines, is it possible to reference retrieving their install dates in a single custom rule, or do I have to make a custom rule for each version?

How do I apply a custom rule to my machines? 

How can I call information from a custom rule within a SQL Report Query?

I have been testing this Custom Inventory Rule to try to get Office Professional Plus install dates:

RegistryValueReturn(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{90140000-0116-0409-1000-0000000FF1CE}, InstallDate, NUMBER)

4 Comments   [ + ] Show Comments

Comments

  • Chucksteel I treid your query but I ge the following SQL eror
    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_ID = 2777 LIMIT 0' at line 1] in EXECUTE(
    "SELECT MACHINE_CUSTOM_INVENTORY.*, MACHINE.NAME FROM ORG1.MACHINE_CUSTOM_INVENTORY JOIN MACHINE on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.IDWHERE SOFTWARE_ID = 2777 LIMIT 0")

    Any ideas?
    • There should be a space between MACHINE_CUSTOM_INVENTORY.ID and WHERE in the last line that you pasted. I don't know if it's just not being displayed or if there actually isn't one there. Also, putting a LIMIT 0 on the end might be a problem.
    • I've changed a bit around. My current Custom Inventory Rule is:

      RegistryValueReturn(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{90140000-0011-0000-0000-0000000FF1CE}, InstallDate, NUMBER)

      And this is the report I run for it:

      SELECT MACHINE_CUSTOM_INVENTORY.*, MACHINE.NAME
      FROM ORG1.MACHINE_CUSTOM_INVENTORY JOIN MACHINE on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID
      WHERE SOFTWARE_ID = 15674;

      The software ID of course matching that of the custom inventory rule. I don't know off the top of my head why your query didn't work for you.
      • This is the inventory rule I am using:
        FileInfoReturn(C:\Desktop\_util\Printer Driver\Samsung M 4020\Driver\LICENSE.txt, ModifiedDate, DATE)

        and my software ID is 2777
    • This content is currently hidden from public view.
      Reason: Member has been banned from the site
      For more information, visit our FAQ's.
  • My query looks like this

    SELECT MACHINE_CUSTOM_INVENTORY.*, MACHINE.NAME FROM ORG1.MACHINE_CUSTOM_INVENTORY JOIN MACHINE on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID WHERE SOFTWARE_ID = 2777;

    The first one I posted was the SQL error I received when trying to run this query.
    • Does the software ID (2777) match that of your custom rule?
  • Yes it do match but as far as I know even if the id is incorrect the query should still work. It is just that there will be nothing in your report when you run it ?
    • That sounds right to me, but I'm still about as lost as you are. I still don't even know how I got mine somewhat working, and it still isn't working the way I want it to.
  • Lets wait maybe the pro's can assist with this one
    • Do you have multiple organizations? I only have one org on my KBOX so I never have to worry about the separate tables but if you have multiple orgs the query will have to be adjusted.
      • No, we only have one organization.
      • Are you using a tool like MySQL Workbench to run your queries or just putting them into the reporting module? Using a tool like MySQL Workbench makes trouble shooting syntax errors much easier.
  • 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

Answers

0

Create a custom software inventory item and use your custom inventory rule in it.  You can then report on the software title in your custom inventory item.

Answered 03/28/2014 by: jknox
Red Belt

  • I had already created the custom software inventory item, but how do I reference it in a SQL report?
Please log in to comment
0

When you create a custom inventory rule you are creating a software title and it will be assigned an ID. If you login to your KBOX using the adminui URL the software ID will be displayed in the URL when viewing the rule. 

The returned data is then listed in the MACHINE_CUSTOM_INVENTORY table. To get a list of Install Dates per machine you could perform the following query:

 SELECT MACHINE_CUSTOM_INVENTORY.*, MACHINE.NAME FROM ORG1.MACHINE_CUSTOM_INVENTORY JOIN MACHINE on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.IDWHERE SOFTWARE_ID = 31484;

Be sure to change the SOFTWARE_ID = 31484 to match the software ID of your custom inventory rule. The specific value you're looking for will be in the NUM_FIELD_VALUE column although you really should be using a date since you're collecting a date. 

Answered 03/31/2014 by: chucksteel
Red Belt

  • So I've replaced the 31484 with the ID of my custom rule (15674) and made a report from what you gave me, but the report still shows nothing. Is my custom rule not being applied to the machines in my inventory?
    • When you look in the inventory for a machine that should have a value does it have one in the Custom Inventory Fields section? If not then try forcing an update on the machine to make sure it has updated its inventory. If it still doesn't have a value then there is something wrong with the rule.
      • It looks like the rule isn't being applied to any machine in the inventory. How can I make it apply to every machine?
      • Did you double check the Supported OS versions? The rule looks fine otherwise.
      • I have every OS selected.
      • I'm not sure what the issue might be, then. I don't have that exact registry entry on my machine but I do have HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{90150000-0011-0000-0000-0000000FF1CE} for Office 2013 (technically it's for Microsoft Office Professional Plus 2013) and it's working for me. What version of the AMP Agent are you running? Some of the older clients had bugs related to custom inventory fields but I can't remember what was fixed when.
      • We're using Agent Version 5.5.30275. I changed the Custom Inventory Rule to the following:

        RegistryValueReturn(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{90140000-0011-0000-0000-0000000FF1CE}, InstallDate, NUMBER)

        Since most machines have Office 2010. Now I have about 32 machines being listed under the Software Deployment Detail list, and the report runs perfectly, but I'm still unbelievably confused about how this works, especially due to the fact that I don't have that registry item on my personal machine, but it's being registered by the custom inventory rule as if I do. Am I missing/misunderstanding something? I feel like I'm still doing something wrong here.
      • Is there a possible 32bit vs 64bit issue? When I tried to run the rule on a 64bit machine it did not work.
      • That's what I thought at first, but my machine (and 95% of the other machines at my office) is 64-bit.
      • Are you using 32 bit version of Office? If so, on a 64 bit machine the key should show under HKLM\Software\Wow6432Node\Microsoft if you look manually in regedit. The Kace agent is 32 bit, so it sees this Wow6432Node area as HKLM\Software\Microsoft
Please log in to comment
Answer this question or Comment on this question for clarity