Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Network Discovery Alert Variables

Getting emails from scheduled discovery jobs is very helpful, not knowing which discovery they come from isn't. I feel its possible but I need some SQL help to sort it out.

Info:

There are 2 tables we need to tie together:

DiscoveryProfiles

DiscoveryJobs

The discovery notification comes from the discovery jobs table. If you look at the table you can see a row for every job. That row contains variable "ProfileID".

I need to tie the "ProfileID" variable from the "DiscoveryJobs" table to the "ProfileID" in the "DiscoveryProfiles" table so I can pull out the "Name" of that discovery.

I would like to have the "Name" of the profile inserted into the email so when i get 6+ a night i know which job it came from. This would also be helpful in the discovery failed emails so you would know which discovery failed to run.

Find more posts tagged with

npm alerts

Accepted answers

marcrobinson

We had a problem with it returning the same profile name every time. This custom variable has been working for us, but it is not elegant. I am using NPM 12. I call the top 1 job (single job) and use a where clause to reorder and filter by successful jobs and timestamp in descending order.

${N=SWQL;M=SELECT Top 1 DiscoveryProfiles.Name

FROM Orion.DiscoveryLogs

INNER JOIN Orion.DiscoveryProfiles

ON DiscoveryLogs.ProfileID = DiscoveryProfiles.ProfileID

WHERE DiscoveryLogs.Result = 2

ORDER BY DiscoveryLogs.FinishedTimeStamp Desc}

For jobs that are not successful. I set the where clause to <>2 (UNsuccessful discovery)

Discovery for ${N=SWQL;M=SELECT Top 1 DiscoveryProfiles.Name

FROM Orion.DiscoveryLogs

INNER JOIN Orion.DiscoveryProfiles

ON DiscoveryLogs.ProfileID = DiscoveryProfiles.ProfileID

WHERE DiscoveryLogs.Result <> 2

ORDER BY DiscoveryLogs.FinishedTimeStamp Desc}

I suspect this might work - I am not sure if it would look up the variable in the swql statement, or fail badly.

${N=SWQL;M=SELECT DiscoveryProfiles.Name

FROM Orion.DiscoveryLogs

INNER JOIN Orion.DiscoveryProfiles

ON DiscoveryLogs.ProfileID = DiscoveryProfiles.ProfileID

WHERE DiscoveryProfiles.ProfileID = ${N=SwisEntity;M=ProfileID} }

I hope this helps, but I suspect I am missing something very simple in the variable.

Marc

All comments

ctlswadmin

Hi Justin,

Give this a try, if it doesn't work let me know what version of NPM you are running.

Select DiscoveryJobs.Name,DiscoveryProfiles(desired column names)
From DiscoveryProfiles
INNER JOIN DiscoveryJobs
ON DiscoveryProfiles.ProfileID = DiscoveryJobs.ProfileID
Where (enter your filters here)

To add filters on DiscoveryProfiles use DiscoveryProfiles.ColumnName, to add filters using DiscoveryJobs use DiscoveryJobs.ColumnName

Thanks,

David

jmontague

Sorry it took so long to get back on this. I appreciate the effort! I'm running NPM version 12.

First problem, i must have wrote the table name wrong in my question. Trying your script i realized the tables are:

DiscoveryLogs

DiscoveryProfiles

Not sure where i got DiscoveryJobs at (maybe it changed in a version??)

I can't run the script as you provided since i have no "where" (or at least i don't know enough to know what that might be). So i'll throw some more info in here and hopefully that will help explain what i'm asking Solarwinds to do.

I've set the following canned alert to run:

Network Discovery and Import complete

I've set this alert up to send an email once a discovery has completed, and to list any nodes/interfaces found during the discovery. It is providing the nodes/interfaces correctly.

If i try and replace the ?? with (insert variable = "Profile ID") my emails sends me for example "Discovery Profile: 18" because from what i can gather i can only pull data out of the DiscoveryLogs table which does know 18 = Manual_Scan.

Screen Shot 2016-12-19 at 1.57.44 PM.png

Here is the 2 tables i believe are needed to get the "name" into the alert email.

DiscoveryLogs:
-if you look the "profileID" is a variable for which discovery job was ran (ProfileID = 18 for example is the "Manual_Scan" discovery job)

Screen Shot 2016-12-19 at 1.52.16 PM.png

DiscoveryProfiles:

-here you can see the "profileID" that matches up to the table above. It has a "name" that i want to put into the email alert (ProfileID "18" = Name "Manual_Scan")

Screen Shot 2016-12-19 at 1.52.32 PM.png

marcrobinson

${N=SWQL;M=SELECT Top 1 DiscoveryProfiles.Name

FROM Orion.DiscoveryLogs

INNER JOIN Orion.DiscoveryProfiles

ON DiscoveryLogs.ProfileID = DiscoveryProfiles.ProfileID

WHERE DiscoveryLogs.Result = 2

ORDER BY DiscoveryLogs.FinishedTimeStamp Desc}

For jobs that are not successful. I set the where clause to <>2 (UNsuccessful discovery)

Discovery for ${N=SWQL;M=SELECT Top 1 DiscoveryProfiles.Name

FROM Orion.DiscoveryLogs

INNER JOIN Orion.DiscoveryProfiles

ON DiscoveryLogs.ProfileID = DiscoveryProfiles.ProfileID

WHERE DiscoveryLogs.Result <> 2

ORDER BY DiscoveryLogs.FinishedTimeStamp Desc}

I suspect this might work - I am not sure if it would look up the variable in the swql statement, or fail badly.

${N=SWQL;M=SELECT DiscoveryProfiles.Name

FROM Orion.DiscoveryLogs

INNER JOIN Orion.DiscoveryProfiles

ON DiscoveryLogs.ProfileID = DiscoveryProfiles.ProfileID

WHERE DiscoveryProfiles.ProfileID = ${N=SwisEntity;M=ProfileID} }

I hope this helps, but I suspect I am missing something very simple in the variable.

Marc

jmontague

SO SO very close!! i really appreciate the time thus far!

Here is what i added to my alert:

Screen Shot 2017-01-04 at 1.30.54 PM.png

And here is the email from a discovery job called "Manual_Scan":

Screen Shot 2017-01-04 at 1.30.24 PM.png

It got the right variable (or it just grabbed the first entry in the table ). I left it in the email and i'll check the emails i get overnight from the scheduled discoveries that will run.

Thanks!!