Skip navigation
2867 Views 8 Replies Latest reply: Aug 27, 2012 1:28 PM by tlman12 RSS
Rookie 6 posts since
Oct 27, 2010

Has received 2 of 9 achievements.
Currently Being Moderated

Jul 29, 2011 10:24 AM

Inventory History Report

I've set up Inventory History to log the "Owner" info in order to track machines being used for other users and find cases of machines moved between users.

 

Doesn't make sense having the Inventory History option if you cannot retrieve this information in a report.

 

Does anyone have an idea on how to retrieve this information ?

 

Thanks in advance,

  • Catalysttgj Expert 413 posts since
    Jul 1, 2010

    Has received 8 of 9 achievements.
    Currently Being Moderated
    Re: Inventory History Report

    I couldn't find anything under any of the canned reports that would be useful, but i did find it in the database.

     

    The table is "history". Pretty obvious. :-)

     

    Doing a basic SQL select statement on this table will reveal what its capturing: "select * from dbo.history"

     

    The query below could be functionally useful. It will show the devicename plus the most important fields from the history relating to the device, ordering by device name first and then the change date.

     

    "select c.DeviceName, h.ChangeDate, h.oldstr, h.newstr from dbo.history as h left outer join dbo.Computer as c on c.computer_idn = h.computer_idn
    order by c.DeviceName, h.ChangeDate"
    ---
    I turned on IP Address which will be pretty cool to see for mobile devices. The issue i see with this will be how large this table will grow. It appears that how much information is retained is controlled under services - inventory tab, and the software button. There's a choice in there for how long it keeps history. I assume that its this history that it refers to.
    Good luck!
  • Catalysttgj Expert 413 posts since
    Jul 1, 2010

    Has received 8 of 9 achievements.
    Currently Being Moderated
    Re: Inventory History Report

    Additional notes... If you check "Alert" in the inventory history screen on the item that you want to get, in addition to the "inventory" check box, you will get "alerts" and those show up under "Logs". The only problem that i see with the data coming into alerts is that the date time info is GMT and not local time. The data going into the history table in the DB is showing in local time, which is quicker to process in my head at least!

     

    Still, I don't see any canned reports relating to alerting, so other than the ability to perform basic searching and filtration in the console, the only real way to get the data into a report is direct SQL queries in the db, and then export it from there.

     

    Anyone else see something different?

  • Catalysttgj Expert 413 posts since
    Jul 1, 2010

    Has received 8 of 9 achievements.
    Currently Being Moderated
    3. Jul 31, 2011 12:47 PM (in response to Catalysttgj)
    Re: Inventory History Report

    Here's a better query:

     

    select c.DeviceName, h.ChangeDate, mr.tablename as category, ma.ATTRNAME as value, h.oldstr as old, h.newstr as new
    from dbo.history as h
    left outer join dbo.Computer as c on c.computer_idn = h.computer_idn
    left outer join dbo.METAOBJATTRRELATIONS as mr on h.MetaObjAttrRelations_Idn = mr.MetaObjAttrRelations_Idn
    left outer join dbo.METAATTRIBUTES as ma on mr.METAATTRIBUTES_IDN = ma.METAATTRIBUTES_IDN
    order by c.DeviceName, h.ChangeDate
    ---
    This will hopefully always get the tablename from which the inventory value belongs and the name of the inventory value itself. The new columns are labled "category" and "value" respectively. So for an exmaple: IP address; the category is "TCP", and the value will be "Address".
    Hope that helps.
  • Catalysttgj Expert 413 posts since
    Jul 1, 2010

    Has received 8 of 9 achievements.
    Currently Being Moderated
    Re: Inventory History Report

    Good deal! :-)

  • Catalysttgj Expert 413 posts since
    Jul 1, 2010

    Has received 8 of 9 achievements.
    Currently Being Moderated
    Re: Inventory History Report

    That is very cool. I'm going to have to give this a try myself.

     

    Thanks for the post!

  • tlman12 Rookie 10 posts since
    Sep 27, 2011

    Has received 1 of 9 achievements.
    Currently Being Moderated
    8. Aug 27, 2012 1:28 PM (in response to Catalysttgj)
    Re: Inventory History Report

    Sorry to reply to such an old post but this is almost EXACTLY what i'm looking for and i can't figure out how to private message. the only thing is i haven't quite grasped the concept of parameters.

     

    how would i use this query and have parameters for between Change Date, device name, category, and value.

     

    for some reason i am able to do everthing except make it work. i know you put in a question mark in the query and add a parameter and add a parameter to the report but for some reason when i do it i either get evertyhing or i get nothing, not filtered.

     

     

    ***EDIT***____________________________________


    I actually had some time to play around with this any I sort of figured out parameters.. sort of... I can't figure out how to use multiple values at once like 2 or 3 different categories, works fine with just one.. but i also figured out (and this may be a little butchered because i am definatly not a SQL programer) but if you have custtom data or custom inventory that your are archiving like i am with your querey all you got was UNMODELDATA for the category and DATA STRING for the Value. so I poked around for a little bit and learned what left outer join meant and modified it a little and it seems to work for me. now i get the actual custom name and custom value name, here it is for those that may stumble on this page from google like i did

     

    select c.DeviceName, h.ChangeDate, mb.enu as category, mc.enu as value, h.oldstr as old, h.newstr as new

    from dbo.history as h

    left outer join dbo.Computer as c on c.computer_idn = h.computer_idn

    left outer join dbo.METAOBJATTRRELATIONS as mr on h.MetaObjAttrRelations_Idn = mr.MetaObjAttrRelations_Idn

    left outer join dbo.METAOBJRELATIONS as ma on mr.METAOBJRELATIONS_IDN = ma.METAOBJRELATIONS_IDN

    left outer join dbo.METAOBJECTS as mb on ma.METAOBJECTS_IDN = mb.METAOBJECTS_IDN

    left outer join dbo.METAATTRIBUTES as mc on mr.METAATTRIBUTES_IDN = mc.METAATTRIBUTES_IDN

    order by h.ChangeDate DESC

More Like This

  • Retrieving data ...

Bookmarked By (2)

Legend

  • Correct Answers - 20 points
  • Helpful Answers - 10 points
LANDESK Community powered by Jive SBS® 4.5.7.1  |  Legal Notices  |  Privacy Policy  |  Icon 

TweeterOn Twitter  |  Icon FacebookOn Facebook © 2007 LANDESK Software