No banner in farm
iscriviti alla newsletter

ISAserver.it - No. 1 Unofficial European web site on Microsoft ISA Server / Forefront TMG

Home di ISAserver.it
Google
 
Articoli
Autori di ISAserver.it
Forum Tecnico Iscriviti al feed RSS del forum di ISAserver.it
Blog di Luca Conte Iscriviti al feed RSS del blog di ISAserver.it
Software per ISA Server
Soluzioni Hardware per ISA Server
Eventi Tecnici e Formazione
Libreria di ISAserver.it
Contatti
Consigliati da ISAserver.it

No banner in farm
WORKBOOK

No banner in farm
Quale prodotto utilizzi

Quale prodotto utilizzi

VMexperts.org
Community tecnica italiana sulla Virtualizzazione
VOIPexperts.it
Community tecnica italiana sul VOIP
 
 

<< Torna all'elenco degli Articoli
 
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:
    http://italy.mvps.org/MVPs/lbianchi/art_UDF.htm
http://www.sqlserverfaq.com/
http://www.microsoft.com/italy/sql/default.mspx
https://www.microsoftelearning.com/sqlserver2005/
http://www.microsoft.com/italy/msdn/risorsemsdn/eventi/webcast/passati/SQL.mspx
http://msdn2.microsoft.com/it-it/library/ms189799.aspx

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.

 
 
 
 
No banner in farm


ISAserver.it - No. 1 Unofficial European web site on Microsoft ISA Server / Forefront TMG

Pubblicità su ISAserver.it - Note d'uso - Privacy
Copyright© 2004-2008 Luca Conte Tutti i diritti riservati.