Network Discovery Email

is this possible?  If so how?

I also whish to see what nodes where discovered during the nightly network discovery as well as a few other people that dont have access to Solarwinds console, so we need to send the report to several managers.

Please advise if this is possible and how.

thanks

Try this SQL, the DiscoveredNodes table holds the last run discovery information for the nodes found, joined against the DiscoveryProfiles to get a name for it.

SELECT DP.Name, DN.[IPAddress] ,DN.[Hostname]

      ,DN.[DNS]

      ,DN.[SysObjectID]

      ,DN.[Vendor]

      ,DN.[MachineType]

      ,DN.[SysDescription]

      ,DN.[SysName]

  FROM [SolarWinds].[dbo].[DiscoveredNodes] DN

  INNER JOIN DiscoveryProfiles DP

          ON DP.ProfileID = DN.ProfileID

  WHERE DN.ProfileID IN (SELECT ProfileID From DiscoveryProfiles

                         WHERE Name LIKE '<Discovery Profile Name>')

Edit the <Discovery Profile Name> to match your Discovery Profile's name and run it Database Manager, MS SQL Studio, or create an Orion Report with it.

Profile names and their history can be found with this type of SQL query:

SELECT DP.Name --Disco-Profile Name

      ,DL.[ProfileID]                                       --handy to know for the SQL query above

      ,DL.[FinishedUTC]                                 --When it finished

      ,DL.[Result] -- result code

      ,DL.[ResultDescription]                          --result description

      ,DL.[ErrorMessage]                                --NULL unless it failed

  FROM [SolarWinds].[dbo].[DiscoveryLogs] DL

  INNER JOIN DiscoveryProfiles DP

          ON DL.ProfileID = DP.ProfileID

--AND CPUMultiLoad.TimeStampUTC > DATEADD(hh,-24, getdate())   --uncomment this and it will show only the last 24 hours of discovery history

Hope it helps

Once you see the logic and how the tables work, the alert trigger should be easy.

You know the ProfileID for the discovery, you can now check the DiscoveryLogs table for its last result [Result] or [ResultDescription] and use this as your alert trigger.

However, getting the results to display in the alert (email) might be the problem. You can write advanced SQL/SWQL queries and their result be displayed in the alert text, but it doesn't handle more than one result.

Wondering if anyone else on Thwack has gotten around this, without scripting the thing from PowerShell?

That is of course an option, call the SQL via PowerShell and then email your discovery results from from there, scheduled with Windows Scheduled Tasks.

No need to resort to powershell and scheduled tasks, The easiest way to do this would be a scheduled report of discovered nodes, but if you want to get really fancy with an alert so it only notifies you when something new is found you could do it like this.

Set up a new alert with a custom Swql alert condition like below to find any discoveries that completed in the last 24 hours that found 1 or more nodes that aren't already in the nodes list.

where DiscoveryLogs.profileid in

(select distinct dl.profileid from

Orion.DiscoveryLogs dl

join orion.discoverednodes dn on dn.profileid = DiscoveryLogs.profileid

left join orion.nodes n on n.ip=dn.IPAddress

where DiscoveryLogs.finishedtimestamp > ADDDAY(-1,getutcdate())

and n.nodeid is null

group by dl.profileid

having count(dn.nodeid)>0)

I'd set it to reset automatically after like an hour, and then set it to only be active for a period of 30 minutes around the time when I wanted the report to show up.  That way the alert will trigger, stay active until after the "report" goes out, then reset itself and be ready to fire again the next day. 

Then in the body of the alert message you want to include this to get it to dump out a giant table of results.  (i haven't tested if there is a character limit for this though, i bet there is, if so I'd probably remove the vendor from the report to stretch things out more)

${SQL:  SELECT isnull((select cast(dn.ipaddress + ' - ' + dn.hostname + ' - ' + dn.vendor + CHAR(10) + CHAR(13) as XML) FROM [dbo].[DiscoveryProfiles] dp join [DiscoveredNodes] dn on dn.profileid=dp.profileid where dp.name = 'yourdiscovery' FOR XML PATH('') ),'None') }

Hi,

I've tried this method and it works but I get spammed with a ton of emails. Am I doing something wrong?

I never actually use that method, just built it to prove the concept.  Do you run multiple discoveries per day? Did you narrow the alert's active window like I suggested up there.  What is your reset condition?  Looking at the logic I would expect it to send one email per discovery that completed in the previous 24 hours.

I have ran multiple discoveries a day. Even when I set the email to just pick one name, it still spams a lot of emails. I tried setting it to reset after an hour but since I was spammed with 40+ emails it didn't make a difference.

So with that logic, it is going to send an email for each Network Discovery that has been completed in the last 24 hours? Explains the 40+ emails if that is why I am getting spammed. I have 42 discoveries total.

Shouldn't need to create a separate alert for SQL for this, should be built into the feature. Will raise a request to be voted on..