Unknown Devices in discoveries.

MarkyB

Hi @stuartd

We have devices that I have discovered using ICMP.
I can identify the devices from our records, but we have no access to them at all.
These are routers that we can not get any SNMP access to. I have 164 (Just counted them) of them and they are all PSBA (Public Sector Broadband Aggregation) I need to get these out of the unknown discovered devices please.

Any help much appreciated 

stuartd

OK ... so this is fairly straightforward once you wrap your head around it but it comes with a HUGE caveat and some advice. We do this for Thin APs that we've added as a ping only IP or 3rd party routers where we don't have SNMP access to.

CAVEAT: This is done via SQL not SWQL so it is a direct change in the DB, and is the only type of change I do that way - everything else is either via the GUI or we try and find a way via Powershell and SWQL but we are not coders.

ADVICE: So because of the caveat, I would say BACKUP your DB first.

Step 1.

Open up your Database Manager (probably from your primary poller is best), log in and click on 'Add Orion Server. In the right hand window, expand the server name and scroll down to NodesData. Now right click that name and click 'Query Table'. You should see the right hand side get populated with the query, so now click execute query or press F5. Feel free to scroll around and explore at this stage you can't hurt anything. As a default the query restricts itself to the TOP 1000 - feel free to edit that number or indeed remove it.

To see only your unknown devices, adjust the query to look like this:

SELECT * FROM [dbo].[NodesData]
WHERE Vendor LIKE '%unknown%'

If your devices in question all have a similar naming convention then you could narrow down this view further by adding something like:
WHERE Vendor LIKE '%unknown%' AND Caption LIKE '%PSBA%' - just look through your columns and decide which one is most appropriate.

Now if you re-run the query it should limit it to just those.

Step 2:

Once you have your data click the empty block top left of the results which will highlight it all and copy/paste it into a blank Excel s/s. The only reason I say to copy it all, as you may want to manually manipulate / check the data outside of the DB Mgr.  All we really need for this to work is the IP Address column of the devices you want to change.

So, once you have the IPs in question then ...

Step 3.

In a new page create 3 columns and paste the IP's into the second one.

I find it easier when doing a bulk change like this to label the 3 columns like this:

So now, in the vendor column, type your group name of choice - PSBA for eg. Then click, hold and drag on the bottom right corner to populate the name down for the number of IPs. 

In the SQL Query box, paste this:

=IF(A2<>"",CONCATENATE("UPDATE NodesData SET Vendor = '",A2,"' WHERE IP_Address = '",B2,"';"),"")

and again, as with the vendor column, drag down to populate, and it should now look like:

Step 4:

This is the bit where brave souls tread if they haven't backed up their DB, or know that it is backed up - I don't scare easily though so first time I just went for it 

So now, take all the data in column 3, and return to your DB Mgr.

Open a new query window - or just delete an old one and paste the generated lines from your s/s into the query window.  You can do it one line at a time or just paste the lot and hit F5 / execute query. After it's completed you'll see a message in the bottom window showing:
(100 row(s) affected)  (or however many rows it was)

Give it a few minutes depending on how big your dataset is and if you want to confirm this has taken place then re-run your original query, and you should have no results. If there are still any left in your results then just wait and re-run and you'll see them dropping off as the DB updates.