Short answer -- "Would need your database to look at" to see what's what. So in other words - this would need to go to support.
Couple of key questions:
- Did you have a backup before you went around deleting things (unlikely to be of major use here, since this should be relatively easy to fix I hope - but a good and important habit to get into!)
- How did you delete the old custom data? I'm assuming DBREPAIR ? Or did you do something else?
- Had you stopped the INVENTORY SERVICE during your delete (please say "yes"). If not, there's a chance you've corrupted your DB schema if you haven't (should still be fixable though).
I suspect something went wrong when you deleted the attributes (i.e. - you didn't stop the inventory service) so they weren't actually deleted (as the inventory service caches the schema & may have re-written them).
If you're not familiar with DBREPAIR, you can request it from support -- as we need to give you a version specific to what version of LANDesk Management Suite you use.
... i've dug up an old (10+ year old) PDF I've written up on how to use DBREPAIR (it's all essentially still the same). Just forigve that some of the UI has changed & such -- but all the important stuff is still very much the same.
And the one cardinal rule is -- when changing the DB stuff -- make sure the Inventory Service is stopped!
Hope that helps .
How to use DBREPAIR.pdf 790.8 K
Thanks for your reply. Below are some answers to your questions. (I have already raised this with the support but still waiting their feedback)
- Did you have a backup before you went around deleting things (unlikely to be of major use here, since this should be relatively easy to fix I hope - but a good and important habit to get into!) (No I didn't)
- How did you delete the old custom data? I'm assuming DBREPAIR ? Or did you do something else? (Right click on the attribute and deleted it)
- Had you stopped the INVENTORY SERVICE during your delete (please say "yes"). If not, there's a chance you've corrupted your DB schema if you haven't (should still be fixable though). (Actually I didn't stop the service)
1 of 1 people found this helpful
Right - that'd explain it then.
So "tut tut" on the lack of DB backup. Should be fine in this case, but I've seen the strangest things happen ... the old law of "if it *CAN* go wrong, it will" // "If you HAVE a backup, you won't NEED it" ... .
Based on you not stopping the inventory service, chances are that what I described has happened (it caches the schema & writes it back from time to time -- hence causing changes on the back-end leading to corruption and strange stuff).
DBREPAIR *should* be able to handle it ... usually. Let us know here if it doesn't (feel free to update the support ticket into "actually - just need DBREPAIR for this version of LDMS I Use").
If that doesn't work (and you're not scared / intimidated by digging into SQL), I can try to give you an explanation on how to clean up the stuff by hand (since it MAY not all be properly connected up but still cause artifacts).
Should be all doable .
Actually only 1 device has been filled with these details as I was just testing the import of CSV into LDMS. If you can provide me with steps on how to do it on the SQL that would be great!
I can test it in my lab environment and then apply it into production
All right. This will only work in "lab & production" if you have an exact copy of your live DB in the lab.
Anyway -- a bit of "essential reading" to begin with...
Basic Plan of action
- Stop the Inventory Service (necessary).
- Make sure you have a full backup of the DB (to prevent any "whoops" type stuff ... we'll be deleting and operating in the guts of the DB directly ... that's no space for errors.
- Find the *DATA* and delete that.
- Find the associated data OBJECTS ("inventory containers") and delete those.
- Re-build components of the DB (a "just in case" safeguard).
- Start the Inventory Service & we're done..
Now then ... in a bit more detail... with an example.
WARNING - Just *please* do beware (and respect) that this is a very highly sensitive set of operations. If you're NOT confident in doing this, don't feel shy about pulling in a SQL guy to help you along. On the other hand, this stuff *IS* fairly straight forward if you don't let it intimidate you.
Better to get a bit of help here than potentially breaking your database.
To "demo" this case, I have the following custom data on 1 device added...
- STOP the Inventory service! And make sure you have a healthy DB Backup!
- Locate your particular custom data using the means described in my article (I'm assuming that'll work). In this case, I know a few data values, so this gives me an edge here.
- Querying the UNMODELLEDDATA table, I'm checking for what DATASTRING-s hold my value(s).
- Make sure to note down any METAOBJATTRRELATIONS_IDN related to your custom data!
SQL used here:
select * from UNMODELEDDATA -- now I'm searching for a few strings to make my life easier. -- I could equally filter by COMPUTER_IDN to reduce the output to a single device, for instance! where DATASTRING in ('18.104.22.168', '22.214.171.124')
That gives me an output like this:
The part that's immediately important here is the METAOBJATTRRELATIONS_IDN ! So in this case, that's "7397" and "7398".
If we were wanted to just delete these values we could do that via a simple "DELETE FROM..." statement, but we don't want to "just" delete the data - we want to delete the associated objects (i.e. "inventory containers") as well.
So for that, we need to see what's connected to what. So - let's look at the METAOBJATTRRELATIONS then!
- Let's dig into the first level ... into the "Meta Object Attribute Relations" linking table
select * from METAOBJATTRRELATIONS -- filtering on our previously noted IDN since that's the only stuff we care about. where METAOBJATTRRELATIONS_IDN IN (7397, 7398)
which gives us something like this:
... that gives us the initial layer of linkages. Stuff that's important to note on this level:
- METAATTRIBUTES_IDN (unique identifier for actual data value associated with a field - so "Version" for instance. In this case the fields would be "Something" and "Something Else")
- In this case, the values we care about are "2959" and "2960"
- METAOBJRELATIONS_IDN (Unique identifier to keep track of the object (i.e. "Data Container") in inventory. In this case, it'd be "Blah Blah" since that's what I named mine.)
- In this case, the values we care about are "894" and "894" (since both attributes are under a single container - "Blah Blah")
Now we'll need to resolve those guys! Since we're working "from the leaf to the branch to the tree", we'll start with the data attribute (i.e. "Something" / "Something Else") and then the data object they're contained in (i.e. "Blah Blah").
SQL write-up for our eradication so far looks like so:
-- First up - make sure that all of our "end point" data is deleted delete from UNMODELEDDATA where METAOBJATTRRELATIONS_IDN IN (7397, 7398)
- Double-check the METAATTRIBUTES table ... (ALWAYS put eyes on data before deleting it ... at least the first few times!)
select * from METAATTRIBUTES where METAATTRIBUTES_IDN IN (2959, 2960)
Which gives me the following:
Which is exactly what I expected.
- One attribute entry for "Something"
- One attribute entry for "Something Else".
- The fact that all the language strings are identical is a "dead giveaway" that this is custom data that's not modelled (i.e. - just came in & doesn't have a table to call its home).
- The fact that the attribute name is "Datastring" is equally a dead giveaway that this is indeed regular custom data which would live in the UNMODELLEDDATA table.
Before we can delete the ATTRIBUTE data though, we need to clear out the relationship mapping (to an object) for it first. That happens in the METAOBJATTRRELATIONS table, where we use the METAOBJATTRRELATIONS_IDN as well.
... perfect - so - let's add the relevant SQL to our "extermination script" that we're gathering. So far, it should look like so:
-- First up - make sure that all of our "end point" data is deleted delete from UNMODELEDDATA where METAOBJATTRRELATIONS_IDN IN (7397, 7398) -- Now that we've delete the DATA, we can start cleaning up the container structure and attributes. -- Attributes first! DELETE from METAOBJATTRRELATIONS where METAOBJATTRRELATIONS_IDN IN (7397, 7398) -- Having deleted the relationship data, we can now delete the attribute record itself! DELETE FROM METAATTRIBUTES where METAATTRIBUTES_IDN IN (2959, 2960)
OK - now we can start looking at our OBJECTS (the data containers) ... so "Blah Blah"-s turn to get identified & marked for extermination .
- Nearly there -- now onwards, for METAOBJECTS!
... but how do we find them? We don't have a METAOBJECTS_IDN do we?
We will shortly. The whole MetaObject "tree" is modelled in the METAOBJRELATIONS table. So - let's query in there with the METAOBJRELATIONS_IDN we have from above ("894") and see what we get.
select * from METAOBJRELATIONS where METAOBJRELATIONS_IDN = 894
Which will give us something like this:
Great - now we have a METAOBJECTS_IDN to resolve against (and double-check we got the right fellow!). So - let's do that!
select * from METAOBJECTS where METAOBJECTS_IDN=798
Which returns the following:
.... great - the name of the container is exactly what we wanted it to be - "Blah Blah". Perfect. Now we can mark this one for deletion as well and start deleting from the bottom (data) up towards the object.
Our final extermination SQL will looks like so:
-- First up - make sure that all of our "end point" data is deleted delete from UNMODELEDDATA where METAOBJATTRRELATIONS_IDN IN (7397, 7398) -- Now that we've delete the DATA, we can start cleaning up the container structure and attributes. -- Attributes first! DELETE from METAOBJATTRRELATIONS where METAOBJATTRRELATIONS_IDN IN (7397, 7398) -- Having deleted the relationship data, we can now delete the attribute record itself! DELETE FROM METAATTRIBUTES where METAATTRIBUTES_IDN IN (2959, 2960) -- and now the containers... -- first the record of the relationship itself... DELETE from METAOBJRELATIONS where METAOBJRELATIONS_IDN = 894 -- and then the container itself (now that everything else is gone about it). DELETE from METAOBJECTS where METAOBJECTS_IDN=798 -- Done!
- Run "COREDBUTIL" => "BUILD COMPONENTS" just to make sure that all the data structure is fine.
- Finally - (Re-)start the Inventory service.
... and you're done.
Thanks a lot for that. I am going to give it a try shortly and let you know how it goes.
One last question, How did you add a sub tree under the Custom Data? I need to create one in my production environment and the attributes will be created underneath it.
Also if I create this sub tree and attributes for one devices, will the changes also take place for the rest of the devices?
I (manually) edited a scan-file.
It just included the following strings:
Custom Data - Blah Blah - Something = 126.96.36.199
Custom Data - Blah Blah - Something Else = 188.8.131.52
A scan-file is "just a text file", so it can be added to quite easily. And you can containerise custom data easily too.
1 of 1 people found this helpful
Any "changes to the data structure" apply to all devices. That's why it's relatively easy to hunt down data based on their "owners" (such as METAOBJECT_IDN or METAATTRIBUTES_IDN).
So "yes" - any such changes are in fact global.
Not all devices might HAVE data in those places, but all devices *CAN* have it, as it were.
Worked like a charm for the data that are under custom data. Thank you!
Now we have the same thing but under the Computer (Main Object in the inventory). How can I achieve the same to it?
I managed to apply it myself using the same commands you posted. Many thanks Paul!
Good to hear you got unstuck / could move onward.
... I'll write this up into a clean article seperately when I get some time ... figure I might as well.