SQL Monitoring

To monitor that the SQL Engine service is running you only need normal windows server credentials.

Then I should ran some SQL query also to see that its actually responding also. What the ask is up to you but I ask how many databases that are NOT backed up last 24h. That is a good thing to ask also.

SELECT
d.database_id,
DatabaseName = d.name ,
LatetstBackUpDate = ISNULL(CONVERT(VARCHAR(30),MAX(b.backup_finish_date),120),'No backup'),
'Days' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) / 24,
'Hours' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) % 24
INTO #BackupInfo
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.name NOT IN ('tempdb','model','ReportServerTempDB','ReportServer','master','msdb') and d.state = 0
GROUP BY d.database_id, d.name
HAVING ISNULL(MIN(DATEDIFF(hh,b.backup_finish_date,getdate())),999) >= 24 * 2

IF (Select count(*) as "FirstCount" from(select b.database_id from #BackupInfo b) as FC)>0
BEGIN
Select
count(*) as "Rowcount",
t.Backupinfo
from(
Select
substring(
(
Select ', '+bkt1.DatabaseName AS [text()]
From #BackupInfo bkt1
For XML PATH ('')
)
, 2, 1000) [Backupinfo]
From #BackupInfo bkt2)
as t
group by t.backupinfo
END
ELSE
Select 0

drop table #backupinfo

If you only want to check how many databases that are online, use this:

Declare @nr int
Declare @Mess varchar(30)

select
@nr=count(*)
,@Mess = 'Number of databases online'
FROM
sys.databases
where state=0

Select @NR, cast(@nr as varchar(3)) + ' databases online'

And yes, you need an account that has permission to ask that SQL querie. Add to the SQL server and also to Orion. SQL local account or AD-account.

I do not really understand what you mean? But this is not in SAM Solarwinds, is it? If so, can you describe a little more how I set it up?

Yes, this is for SAM. I have tried to share the template for this below, hope it works.

So in a SAM template you can have different components. For example some that monitors services, like the SQL engine service, and other components can run SQL queries against the monitored server, the "SQL Server User Experiance Monitor". There you can write your own queries for what info you want to know. 

The template below includes:

So both services and sql queries.

Download the template, import it into your orion (Settings/All settings/ SAM settings/Manage templates/Import...)

Then assign it to the node you want to monitor. You may try to use "inherit credentials from node" or choose something else.

SQL Server Basics.apm-template

Thanks for your help, you are cruel!
It seems to work, I will let it roll in a couple of days now and I will return with a definitive answer. You may not be able to do anything similar with IIS. I just want to know if the IIS service is running on a server and then I want to monitor the web pages that the server takes care of, there can be several.

For IIS, if you don't want to use Appinsight for IIS, it is good,  there is another template called "Internet Information Service (IIS) 10". It looks at alot also. But in there you have the service. you can disable to component you are not interested in. 

Also you can add a http- or https-monitor to check if the website is responding. 

I don't have a "IIS Server Basics" template like with SQL but it's easy to create. Do you want me to do that you do you want to try for yourself?

Yes please if you have time to create IIS templare for me i Will be very statisfied.

Yes please if you have time to create IIS templare for me i Will be very statisfied.

Created an IIS Server Basics template. Attached below.

Change the http and https components to match your environment. Add more or disable as required. Probably you need to adjust the url they point to also.

Hope it works fine for you!!

IIS Server Basics.apm-template