DB Table(?) Locations

Not even sure the title is the right phrasing for this but ...

I always get thoroughly confused when I'm trying to find table entries in the DB - whether I use the DN Manager of SWQL Studio. Once I find what I want, it is so much easier to see the bigger picture at a glance than trying to use the GUI.

Anyway, today's challenge is to try and find where Solarwinds hides/stores the following details:

- last backup date

- is backup enabled (GUI options are Yes/No/Never)

Anybody point me at these ones please?

Find more posts tagged with

Accepted answers

sum_giais

I've had to build a query like that before to sort through the nodes that should / shouldn't be in NCM and also verify that they are actually able to login. Much easier than any alternative of manually sifting through them in the GUI.

SELECT n.NodeID, n.IPAddress, n.Caption, n.DNS, n.MachineType, n.Vendor, n.NCMLicenseStatus.LicensedByNCM, nc.LoginStatus
FROM Orion.Nodes AS n
LEFT JOIN NCM.Nodes AS nc
ON nc.CoreNodeID = n.NodeID AND n.NCMLicenseStatus.LicensedByNCM = 'Yes'

The way the query is set, the NCM login details are only joined if the node is licensed (set to 'Yes') in NCM.

Hope you find this useful -

Take care.

All comments

sum_giais

SELECT n.NodeID, n.IPAddress, n.Caption, n.DNS, n.MachineType, n.Vendor, n.NCMLicenseStatus.LicensedByNCM, nc.LoginStatus
FROM Orion.Nodes AS n
LEFT JOIN NCM.Nodes AS nc
ON nc.CoreNodeID = n.NodeID AND n.NCMLicenseStatus.LicensedByNCM = 'Yes'

The way the query is set, the NCM login details are only joined if the node is licensed (set to 'Yes') in NCM.

Hope you find this useful -

Take care.

stuartd

Thank you - I am still very much learning SWQL/SQL and the 'Join' bits still throw me for a loop, though as I understand it they are calling data from a different table.

But, yes, that appears to be a most excellent start for me.

As a follow on (more for my benefit than anything), and as I read the code, it is pulling data from Orion.Nodes and NCM.Nodes tables, yes? So I've just edited it slightly to remove n.DNS and replaced it with nc.Client which still works and is better for my specific scenario.

I just spotted that I had to add a "Where n.NCMLicenseStatus.LicensedByNCM = 'Yes' to get back only those actually configured.

Is there a way to include the date devices were last backed up and, this one is a stretch (well it is for me) whether it is included as part of a job or not?

sum_giais

Yes that's correct, it starts with Orion.Nodes, then joins in (left join) the NCM.Nodes data. Here's an attempt at pulling in the LastAction Date. Looks to work, but incorporating in Job data may be a bit more complicated. Jobs use (vaguely looking at it) an XML format and also dynamic queries and otherwise so they don't have a direct line to what nodes are running in them. A better way to think about that if you're using dynamic queries to include nodes in the NCM jobs would be to have an alert setup if a specific device should be in NCM but certain criteria is not set that would deem it run against the job.

SELECT n.NodeID, n.IPAddress, n.Caption, n.DNS, n.MachineType, n.Vendor, n.NCMLicenseStatus.LicensedByNCM, nc.LoginStatus, ToLocal(sub.DateTime) AS LastAction
FROM Orion.Nodes AS n
LEFT JOIN NCM.Nodes AS nc ON nc.CoreNodeID = n.NodeID AND n.NCMLicenseStatus.LicensedByNCM = 'Yes'
LEFT JOIN ( SELECT MAX(tr.DateTime) AS DateTime, tr.NodeID FROM NCM.TransferResults AS tr GROUP BY tr.NodeID ) AS sub ON sub.NodeID = nc.NodeID AND n.NCMLicenseStatus.LicensedByNCM = 'Yes'
WHERE n.NCMLicenseStatus.LicensedByNCM = 'Yes'

Good luck!

stuartd

@sum_giais again my thanks. I will give that a go and see what I get.

p.s. apologies for tardy reply but I have been away on a first aid at work course.