August 21, 2013 at 5:16 pm
Hi Experts,
We had a application that enters logs into database approximately for every half an hour.
Lets say if the apllication started at 1:01 and ended its at job 1:05, the next run of application logging entries will be started at 1:36. Because it calculates 30 mins from 1:05 i.e 30 mins from recent end time.. If the application again started logging some rows starting 1:36 to 1:42. The next logging instance will start at 2:12. The number of rows logged by application might be from 0 to 100(apprx)
Here the requirement is i have to send a email with that log entries for the last two loggings, for Ex: If i Set a job to run at 2:00 It should select the logging that occured between 1:01 to 1:05 and 1:35 to 1:42. How can i enable a job, which always selects recent two set of log entries .
Please help me in this.
Any input is appreciated
Thanks
August 22, 2013 at 7:29 am
SQListic (8/21/2013)
Hi Experts,We had a application that enters logs into database approximately for every half an hour.
Lets say if the apllication started at 1:01 and ended its at job 1:05, the next run of application logging entries will be started at 1:36. Because it calculates 30 mins from 1:05 i.e 30 mins from recent end time.. If the application again started logging some rows starting 1:36 to 1:42. The next logging instance will start at 2:12. The number of rows logged by application might be from 0 to 100(apprx)
Here the requirement is i have to send a email with that log entries for the last two loggings, for Ex: If i Set a job to run at 2:00 It should select the logging that occured between 1:01 to 1:05 and 1:35 to 1:42. How can i enable a job, which always selects recent two set of log entries .
Please help me in this.
Any input is appreciated
Thanks
You are going to have to provide more details here if you want an answer. What do these tables look like? Are you looking for two rows that have a start and end time or are there groups of rows?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2013 at 3:33 pm
The app should insert a "header" row into a different table when it starts. You can then use that table to pull the log entries.
If the log table contains rows from only that application, it would be as simple as:
SELECT *
FROM dbo.log_entries
WHERE
datetime >= ( SELECT MIN(start_datetime) FROM ( SELECT TOP (2) start_datetime FROM dbo.log_header /* WHERE app_name = 'whatever' */ ORDER BY start_datetime DESC ) AS subquery1 )
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 22, 2013 at 5:02 pm
Thank you Sean and Scott for your replies.
This is the query I am using currently... I created a SP with it and enabled in a job for every hour like 1:00, 2:00, 3:00 etc.... whenever the application runs randomly at lets say 1:57 and ends logging at 2:05. The job that ran at 2:00 is only selecting the entries that occured between 1:57 to 2:00. All I want to select data of last two set of logs. Each set of log might have multiple row entries.
sample output result data:
DateTime Text
2013-08-16 14:10:33.413count = 3
2013-08-16 14:10:34.073Success for 22
2013-08-16 14:10:34.390Success for 23
2013-08-16 14:10:35.913Success for 24
2013-08-16 14:44:33.413count = 4
2013-08-16 14:44:34.073Success for 25
2013-08-16 14:44:34.390Success for 26
2013-08-16 14:44:35.913Success for 27
2013-08-16 14:44:35.913Success for 28
--------------------------------------------------------
Query:
--------
SELECT
[DateTime]
,[Text]
FROM [MicrosoftLog].[dbo].[Log]
where
[LogApplicationID] in (select [LogApplicationID]
from
[MicrosoftLog].[dbo].[LogApplication]
where applicationname in
(
'VirtualMan',
'PhysicalMan')) and StartText<> 'count=0'
and
StartDateTime < GetDate() and StartDateTime > dateadd(minute, -60,
GetDate())
August 23, 2013 at 7:30 am
Again we can't really help much here because we don't have any details to work with. Keep in mind that we can't see your screen. We don't know your project, your table structures or what you are trying to do. All we have to work with is what you say is the desired output and a query that doesn't do what you want. How do you think we can possibly help you write a query that works from that? This is probably a pretty simple thing but until we have the ddl and few sample rows we are shooting in the dark.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply