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_idnorder 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!
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?
1 of 1 people found this helpful
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 newfrom dbo.history as hleft outer join dbo.Computer as c on c.computer_idn = h.computer_idnleft outer join dbo.METAOBJATTRRELATIONS as mr on h.MetaObjAttrRelations_Idn = mr.MetaObjAttrRelations_Idnleft outer join dbo.METAATTRIBUTES as ma on mr.METAATTRIBUTES_IDN = ma.METAATTRIBUTES_IDNorder 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.
Good deal! :-)
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 !!!
That is very cool. I'm going to have to give this a try myself.
Thanks for the post!
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.
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