Informazioni di riferimento sulle istruzioni SQL per i report di Configuration Manager

Durante la creazione di report di Configuration Manager è possibile usare molte istruzioni microsoft SQL Server utili, che vengono brevemente descritte in questa sezione. Per seguire questa discussione, è necessario disporre di un livello di base di conoscenza dell'istruzione di query SQL e della possibilità di scrivere query come le seguenti:

SELECT Name, Comment, CollectionID

FROM v_Collection

WHERE Name LIKE 'All Windows%'

ORDER BY Name

Per informazioni su come scrivere query di base, vedere la documentazione SQL Server.

Funzioni di aggregazione

Le funzioni di aggregazione, ad esempio SUM, AVG, COUNT, COUNT(*), MAX e MIN, generano valori di riepilogo nei set di risultati della query. Una funzione di aggregazione (ad eccezione di COUNT(*)) elabora tutti i valori selezionati in una singola colonna per produrre un singolo valore di risultato. Le funzioni di aggregazione possono essere applicate a tutte le righe di una visualizzazione, a un subset della vista specificata da una clausola WHERE o a uno o più gruppi di righe nella visualizzazione. Quando viene applicata una funzione di aggregazione, viene generato un singolo valore da ogni set di righe.

Importante

Tenere presente che i valori NULL non sono inclusi nei risultati aggregati. Ad esempio, se sono presenti 100 record e 8 di essi hanno un valore di colonna NULL per la proprietà che si sta contando, il conteggio restituirà solo 92 risultati.

Un esempio di utilizzo della funzione di aggregazione COUNT(*) viene visualizzato nella query seguente (dai client Count per ogni report predefinito del sito) e nel set di risultati di esempio.

SELECT v_Site.SiteCode, v_Site.SiteName, v_Site.ReportingSiteCode,

Count(SMS_Installed_Sites0) AS 'Count'

FROM v_Site, v_RA_System_SMSInstalledSites InsSite

WHERE v_Site.SiteCode = InsSite.SMS_Installed_Sites0

GROUP BY SiteCode, SiteName, ReportingSiteCode

ORDER BY SiteCode

Codicesito	Sitename	ReportingSiteCode	Conteggio
ABC	Sito ABC		928
123	123 Sito	ABC	1010

Funzioni data e ora

Molti report predefiniti usano le funzioni Data e ora. Le funzioni più comuni usate sono GETDATE, DATEADD, DATEDIFF e DATEPART.

GETDATE ()

La funzione GETDATE produce la data e l'ora correnti in SQL Server formato interno per i valori datetime. GETDATE accetta il parametro NULL ().

L'esempio seguente restituisce la data e l'ora di sistema correnti:

SELECT GETDATE()

(nessun nome di colonna)
2005-05-29 10:10:03.001

DATEADD (datepart, number, date)

La funzione DATEADD restituisce un nuovo valore datetime basato sull'aggiunta di un intervallo alla data specificata.

Datepart è il parametro che specifica in quale parte della data restituire un nuovo valore (ad esempio, anno, mese, giorno, ora, minuto e così via), number è il valore utilizzato per incrementare datepart e date è la data di inizio.

L'esempio seguente restituisce una data di due giorni a partire dal 29 maggio 2005:

SELECT DATEADD([day], 2, '2005-05-29 10:10:03.001')

(nessun nome di colonna)
2005-05-31 10:10:03.001

DATEDIFF (datepart , startdate , enddate)

La funzione DATEDIFF restituisce il numero di limiti di data e ora incrociati tra due date specificate.

Datepart è il parametro che specifica in quale parte della data restituire un nuovo valore (ad esempio, anno, mese, giorno, ora, minuto e così via), startdate è la data di inizio, enddate è la data di fine.

L'esempio seguente restituisce il numero di minuti tra la prima e la seconda data:

SELECT DATEDIFF (minute, '2005-05-29 10:10:03.001',

'2005-06-12 09:28:11.111')

(nessun nome di colonna)
20118

DATEPART (datepart , date)

La funzione DATEPART restituisce un numero intero che rappresenta il datepart specificato della data specificata.

Datepart è il parametro che specifica la parte della data da restituire e la data è la data specificata.

L'esempio seguente restituisce il mese nella data specificata:

SELECT DATEPART (month, '2005-05-29 10:10:03.001')

(nessun nome di colonna)
5

Combinazione di funzioni di data e ora

È tipico usare una combinazione delle funzioni Data e ora nei report di Configuration Manager.

L'esempio seguente restituisce la data e l'ora correnti (2005-05-29 10:10:03.001 in questo esempio) meno 100 giorni:

SELECT DATEADD([day], - 100, GETDATE())

(nessun nome di colonna)
2005-02-18 10:10:03.001

Query di esempio con funzioni di data e ora

La query seguente restituisce il conteggio totale dei messaggi di stato per un periodo di un giorno. In questa query vengono usate le funzioni COUNT, GETDATE e DATEADD, nonché l'operatore logico BETWEEN e le clausole GROUP BY e ORDER BY.

SELECT SiteCode, MessageID, COUNT(MessageID) AS [count],

GETDATE() AS [End Date]

FROM vStatusMessages

WHERE ([Time] BETWEEN DATEADD([day], -1, GETDATE()) AND GETDATE())

AND (MessageID BETWEEN '0' AND '10000')

