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?

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.

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

Do you have an idea of ​​what might be wrong or what I can check?

If you have a SQL Named instance, the SQL service name might be different than the default. So check the service name on the node and if needed update the template if needed:

I'm probably a little sluggish, but if I use this template for multiple servers, should I name each SQL instance in the box marked in yellow or do I do it correctly? Do I have a server that I connect this template to that does not have a sql instance that I put there, so there will be an alarm?

So, you should only use THIS template on SQL servers, of no SQL instance is found the component will be down.

If you have this template on all your SQL servers and they have SQL installed as a default instance, you should not be needing to change it.

I try to learn that  in orion monitoring is one thing and alerting another. Just because you monitor something it does not automatically mean you will get an alert. BUT if you have one alert rule that alerts if a component id down, then you will get an alert if you apply this template and it does not find the service. 

I want this monitoring on all my SQL servers but they can have different instance names. Could i use this template?

Yes. Assign the template to all SQL servers and edit the once that have different instance names. ("Override template")

Yes. Assign the template to all SQL servers and edit the once that have different instance names. ("Override template")