8 Replies Latest reply: Aug 27, 2012 1:28 PM by tlman12 RSS

    Inventory History Report

    Rookie

      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,

        • 1. Re: Inventory History Report
          Catalysttgj Expert

          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!
          • 2. Re: Inventory History Report
            Catalysttgj Expert

            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?

            • 3. Re: Inventory History Report
              Catalysttgj Expert

              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.
              • 4. Re: Inventory History Report
                Rookie

                Thank you very much Catalysttgj

                It resolved my situtation!!!

                Best Regards,

                • 6. Re: Inventory History Report
                  Rookie

                  Finally, to generate a REPORT on LDMS. I just followed the steps below:

                  1- Created a report using any other LDMS query (doesn't matter which one)

                  2- From REPORT DESIGNER edit DATA SOURCE Query


                  3- Changed the QUERY Code to the SQL Query Code provided by Catalysttgj above.

                   

                  4- Delted all reports fields and added the required ones from the new DataSource.

                   

                  5- Finally Save the Report Desing and PREVIEW

                   

                  Thats All !!!

                  • 7. Re: Inventory History Report
                    Catalysttgj Expert

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

                     

                    Thanks for the post!

                    • 8. Re: Inventory History Report
                      tlman12 Rookie

                      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