 Rank: Administration Medals:  Joined: 3/28/2014(UTC) Posts: 2,862  Thanks: 3 times Was thanked: 351 time(s) in 303 post(s)
|
We pass parameters into the following template to delete Ended sessions: Code:
DELETE FROM
Session
LEFT OUTER JOIN SessionEvent se ON Session.SessionID = se.SessionID AND se.EventType = @A
LEFT OUTER JOIN SessionConnectionEvent sce ON Session.SessionID = sce.SessionID AND sce.EventType = @A
WHERE
SessionType = @B
AND (se.Time < @C OR sce.Time < @C)
Where @A is SessionEventType.EndedSession, @B is the type of Session (Support, Meet, Access) and @C is the daysAgo. To delete SessionConnections we use the following: Code:
DELETE FROM
SessionConnection
INNER JOIN Session ON SessionConnection.SessionID = Session.SessionID
LEFT OUTER JOIN SessionConnectionEvent ce ON SessionConnection.SessionID = ce.SessionID AND SessionConnection.ConnectionID = ce.ConnectionID AND ce.EventType = @A
LEFT OUTER JOIN SessionConnectionEvent de ON SessionConnection.SessionID = de.SessionID AND SessionConnection.ConnectionID = de.ConnectionID AND de.EventType = @B
LEFT OUTER JOIN SessionConnectionEvent ee ON SessionConnection.SessionID = ee.SessionID AND SessionConnection.ConnectionID = ee.ConnectionID AND ee.EventType = @C
WHERE
SessionType = @D
AND (ce.ConnectionID IS NULL OR de.Time < @E)
AND ee.ConnectionID IS NULL
AND ProcessType IN ({this.connection.GetInClauseInnerSql(connectionTypesToDelete)})"
WHERE @A is SessionEventType.Connected, @B is SessionEventType.Disconnected, @C is SessionEventType.EndedSession, @D is the type of Session (Support, Meet, Access), and @E is the daysAgo value. Also the GetInClauseInnerSql method returns the ProcessType which is either Guest or Host. To delete Session Events (Activity), we use: Code:
DELETE FROM
{tableName}
INNER JOIN Session ON {tableName}.SessionID = Session.SessionID
WHERE
SessionType = @A
AND Time < @B
AND EventType IN ({this.connection.GetInClauseInnerSql(eventTypesToDelete)})"
Where tableName is either SessionEvent or SessionConnectionEvent, @A is the SessionType (Support, Meet, Access), @B is the daysAgo, and EventType is derived from the following enum: Code:
public enum SessionEventType
{
[EnumMember(Value = "n")]
None = 0,
[EnumMember(Value = "c")]
Connected = 10,
[EnumMember(Value = "d")]
Disconnected = 11,
[EnumMember(Value = "cs")]
CreatedSession = 20,
[EnumMember(Value = "e")]
EndedSession = 21,
[EnumMember(Value = "ij")]
InitiatedJoin = 30,
[EnumMember(Value = "in")]
InvitedGuest = 31,
[EnumMember(Value = "an")]
AddedNote = 32,
[EnumMember(Value = "qr")]
QueuedReinstall = 40,
[EnumMember(Value = "qu")]
QueuedUninstall = 41,
[EnumMember(Value = "qi")]
QueuedInvalidateLicense = 42,
[EnumMember(Value = "qw")]
QueuedWake = 43,
[EnumMember(Value = "qc")]
QueuedCommand = 44,
[EnumMember(Value = "qm")]
QueuedMessage = 45,
[EnumMember(Value = "qg")]
QueuedGuestInfoUpdate = 46,
[EnumMember(Value = "qt")]
QueuedTool = 47,
[EnumMember(Value = "qet")]
QueuedElevatedTool = 101,
[EnumMember(Value = "qd")]
QueuedForceDisconnect = 48,
[EnumMember(Value = "qa")]
QueuedInstallAccess = 49,
[EnumMember(Value = "que")]
QueuedUninstallAndEnd = 100,
[EnumMember(Value = "pr")]
[Obsolete("Use QueuedReinstall and NeedsProcessing attribute")]
ProcessedReinstall = 50,
[EnumMember(Value = "pu")]
[Obsolete("Use QueuedUninstall and NeedsProcessing attribute")]
ProcessedUninstall = 51,
[EnumMember(Value = "pi")]
[Obsolete("Use QueuedInvalidateLicense and NeedsProcessing attribute")]
ProcessedInvalidateLicense = 52,
[EnumMember(Value = "pw")]
[Obsolete("Use QueuedWake and NeedsProcessing attribute")]
ProcessedWake = 53,
[EnumMember(Value = "pc")]
[Obsolete("Use QueuedCommand and NeedsProcessing attribute")]
ProcessedCommand = 54,
[EnumMember(Value = "pm")]
[Obsolete("Use QueuedMessage and NeedsProcessing attribute")]
ProcessedMessage = 55,
[EnumMember(Value = "pg")]
[Obsolete("Use QueuedGuestInfoUpdate and NeedsProcessing attribute")]
ProcessedGuestInfoUpdate = 56,
[EnumMember(Value = "pt")]
[Obsolete("Use QueuedTool and NeedsProcessing attribute")]
ProcessedTool = 57,
[EnumMember(Value = "pd")]
[Obsolete("Use QueuedForceDisconnect and NeedsProcessing attribute")]
ProcessedForceDisconnect = 58,
[EnumMember(Value = "mn")]
ModifiedName = 60,
[EnumMember(Value = "mp")]
ModifiedIsPublic = 61,
[EnumMember(Value = "mc")]
ModifiedCode = 62,
[EnumMember(Value = "mh")]
ModifiedHost = 63,
[EnumMember(Value = "mu")]
ModifiedCustomProperty = 64,
[EnumMember(Value = "rc")]
RanCommand = 70,
[EnumMember(Value = "sm")]
SentMessage = 71,
[EnumMember(Value = "sp")]
SentPrintJob = 80,
[EnumMember(Value = "rp")]
ReceivedPrintJob = 81,
[EnumMember(Value = "ct")]
CopiedText = 82,
[EnumMember(Value = "cf")]
CopiedFiles = 83,
[EnumMember(Value = "df")]
DraggedFiles = 84,
[EnumMember(Value = "rf")]
RanFiles = 85,
[EnumMember(Value = "tf")]
SentFiles = 86,
}
|