|
|
|
|
| |
|
ISA Server 2006 / 2004
: Inviare e-mail di alert sfruttando
SQL Server 2005
|
Autore:
Giulio Martino
Data: 08.01.2007
Informazioni sull'Autore:
opera su sistemi Microsoft
fin dai tempi del DOS
senza trascurare sistemi
Unix, Linux. Le sue competenze
spaziano anche nella installazione
e configurazione di apparati
di rete, firewall e sistemi
di posta. Svolge la propria
attività a Matera.
Domande
sull'articolo? Scrivi
sul forum di ISAServer.it
>>
|
ISA
Server 2006/2004
: Inviare
e-mail di alert
sfruttando
SQL Server 2005
|
Introduzione
In questo documento cercheremo
di capire come poter sfruttare
al meglio i log che ISA
server ci mette a disposizione
dopo averli salvati nel
nuovo motore SQL Server
2005.
Vista la molteplicità
di esigenze e di scenari
possibili, verranno forniti
solamente degli spunti
e una base comune sulla
quale, a seconda delle
esigenze, sarà
possibile costruire nuove
soluzioni.
La situazione ideale per
un amministratore di rete
sarebbe quella di essere
avvisato se qualcosa non
funziona o qualcuno viola
le politiche assegnate.
Un esempio valido puo'
essere portato segnalando
due prodotti che fanno
il lavoro di analisi dei
log degli eventi, e ci
avvisano via email in
caso di problemi; i due
prodotti in questione
sono:
http://www.gfi-italia.com/italia/eventsmanager/
http://www.gfi-italia.com/italia/nsm/
Come possiamo ottenere
la stessa cosa con i Log
di ISA server senza dover
spendere tempo e denaro
in componenti aggiuntivi?
La risposta è semplice...utilizzando
SQL Server 2005!!.
In breve gli stumenti
da usare saranno:
- Store Procedure
/ Funzioni
- Trigger
- DataBaseMail
A questo proposito segnalo
alcuni link necessari
per capire cosa sono e
come funzionano:
Quindi per esempio possiamo
1) Creare un trigger
che ci invii una email
se una regola del Firewall
viene violata.
2) Creare un trigger
che registri la riga in
una tabella Segnalazioni
quando una regola viene
violata
3) Creare una Store
procedure che tutte
le notti gira e ci restituisce
una tabella contente tutti
i siti visitati da uno
specifico client.
…e tante altre ancora.
Inoltre per i programmatori
sarà una passeggiata
con .NET realizzare
oggetti e applicativi
che sfruttino i dati registrati
su SQL Server 2005 da
ISA Server.
Invio
Automatico di email
Una delle funzioni più
interessanti è
quella di poter dire a
SQL Server 2005 di inviare
delle email in caso di
necessità.
Il Plug-In di invio email
pur non non essendo disponibile
nella versione Express
di SQL Server
non ci deve scoraggiare;
in realtà quello
che manca è solo
il componente esterno
DataBaseMail90.exe
e relative DLL,
chiamate da una Stored
Procedure di sistema
per inviare la email...tutto
il resto dell'ambiente
descritto di seguito è
presente.
Per risolvere il problema
del plugin mancante abbiamo
tre strade:
1) Costruirsi un Client
SMTP che interroga la
tabella contente le email
da inviare
2) Utilizzare componenti
aggiuntivi gratuiti di
terze parti
3) Acquistare la versione
completa di SQL 2005 Server
Configurazione dell'ambiente
di SQL Server 2005
Per compatibilità
con la versione Express,
che non possiede tutte
le MMC per la configurazione
dell'ambiente di Email,
userò le MMC solo
quando è presente
anche nella versione Express
negli altri casi invece
usero degli script SQL.
Per abilitare il Plug-In
di invio email andiamo
in Configurazione Superficie
di Attacco… >
Configurazione Superficie
di Attacco per funzionalità
> Posta Elettronica
DataBase
e selezionare la voce
Attiva Stored Procedure
di Posta Elettronica Database
Configurare il server
SMTP
Andiamo in Configurazione
di Reporting Services
> Impostazioni di posta
elettronica
Qui inseriremo i dati
del nostro server SMTP.
Creazione Account di
posta elettronica
[SCRIPT]
--
=====================================================
-- Author: Giulio
Martino
-- Create date:
05/12/2006
-- Description:
Aggiunge un account
di posta elettronica
-- =====================================================
EXEC msdb.dbo.sysmail_add_account_sp
@account_name =
'Isa_Log',
@description = 'Account
per invio posta
da SQL',
@email_address =
<indirizzo email
Account>,
@display_name =
'Isa Server Log',
@username = <
Nome Account>,
@password = <Password>,
@mailserver_name
= <SMTP Server> |
Questo script usa
la Stored Procedure sysmail_add_account_sp
per aggiungere un account
di posta elettronica.
La tabella in cui verrà
creato l'account è
msdb.dbo.sysmail_account.
Creazione Profilo di
posta elettronica
[SCRIPT]
--
===================================================
-- Author: Giulio
Martino
-- Create date:
05/12/2006
-- Description:
Aggiunge un Profilo
-- ===================================================
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name =
'IsaServer',
@description = 'Profilo
per invio email
SQL'; |
Questo script usa la Store
Procedure sysmail_add_profile_sp
per aggiungere un profilo.
La tabella in cui verrà
creato l'account è
msdb.dbo.sysmail_profile.
Associazione Profilo
<=> Account [SCRIPT]
--
===================================================
-- Author: Giulio
Martino
-- Create date:
05/12/2006
-- Description:Associa
un profilo all'account
-- ===================================================
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name =
'IsaServer',
@account_name =
'Isa_Log',
@sequence_number
= 1 ; |
Questo script usa la Stored
Procedure sysmail_add_profileaccount_sp
per aggiungere un profilo.
La tabella in cui verrà
creato l'account è
msdb.dbo.sysmail_profileaccount.
Creazione Profilo Predefinito
[SCRIPT]
--
===================================================
-- Author: Giulio
Martino
-- Create date:
05/12/2006
-- Description:
Crea il profilo
predefinito
-- ===================================================
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name =
'IsaServer',
@principal_name
= 'public',
@is_default = 1
; |
Questo script usa la Stored
Procedure sysmail_add_principalprofile_sp
per aggiungere un profilo.
La tabella in cui verrà
creato l'account è
msdb.dbo.sysmail_principalprofile.
@principal_name = 'public'
indica la visibilità
del profilo.
Invio Email [SCRIPT]
--
===================================================
-- Author: Giulio
Martino
-- Create date:
05/12/2006
-- Description:
Invia una email
di prova
-- ===================================================
use master
EXEC msdb.dbo.sp_send_dbmail
@profile_name =
'IsaServer',
@recipients = <indirizzo
email>,
@body = 'Test della
nuova funzionalità
di SQL Server 2005',
@subject = 'Test
SQL Email' |
Questo script usa la Stored
Procedure sp_send_dbmail
inviare una email. La
email viene inserita nella
tabella dbo.sysmail_mailitems.
Importante: gli
script elencati devono
essere eseguiti nell'ordine
in cui li trovate. Dopo
di che tramite lo script
di esempio - Invio
Email - sarà
possibile inviare tutte
le email che vogliamo.
Nota alla versione
Express: ad ogni esecuzione
dello script di invio
email verrà generato
un errore simile a questo
:
| The
activated proc [dbo].[sp_sysmail_activate]
running on queue
msdb.dbo.ExternalMailQueue
output the following:
'Impossibile creare
il processo DatabaseMail90.exe.
Esecuzione dell'API
'CreateP' |
Il processo non puo'
essere creato perché
ci manca il componente
esterno DataBaseMail90.exe,
che altro non è
che un SMTP client, con
qualche funzioncina in
più.
Per ovviare a questo problema
ci sono una miriade di
soluzioni:
1) Sostituire la chiamata
al componente esterno
DataBaseMail90.exe con
un proprio componente
2) Crearsi un programmino
SMTP Client (che gira
come servizio) che interroga
la tabella dbo.sysmail.mailitems
e provvede ad inviare
le email presenti, provvedendo
alla loro cancellazione
in caso di invio riuscito.
3) Creare un trigger
sulla tabella dbo.sysmail.mailitems
che, in caso di insert,
esegue una funzione che
fa una chiamata al nostro
client SMTP……ed
altro ancora!!
Integrazione
con ISA Server
Con questi strumenti possiamo
sfruttare al meglio i
LOG che ISA server mette
a disposizione.
Sul forum di ISAServer.it
- http://www.isaserver.it/forum
- e sul newsgroup italiano
e americano dedicato ad
ISA Server ho visto spesso
postare la domanda: Come
faccio a vedere tutti
i siti visitati da un
utente? Su questa stessa
linea molte altre domande....
...La risposta? Utilizzando
Stored Procedure e Query.
Per schematizzare una
risposta alla domanda
precedente, possiamo pensare
ad esempio ad una stored
procedure a cui passiamo,
come parametri, <Nome
Utente> e <Range
di Date>, così
che crei una tabella temporanea
dove inserisca tutte le
operazioni fatte con il
browser dall'utente -
< Nome Utente> -
leggendo i dati dalla
tabella del WebProxyLOG,
e ci invii una mail quando
ha finito. Volendo possiamo
esportare il tutto in
XML...
Esempio di Query
| SELECT
[ClientIP] |
|
,[ClientUserName]
,[ClientAgent]
,[ClientAuthenticate]
,[logDate]
,[logTime]
,[service]
,[servername]
,[referredserver]
,[DestHost]
,[DestHostIP]
,[DestHostPort]
,[processingtime]
,[bytesrecvd]
,[bytessent]
,[protocol]
,[transport]
,[operation]
,[uri]
,[mimetype]
,[objectsource]
,[resultcode]
,[CacheInfo]
,[rule]
,[FilterInfo]
,[SrcNetwork]
,[DstNetwork]
,[ErrorInfo]
,[Action] |
FROM
[IISLOG].[dbo].[WebProxyLog]
WHERE ClientUserName
= <Nome
Utente>
and logDate
= CONVERT(DATETIME,
'2006-12-13
00:00:00',
102) |
|
<Nome Utente> nella
forma DOMINIO\UserName
- es. ISASERVER\user1
-
Esempio di Trigger
--
Creo il trigger
test sulla tabella
WebProxyLog
CREATE TRIGGER Test |
|
ON
ISALOG.dbo.webproxylog
AFTER INSERT |
AS
--
La variabile stato
server a contenere
il valore
-- del campo action
dopo che l'operazione
Insert
-- è terminata
declare @STATO varchar(32)
-- Assegno la variabile
STATO
SELECT @STATO =
webproxylog.[Action]
from webproxylog
-- Controllo il
contenuto e se è
Denied
-- invio una email
all'admin della
rete
if (@STATO = 'Denied')
BEGIN |
| |
EXEC
msdb.dbo.sp_send_dbmail
| |
@profile_name
= 'IsaServer',
@recipients
= 'admin@miodominio.it',
@body = 'Intercettato
Denied in
WebProxyLog',
@subject =
'ISASERVER
: ATTENZIONE!!'; |
|
END
GO |
….
Il resto lo lascio a voi.
Note: Stored
Procedure, Database,
Email si trovano
tutte nella sezione Stored
Procedure di sistema
database MSDB.
Tabelle, Database, Email
si trovano tutte nella
sezione Tabelle di
sistema del database
MSDB.
Buon
Lavoro con ISAServer.it
Giulio Martino
ISAServer.it
ed i suoi Autori non sono
responsabili per danni
di qualsiasi tipo - inclusi
danni per perdita o mancato
guadagno, interruzione
dell'attivita', perdita
di informazioni commerciali
o altre perdite pecuniarie
- derivanti o correlati
all'utilizzo o all'incapacita'
di utilizzo delle informazioni
e degli esempi riportati
- per quanto testati e
funzionanti -. Le informazioni
e gli script sono "cosi'
come sono", senza
garanzia di nessun tipo.
L'intero rischio derivante
dall'uso delle informazioni
e degli script di esempio
e' interamente a proprio
carico. L'utilizzo è
consentito solo accettando
le presenti condizioni.
|
|
| |
|
|
| |
|
|
|
|
