Query to detect "lost sessions" on IIS Server

[lassesen]

We have an application that logs every page that a user obtains. It appears that sometimes that the IIS session for one user is lost (as opposed to App pool recycling...). I am trying to find evidence from splunk logging.

If a User is logged on(has a session) we have a "UserName" being logged. If it is missing and on a secure page (required login to get to it), then the user is logged off. We have cases where we have two records within seconds where the UserName appears to disappear (not recorded in a record that would log it).

So:

• "UserHostName" is used to find the client IP address

• "host" is the server address

• "date" is the date time

• "UserName" is missing if not logged in, missing if session is ended and not on Aspx="~/Login.aspx"

What I am wanting to do is from the UserName missing record (found already) MinDate (when it first appear), find the prior record if it is within 20 minutes (1200 seconds).

In TSQL, something like
Select UserHostName, DateDiff(m, Session.Date, NoSession.Date) FROM NoSession JOIN With Session ON NoSession.UserHostName=Session.UserHostName AND Session.Date < NoSession.Date
WHERE DateDiff(m, Session.Date, NoSession.Date) < 20

In other words --- find when the prior to NoSession that the last session occurred. I have tried using Transaction but without easy success.

[lguinn2]

I appreciate the TSQL, but it actually doesn't help me. You are using record names and fields, but not all of them are defined in your post. I'll propose some ideas here anyway...

sourcetype=iis OR whateverFindsTheEventsWithMissingUserNames 
| eval MissingDatetime=_time 
| fields session MissingDatetime
| join session [ search sourcetype=iis OR whateverFindsALLTheEventsThatShouldHaveUserNames ]
| sort session _time

This should give you the sessions where at least one event is missing its user name. To reduce it further, you could add this at the end

| eval secondsDiff = MissingDatetime - _time
| where secondsDiff >= 0 AND secondsDiff <= 1200

This would list all the events that were within the 20 minutes prior to the missing user name event. If this answer doesn't help, can you show a few lines of sample data?