Verwaiste Inventurdaten finden

Heute ist es passiert: Der Report “Count operating systems and service packs” sagte mir, es gebe noch 20 Windows Server 2003 mit Service Pack 1, doch als ich auf den Report-Link zu “Computers with a specific operating system and service pack” klickte, wurden nur 13 aufgelistet. Ein Fehler in den Standard-Reports? Ich habe also in die Report-Queries hineingeschaut und konnte feststellen, dass der “Count…”-Report nur den View v_GS_OPERATING_SYSTEM bemüht, der detaillierte Report “Computers with…” ist aber zusätzlich per inner join mit v_R_System verknüpft, was die Anzahl der Datensätze auf Computer begrenzt, die in beiden Tabellen vorhanden sind.

Damit war klar, dass in der Inventur-Tabelle Operating_System_DATA verwaiste Einträge vorhanden sein mussten, also Einträge von Clients, die bereits gelöscht wurden. Das habe ich denn auch gleich noch mit dem Query Analyzer überprüft:

SELECT * FROM Operating_System_DATA WHERE MachineID NOT IN
  (SELECT ItemKey FROM System_DISC)

Und da waren sie, die 7 verwaisten Einträge, die ich denn auch umgehend gelöscht habe:

DELETE FROM Operating_System_DATA WHERE MachineID NOT IN
  (SELECT ItemKey FROM System_DISC)

Eigentlich sollte der Trigger System_DISC_del dafür sorgen, dass beim Löschen einer Resource aus System_DISC alle dazugehörigen Inventurdaten gelöscht werden, aber in Einzelfällen kann die Trigger-Ausführung abgebrochen worden sein.

Bei der Gelegenheit habe ich natürlich gleich alle Inventur-Tabellen überprüft:

DECLARE @table varchar(128)
DECLARE Tables_Cursor CURSOR FOR
  SELECT SpecificTableName FROM GroupMap
  ORDER BY SpecificTableName
OPEN Tables_Cursor
FETCH NEXT FROM Tables_Cursor INTO @table
WHILE @@fetch_status = 0
BEGIN
  PRINT @table
  EXEC ('SELECT DISTINCT MachineID as MachineID_' + @table +
        ' FROM ' + @table + ' WHERE MachineID NOT IN
            (SELECT ItemKey FROM System_DISC)')
  FETCH NEXT FROM Tables_Cursor INTO @table
END
CLOSE Tables_Cursor
DEALLOCATE Tables_Cursor

Wenn man in der WHILE-Schleife das EXEC-Statement durch

  EXEC ('DELETE FROM ' + @table + ' WHERE MachineID NOT IN
    (SELECT ItemKey FROM System_DISC)')

ersetzt, kann man natürlich auch gleich alle verwaisten Daten löschen. Soviel Mut hatte ich heute nicht. Ich habe lieber für jede Tabelle, in der ich verwaiste Einträge gefunden habe, das oben gezeigte DELETE ausgeführt. (Keine Panik: Die Anzahl der gelöschten Zeilen ist manchmal größer als die der mit SELECT DISTINCT MachineID gefundenen Zeilen, denn manche Tabellen, z.B. Add_Remove_Programs_DATA, haben ja mehrere Einträge pro MachineID.)

In den History-Tabellen sollten keine verwaisten Einträge übrig bleiben, wenn der SMS-Datenbankwartungstask “Delete Aged Inventory History” regelmäßig läuft. Wer dies dennoch prüfen möchte, ersetze im oberen Batch einfach die Zeile

  SELECT SpecificTableName FROM GroupMap
  ORDER BY SpecificTableName

durch

  SELECT HistoryTableName FROM GroupMap
  WHERE datalength(HistoryTableName)>0
  ORDER BY HistoryTableName

Vielleicht finden sich auch in den Discovery-Arraytabellen verwaiste Einträge:

DECLARE @table varchar(128)
DECLARE Tables_Cursor CURSOR FOR
  SELECT DISTINCT ArrayTableName FROM DiscPropertyDefs
  WHERE datalength(ArrayTableName) > 0
  ORDER BY ArrayTableName
OPEN Tables_Cursor
FETCH NEXT FROM Tables_Cursor INTO @table
WHILE @@fetch_status = 0
BEGIN
  PRINT @table
  EXEC ('SELECT DISTINCT ItemKey as ItemKey_' + @table +
        ' FROM ' + @table + ' WHERE ItemKey NOT IN
            (SELECT ItemKey FROM System_DISC UNION
             SELECT ItemKey FROM User_DISC UNION
             SELECT ItemKey FROM User_Group_DISC)')
  FETCH NEXT FROM Tables_Cursor INTO @table
END
CLOSE Tables_Cursor
DEALLOCATE Tables_Cursor

Es gibt noch weitere Tabellen, die eigentlich durch den Trigger System_DISC_del bereinigt werden sollten (in Klammern der Name der Spalte, die gegen ItemKey aus System_DISC verglichen werden muss):

SoftwareInventory (ClientID)
SoftwareInventoryStatus (ClientID)
Collection_Rules (MachineID)
MachineIdGroupXRef (MachineID)
MeterData (SystemItemKey)
MonthlyUsageSummary (SystemItemKey)
CollectionMembers (MachineID)
ClientOfferStatus (ItemKey)
ClientUpdateOfferStatus (ItemKey)
PatchStatusSummary (ItemKey)
ClientKeyData (MachineID)

Viel Spaß beim Basteln der Abfragen nach dem Muster:

SELECT * FROM <table> WHERE <key_column> NOT IN
  (SELECT ItemKey FROM System_DISC UNION
   SELECT ItemKey FROM User_DISC UNION
   SELECT ItemKey FROM User_Group_DISC)

Eine letzte Anmerkung für heute: Das Abfragen mehrerer Tabellen geht auch mit der undokumentierten Stored Procedure sp_foreachtable:

EXEC sp_msforeachtable
 @command1='SELECT DISTINCT MachineID AS ''?.MachineID'' FROM ?
  WHERE MachineID NOT IN (SELECT ItemKey FROM System_DISC)',
 @whereand='AND o.name in (SELECT SpecificTableName
    FROM GroupMap) ORDER BY o.name'

Kommentarfunktion ist deaktiviert