Querying Application Logs (Please Help)

[JAPixley]

Our system spans multiple servers and the logs are stored in a central database. Each application flows through a decision tree, with each 'box' being a step. One of the things I am responsible for is tracking errors. The errors can happen at any step in the decision flow, which then defaults to step 800.20. I would like to be able to identify the step prior to 800.20 in each transaction without having to export the data into Excel and manipulating it manually. An added complication is that one of the applications has two different kinds of errors: one which ends up terminating the transaction and one that is merely noted while the transaction continues, 800.20 and 800.10 respectively.

I've got a query that can find, for a given application during a specified time range, the transactions with errors. This was pretty easy, but only half of what I need. What I am hoping you can help me with is this: I want to identify which transactions hit steps 800.10 and/or 800.20 and then, for each transaction, return only the step immediately prior to 800.10/800.20.

Thanks,
Alex

PS: This is what I have so far:

SELECT
DateTime, Priority, Application, MachineName, TransID, Msg
FROM
Logger
WHERE
TransID in (
SELECT
TransID
FROM
Logger
WHERE
Msg = 'Stat Event 800.20 updated'
AND Application = 'Orders'
AND DateTime LIKE '20091214%'
)
AND Msg LIKE '%Running Step%'
AND Application = 'Orders'
AND DateTime LIKE '20091214%'
ORDER BY
TransID, DateTime

 

[RyanEK]

Can you provide sample data with expected results?

 

[JAPixley]

Here is an example of a result set with four calls (each call starts at Step 1):

DateTime Svr Step
20100131085346688 VS01 Step 1
20100131085346703 VS01 Step 20
20100131085348188 VS01 Step 30
20100131085348281 VS01 Step 40
20100131085355282 VS01 Step 52
20100131085355282 VS01 Step 60
20100131085355360 VS01 Step 100.10
20100131085406236 VS01 Step 100.15
20100131085419675 VS01 Step 100.17
20100131085419800 VS01 Step 100.90
20100131085419800 VS01 Step 100.93
20100131085419878 VS01 Step 100.21
20100131085428613 VS01 Step 100.25
20100131085440614 VS01 Step 100.26
20100131085440692 VS01 Step 100.34
20100131085440692 VS01 Step 100.80
20100131085441177 VS01 Step 100.83
20100131085441192 VS01 Step 100.35
20100131085441255 VS01 Step 200.10.10.15
20100131085441271 VS01 Step 200.10.10.20.40
20100131085525400 VS01 Step 200.10.11
20100131085525415 VS01 Step 200.10.12
20100131085525493 VS01 Step 200.10.16.7
20100131085552933 VS01 Step 200.10.16.8
20100131085552933 VS01 Step 200.10.15.10
20100131085626296 VS01 Step 200.100.5
20100131085626311 VS01 Step 200.100.20.10
20100131085638390 VS01 Step 200.100.20.11
20100131085642422 VS01 Step 200.100.20.25
20100131085657798 VS01 Step 200.100.20.26
20100131085703658 VS01 Step 200.100.20.30
20100131085715066 VS01 Step 200.100.20.32
20100131085719097 VS01 Step 200.100.20.40
20100131085731005 VS01 Step 200.100.20.42
20100131085735130 VS01 Step 200.100.20.60
20100131085750084 VS01 Step 200.100.20.62
20100131085753866 VS01 Step 200.100.20.70
20100131085806117 VS01 Step 200.100.20.72
20100131085812290 VS01 Step 200.100.20.90
20100131085827947 VS01 Step 200.100.20.92
20100131085838870 VS01 Step 200.150.10
20100131085858887 VS01 Step 800.20
20100131085905451 VS01 Step 800.10
20100131085848687 VS06 Step 1
20100131085848703 VS06 Step 20
20100131085850203 VS06 Step 30
20100131085850296 VS06 Step 40
20100131085856468 VS06 Step 52
20100131085856484 VS06 Step 60
20100131085856562 VS06 Step 100.10
20100131085910843 VS06 Step 100.15
20100131085926125 VS06 Step 100.17
20100131085926265 VS06 Step 100.90
20100131085926265 VS06 Step 100.93
20100131085926359 VS06 Step 100.21
20100131085940921 VS06 Step 100.25
20100131085954953 VS06 Step 100.26
20100131085955109 VS06 Step 100.27
20100131085955109 VS06 Step 100.70.10
20100131090013671 VS06 Step 100.70.30
20100131090027796 VS06 Step 100.70.40
20100131090047828 VS06 Step 800.20
20100131090054406 VS06 Step 800.10
20100131104245843 VS01 Step 1
20100131104245859 VS01 Step 20
20100131104247359 VS01 Step 30
20100131104247437 VS01 Step 40
20100131104253046 VS01 Step 52
20100131104253062 VS01 Step 60
20100131104253140 VS01 Step 100.10
20100131104303906 VS01 Step 100.15
20100131104317250 VS01 Step 100.17
20100131104317531 VS01 Step 100.90
20100131104317546 VS01 Step 100.93
20100131104317656 VS01 Step 100.21
20100131104326796 VS01 Step 100.25
20100131104339531 VS01 Step 100.26
20100131104359562 VS01 Step 100.27
20100131104359578 VS01 Step 800.20
20100131104406140 VS01 Step 800.10
20100131111620703 VS07 Step 1
20100131111620718 VS07 Step 20
20100131111622234 VS07 Step 30
20100131111622359 VS07 Step 40
20100131111628921 VS07 Step 52
20100131111628921 VS07 Step 60
20100131111629000 VS07 Step 100.10
20100131111642875 VS07 Step 100.15
20100131111656765 VS07 Step 100.17
20100131111658390 VS07 Step 100.90
20100131111658390 VS07 Step 100.93
20100131111658468 VS07 Step 100.21
20100131111710593 VS07 Step 100.25
20100131111723718 VS07 Step 100.26
20100131111743734 VS07 Step 100.27
20100131111743734 VS07 Step 800.20
20100131111750312 VS07 Step 800.10