GROUP BY SiteCode, MessageID

ORDER BY SiteCode, MessageID

Codice sito	MessageID	Conteggio	Data di fine
ABC	500	190	2005-05-29 10:10:03.001
ABC	501	130	2005-05-29 10:10:03.001
ABC	502	190	2005-05-29 10:10:03.001
ABC	1105	85	2005-05-29 10:10:03.001
ABC	1106	5	2005-05-29 10:10:03.001

UNISCE

Per creare report efficaci in Configuration Manager, è necessario comprendere come unire visualizzazioni diverse per ottenere i dati previsti. Esistono tre tipi di join: interno, esterno e incrociato. Esistono inoltre tre tipi di outer join: left, right e full. Il self-join usa uno dei join precedenti, ma unisce i record dalla stessa visualizzazione.

Inner join

In un inner join, i record di due viste vengono combinati e aggiunti ai risultati di una query solo se i valori dei campi aggiunti soddisfano determinati criteri specificati. Se si usa un inner join usando ResourceID per unire le visualizzazioni v_R_System e v_GS_WORKSTATION_STATUS , il risultato sarà un elenco di tutti i sistemi e la data dell'ultima analisi hardware.

SELECT v_R_System.Netbios_Name0 AS MachineName,

v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan]

FROM v_R_System INNER JOIN v_GS_WORKSTATION_STATUS

ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

Nome computer	Ultima analisi HW
Client1	2005-05-29 10:10:03.001
Client3	2005-06-12 09:28:11.110

Outer join

Un outer join restituisce tutte le righe delle visualizzazioni unite indipendentemente dal fatto che esista o meno una riga corrispondente tra di esse. La clausola ON integra i dati anziché filtrarli. I tre tipi di outer join (left, right e full) indicano l'origine dei dati principale. I outer join possono essere particolarmente utili quando si dispone di valori NULL in una visualizzazione.

Outer join a sinistra

Quando si usa un left outer join per combinare due visualizzazioni, tutte le righe nella visualizzazione a sinistra vengono incluse nei risultati. Nella query seguente le visualizzazioni v_R_System e v_GS_WORKSTATION_STATUS vengono unite in join usando l'outer join sinistro. La vista v_R_System è la prima vista elencata nella query, che la rende la visualizzazione a sinistra. Il risultato includerà un elenco di tutti i sistemi e la data dell'ultima analisi hardware. A differenza dell'inner join, i sistemi che non sono stati analizzati per l'hardware verranno comunque elencati con un valore NULL (come illustrato nel set di risultati).

SELECT v_R_System.Netbios_Name0 AS MachineName,

v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan]

FROM v_R_System LEFT OUTER JOIN v_GS_WORKSTATION_STATUS

ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

Nome computer	Ultima analisi HW
Client1	2005-05-29 10:10:03.001
Client2	NULL
Client3	2005-06-12 09:28:11.110

Outer join a destra

Un right outer join è concettualmente lo stesso di un outer join sinistro, ad eccezione del fatto che tutte le righe della visualizzazione a destra sono incluse nei risultati.

Outer join completo

Un outer join completo recupera tutte le righe da entrambe le viste unite. Restituisce tutte le righe abbinate in cui la condizione di join è true, oltre alle righe non associate di ogni vista concatenate con righe NULL dell'altra visualizzazione. In genere non si vuole usare questo tipo di outer join.

Cross join

Un cross join restituisce il prodotto di due visualizzazioni, non la somma. Ogni riga nella visualizzazione a sinistra viene confrontata con ogni riga nella visualizzazione a destra. Si tratta del set di tutte le possibili combinazioni di righe, senza alcun filtro. Tuttavia, se si aggiunge una clausola WHERE, un cross join funziona come inner join, usa la condizione per filtrare tutte le possibili combinazioni di righe fino a quelle desiderate.

Self-join

Un self-join usa uno qualsiasi dei tipi join precedenti, ma è una vista unita a se stessa. Nei diagrammi di database, un self join è denominato relazione riflessiva.

PAROLA CHIAVE NOT IN

Le sottoquery con la parola chiave NOT IN sono molto utili per trovare informazioni su un set di dati che non soddisfa determinati criteri. Nell'esempio seguente la query restituisce il nome NetBIOS di tutti i computer che NON hanno Notepad.exe installato. È innanzitutto necessario creare una query in grado di rilevare tutti i computer in cui è installato il file selezionato come indicato di seguito:

SELECT DISTINCT v_R_System.Netbios_Name0

FROM v_R_System INNER JOIN v_GS_SoftwareFile

ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)

WHERE v_GS_SoftwareFile.FileName = 'Notepad.exe'

Dopo aver confermato che nella prima query vengono visualizzati tutti i computer in cui è installato Notepad.exe, l'istruzione della sottoquery seguente userà la parola chiave NOT IN per trovare tutti i nomi di computer in cui non è installato il file Notepad.exe:

SELECT DISTINCT Netbios_Name0

FROM v_R_System

WHERE Netbios_Name0 NOT IN

(SELECT DISTINCT v_R_System.Netbios_Name0

FROM v_R_System INNER JOIN v_GS_SoftwareFile

ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)

WHERE v_GS_SoftwareFile.FileName = 'Notepad.exe')

ORDER by Netbios_Name0

Vedere anche

Usare Progettazione query per scrivere istruzioni SQL del report per Configuration Manager report