Need help in adding several features to a sql script

Question

Need help in adding several features to a sql script

kaplan71

Hall of Fame

Points: 3634
More actions
March 3, 2016 at 9:03 am

#308963

Hello --
I am running a SQL script that is designed to query the whole log of a particular database. The syntax of the script is the following:
use RayStationMachineDB_472
select * from RS_AuditLog_
There are two 'features' that I want to add to the script, and I need help with the syntax. They are the following:
1. Limit the query to the past twenty-four hours.
2. If possible, filter the output so that only certain entries in the message field of the output are displayed.
The first item is the more important of the two, although having both would be helpful.
What would the correct syntax be to accomplish this?
Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

kaplan71 (3/3/2016)
Hello --
I am running a SQL script that is designed to query the whole log of a particular database. The syntax of the script is the following:
use RayStationMachineDB_472
select * from RS_AuditLog_
There are two 'features' that I want to add to the script, and I need help with the syntax. They are the following:
1. Limit the query to the past twenty-four hours.
2. If possible, filter the output so that only certain entries in the message field of the output are displayed.
The first item is the more important of the two, although having both would be helpful.
What would the correct syntax be to accomplish this?
Thanks.

you'll need to look at the columns, there's got to be a column like CreatedDate or LogDate to use for the filtering in the WHERE statement.

in the example below, the parenthesis are important, because you want to test multiple items, but still filter by date.

select * from RS_AuditLog_

WHERE CreatedDate >= DATEADD(hh,-24,getdate()) --twentyfour hours

AND (

[Message] LIKE '%failed%'

OR [Message] LIKE '%stopped%'

OR [Message] LIKE '%disconnected from server%'

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

Lowell's answer is excellent. Just understand the logic he used. He's taking 24 hours off the time. You could also just take one day off the time using the same syntax, just change the -24 to -1 and the hh to dd. You can read more about DATEADD here.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

kaplan71 Hall of Fame Points: 3634 More actions · Answer 3

Thanks Lowell --

I added the lines you suggested, and I am getting the information in question.

kaplan71 Hall of Fame Points: 3634 More actions · Answer 4

Hello --

I experimented with the DATEADD options, and I am also doing a six month, and one year check of the database.