question

brendagrossnickle-6913 avatar image
0 Votes"
brendagrossnickle-6913 asked arulaswin-7289 answered

Reading a log file and Matching up the Start and End rows

I have a process that writes to a cs_process_log. It inserts a START row at the beginning of certain processes and then an END row at the end of the process. I want to match up the START with END rows to get a minutes DATEDIFF. Under certain conditions a process might insert multiple MESSAGEs between the START and END rows. I want to show these messages also and want to catch any START rows that do not have a matching END row, which would mean a failed condition.

I have played with it for awhile now. but have not been able to come up with a working solution. Any help would be appreciated.

This is the output that i am hoping to get. (it is in desc order)


120966-log-screen-print.jpg




 drop table cs_process_log
 create table cs_process_log (recid int identity, process_name varchar(max), category varchar(max), run_datetime datetime, notes varchar(max))
    
 insert into cs_process_log
 select process_name, category, run_datetime, notes from 
 (values 
 ('SECURITY', 'START', '2021-08-01 14:35:39.067', ''),
 ('SECURITY', 'END',   '2021-08-01 14:50:39.067', ''),
 ('DASHBOARD', 'START', '2021-08-01 15:01:39.067', ''),
 ('DASHBOARD', 'END',   '2021-08-01 15:08:39.067', ''),
    
 ('SECURITY', 'START', '2021-08-01 16:35:39.067', ''),
 ('SECURITY', 'END',   '2021-08-01 16:50:39.067', ''),
 ('DASHBOARD', 'START', '2021-08-01 17:01:39.067', ''),
 ('DASHBOARD', 'END',   '2021-08-01 17:08:39.067', ''),
    
 ('SECURITY', 'START', '2021-08-02 14:35:39.067', ''),
 ('SECURITY', 'END',   '2021-08-02 14:50:39.067', ''),
 ('DASHBOARD', 'START', '2021-08-02 15:01:39.067', ''),
 ('DASHBOARD', 'MIDDLE STEP2', '2021-08-02 15:03:39.067', ''),
    
 ('SECURITY', 'START', '2021-08-03 14:35:39.067', ''),
 ('SECURITY', 'END',   '2021-08-03 14:50:39.067', ''),
 ('DASHBOARD', 'START', '2021-08-03 15:01:39.067', ''),
 ('DASHBOARD', 'MIDDLE STEP3', '2021-08-03 15:03:39.067', ''),
 ('DASHBOARD', 'END',   '2021-08-03 15:08:39.067', ''),
    
 ('SECURITY', 'START', '2021-07-25 14:35:39.067', ''),
 ('SECURITY', 'END',   '2021-07-25 14:50:39.067', ''),
 ('DASHBOARD', 'START', '2021-07-25 15:01:39.067', ''),
 ('DASHBOARD', 'END',   '2021-07-25 15:08:39.067', ''))
  a (process_name, category, run_datetime, notes)
sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered brendagrossnickle-6913 commented

Hi @brendagrossnickle-6913,

Welcome to Microsoft Q&A!

Please refer below query and check whether it is working.

 ;with cte as (
 SELECT *,COUNT(CASE category WHEN 'Start' THEN 1 END) OVER (PARTITION BY process_name ORDER BY recid) AS Grp
 FROM cs_process_log
 WHERE category IN ('Start','End'))
 ,cte1 as (
 SELECT process_name,run_datetime, category,
        MAX(CASE category WHEN 'Start' THEN run_datetime END) over (partition by process_name,Grp) AS run_datetime_start,
        MAX(CASE category WHEN 'End' THEN run_datetime END) over (partition by process_name,Grp) AS run_datetime_end
 FROM cte
 union 
 SELECT process_name,run_datetime,category,run_datetime,NULL
 from cs_process_log
 where category not IN ('Start','End'))
 select distinct DATEDIFF(MINUTE,run_datetime_start,run_datetime_end) MINUTES_RUN,
 process_name,category,run_datetime_start,run_datetime_end from cte1
 where category<>'end'
 order by run_datetime_start desc

Output:
121053-output.png

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


output.png (20.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you so much. That first line was the missing piece. That COUNT with the CASE statement was great.

SELECT *,COUNT(CASE category WHEN 'Start' THEN 1 END) OVER (PARTITION BY process_name ORDER BY recid) AS Grp

0 Votes 0 ·
arulaswin-7289 avatar image
0 Votes"
arulaswin-7289 answered

As we see, common table expressions (CTE in SQL Server) can provide us with a convenient way to query data similar to using tools like temp tables and subqueries. We have quick troubleshooting options with these where we can run select statements inside the wrapping of the CTE and we can create multiple statements to join together. Are these faster? In some cases, they may be, or they may be faster to troubleshoot – which could be important. Still, we should always test our queries to ensure that the way we’re running our query is the most optimal route.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

arulaswin-7289 avatar image
0 Votes"
arulaswin-7289 answered

That first line was the missing piece. That COUNT with the CASE statement was great.

SELECT *,COUNT(CASE category WHEN 'Start' THEN 1 END) OVER (PARTITION BY process_name ORDER BY recid) AS Grp

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.