Introduction

The Microsoft documentation tells us that the SessionId column, within ASPStateTempSessions, consists of the Session ID plus the App ID. Although accurate, the method used for this does not make it obvious.

Background

Our Developers needed to write a series of queries, to analyse the usage within ASPState – tracking the usage of this database to the applications.

In the default configuration, there are two tables we are interested in – ASPStateTempApplications and ASPStateTempSessions. The first table contains the application ID and the second has that value stored as part of the SessionId column – after some manipulation.

Detail

In this test example, we have two Applications within ASPStateTempApplications, with an AppId value of -2010518888 and 168339286.

Figure 1: The contents of the ASPStateTempApplications table

Within the table ASPStateTempSessions there are entries that show the session details. The SessioinId column is what we’re interested in.

Figure 2: The contents of the ASPStateTempSessions table

Methodology

At first, I tried to find the value of AppId in SessionId and of course, found no match.

Then I converted the AppId into hexadecimal format.

The value of 168339286 converts to A08A756.

Figure 3: One value converted to hex and located within SessionId

The negative number revealed a slight twist to this methodology – it does not use the entire hexadecimal value. Using the Windows calculator, when I changed the ‘QWORD’ to ‘DWORD’ the exact value matched. Therefore, the integer value is being converted to a double word hexadecimal value.

Figure 4: The Qword value

Figure 5: The DWord value

The SQL code to perform this conversion is:

Listing 1: SQL Code to convert AppId

SELECT AppId,

CONVERT(VARBINARY(16), AppId)

FROM dbo.ASPStateTempApplications;

Conclusion

The AppId is converted from an integer, to a VarBiinary(16) and then appended on the end of a unicode value, to end up in an nvarchar(88).

Somebody, at some time, thought that was a good design.

No, I don’t know why.

 

References

Session State Providers