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
Store

How to insert information from a tabular table into the alert email

We have SW and SAM but this is a SW feature we're trying to get working, here below we have a Orion Universal Device Poller using OID's adding data to a tabular table

So we've a custom poller setup to give a warning alert at less than 60 and a critical at less than 30 days and this works fine but we need to formulate a way of getting that information of the expiring certificate into the email alert using the variables or SWQL - can anyone assist in this please? We are currently getting an email with just node information within.

Find more posts tagged with

Accepted answers

All comments

adam.beedell

Search in here for "FOR XML PATH", you can export a table in a SQL macro but it's not very straightforward.

Something that tends to make things easier is to have a window of something like -1 day to +14 days (dial in based on your scenario) and report the worst in that window. (WHERE, TOP 1)

That makes the output easier, but does potentially have you miss if there's 2 bad ones in a batch or something.

jamieg

Cheers Adam

Don't think I'm finding what you're referencing there! Been having a good look for it today.

KMSigma.SWI

You might also want to consider looking at Adventures in PIVOTing - how to do a poor man's pivot in SWQL . This is the way I used to access this stuff. It'll still need to be pulled into the Alert as a Custom SWQL Variable, but at least you'll have what you need.

adam.beedell

I pulled this example from one of the top search results:

SELECT TOP 10 Convert(varchar,TimeGeneratedUtc)+' '+Message
FROM APM_WindowsEvent_Detail
INNER JOIN (SELECT ID as CID FROM APM_Component
WHERE ComponentOrder=1 and ApplicationID = 728) CID
ON ComponentID = CID
ORDER BY TimeGeneratedUtc DESC
for xml path('')

In this one you can kinda see how its starting with 10 results, and gets a "FOR XML PATH('')" stuck on the bottom.

there's some docs on the function over here as it's a pure SQL thing: https://www.sqlshack.com/for-xml-path-clause-in-sql-server/

It all gets a bit specific fast at this point, but i'd work like this:

1) Make a SWQL query that selects that table

- Download SWQL studio/orion SDK if you havnt already

- There'll be an ID and a table where it lives, find those

- select from table where ID

- see if SWQL is sufficient, using stuff like CONCAT

2) Open database manager or SSMS and try to do the SQL equivalent of step 1

3) Try a FOR XML PATH and see what it says, see if you can work that to something sufficient

4) Test in Edit Alert, you'll need to replace the ID you used in testing with a macro that produces that ID most of the time

5) If 4) wasnt enough yet, next try adding something within the quotes and brackets in the FOR statement, and maybe in concats, and then REPLACING it out for formatting perhaps
If that's not enough, the next step is to build a proper HTML table. I've got an example somewhere but it's pretty custom and hard to read. ('td') or simmilar creates cells, and then you need to supplement with the top and bottom of the table.