Time when a device discovered in NetVoyant

[Legacy User]

Hi,

 

Can anyone help me to have database query so that i will get to know when a device is discovered(Added) in NetVoyant database? Need the date only.. if we can get time as well no prblem.

 

Also it would be nice if we can get date for when the device got rediscovered last?

 

Thanks in Advance.

[Legacy User]

Any help on this?

[Stuart Weenig]

Sorry, CAWorld was this week, so most of us have been involved with that.

 

select *, from_unixtime(dev_discovered) as 'First Discovered', modified as 'Last Modified' from devices;

 

This will give you when the device was first discovered and last modified. Last modified should be the same as the last time it was discovered. However, it may only represent when the last discovery that changed anything happened.

[Legacy User]

Hey Stu,

 

I applied the query in database. Now can you please let me know how can i see it in a page?

[Stuart Weenig]

You have two options:

1. Setup the ODBC connector and use that query in your config. This will run the query every time the page is rendered.
2. Run the query as part of a scheduled task with the -H flag. This will output the results in HTML. Redirect that HTML to your custom content directory and use a browser view to get it on the page. This will run the query only when it is scheduled.

[Legacy User]

Hey Stu,

 

I am not using NPC.. so it seems like i can not perform above two options.

[Stuart Weenig]

Why not?

You can use the browser view in NV web GUI I think. 

[David Michel]

If all that is needed is to see the output in an easy to read format you can use this:

mysql nms2 -t -e "select *, from_unixtime(dev_discovered) as 'First Discovered', modified as 'Last Modified' from devices" > D:\DeviceDiscoverDate.txt

or if you don't want all the info for each device

mysql nsm2 -t -e "select from_unixtime(dev_discovered), dev_alias, dev_name from devices" > DeviceDiscoverDate.txt

In this case dev_discovered = the 'First Discovered' in the first query.

[Stuart Weenig]

You could also design a query to create a property for each device with the updated discovery time.

[Legacy User]

Yeah In addition to Stu recommendation can we have this in NetVoyant webpage.

[Stuart Weenig]

Adding a property will add it to the details page in NPC & NV. I'll post a script later. 

[Stuart Weenig]

Run this script every so often and the properties will show up in the details view for each device in NPC and NV.

mysql -P 3308 nms2 -e "replace into properties select dev_properties,'Last Discovered', 18, from_unixtime(dev_discovered),0,0 from devices;"
mysql -P 3308 nms2 -e "replace into properties select dev_properties,'Last Modified', 18, modified,0,0 from devices;"

I've also incorporated these two lines into my properties setting script.

[Stuart Weenig]

If you run these queries with a -H option, it will output HTML format instead of tab separated text documents.