September 21, 2010 at 8:31 am
ps. (9/21/2010)
may be you can write the move part in a seperate job that moves all zipped files. and run this "before" the the zipping job runs.i would have done something like this:
1. Job zip - that finds all trace files except current one and zip them asynchrounously in seperate zip files. ( the script i posted earlier does that)
2. job move - this job would move the zipped files one by one to target location. This will run syncronous.
what i have seen is the file which is getting zipped wont get the extension .zip unless it is complete; which means your move job will move only those files which are zipped completely. I hope you will use wildcard something like move files *.zip
Maybe I'm missing something in your response.
From my testing, a seperate "move job" makes no difference. My issue is that I cannot tell "when" the zipping is complete. Consequently, there is the risk of deleting a file that has not been zipped yet (and the script has deleted several unzipped files).
Currently, I have a kludge workaround in place which arbitrarily sleeps for 25 seconds between the zip call and the delete. Usually, this works because zipping a 200 GB file takes about 10-15 seconds. However, if the server gets bogged down, disk defragmentation starts up, antivirus decides to kick in, (insert your favorite "Murphy's Law" processing here), I suspect that the 25 second sleep will not be enough. If I could find a synchronous way to wait for the zip to finish, I think my current script would work fine.
September 21, 2010 at 8:38 am
shew (9/21/2010)[hr
From my testing, a seperate "move job" makes no difference. My issue is that I cannot tell "when" the zipping is complete. Consequently, there is the risk of deleting a file that has not been zipped yet (and the script has deleted several unzipped files).
I think there are two missing point:
1. I would MOVE the file from source to destination and not COPY to target and then DELETE.
2. I would put a wild card for moving, which means the script would move only those files having .zip extension and wont touch others.
let me try to write a sample script for point 2. but that would be in vbscript. u'll need to re-write in powershell :-p
September 21, 2010 at 8:47 am
Ok. this is what i found on net.
modified it a bit.
Dim oFS, oFile, sFile
Set oFS = WScript.CreateObject("Scripting.FileSystemObject")
Const sSourceFdr = "C:\test1\"
Const sDestFdr = "D:\test2\"
For Each oFile In oFS.GetFolder(sSourceFdr).Files ' read each file from source folder
if instr(1, oFile.Name, ".zip", 1) > 0 then
{do your file move}' This will move .zip files only. not others.
end if
Next
September 21, 2010 at 8:56 am
shew (9/21/2010)
EdVassie (9/21/2010)
Why are you looking at C2 auditing? This is an obsolete standard that does not match current compliance requirements. IMHO you should be looking at Common Criteria, which is the current standard.I'm a contractor. It is my understanding that C2 Level Auditing is a specific requirement at this site. Just trying to keep the customer happy (and operational, without full disks). 🙂
I currently work as a contractor for the military and if you are following the DoD STIG checklist, C2 Auditing is not required. The checklist has a custom server-side trace that can be used in place of C2 Auditing. This will allow you to move the trace file to your desired location. As well the custom trace does not capture all the information that C2 Audit does.
On a side note as well in the checklist C2 auditing actually becomes a finding in that it and the default trace collect to much information.
Anyway, I'm looking over your PowerShell script as well (:-D just to learn) and will post if I come up with anything.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 21, 2010 at 9:08 am
I think there are two missing point:
1. I would MOVE the file from source to destination and not COPY to target and then DELETE.
2. I would put a wild card for moving, which means the script would move only those files having .zip extension and wont touch others.
Sorry, I don't see how this helps me. The 200 MB source files are named .trc. When zipped, they shrink to 5 MB a piece. At some point in the process, they need to be zipped to be manageable. Unfortunately, zip, under PowerShell, leaves the original file in place, and it is the *original* file that I am deleting to regain disk space. If the zip is not finished yet, I inadvertently whack a file that was not zipped. The destination .zip file stays in the SQL Server data directory (or will be moved to a network location) and will be deleted ~6 months later.
As I see the issue, the problem is the asynchronous zip completion. My script needs to be smart enough to answer the question, "When is the zip operation complete?"
September 21, 2010 at 9:14 am
Shawn Melton (9/21/2010)
I currently work as a contractor for the military and if you are following the DoD STIG checklist, C2 Auditing is not required. The checklist has a custom server-side trace that can be used in place of C2 Auditing. This will allow you to move the trace file to your desired location. As well the custom trace does not capture all the information that C2 Audit does.On a side note as well in the checklist C2 auditing actually becomes a finding in that it and the default trace collect to much information.
Anyway, I'm looking over your PowerShell script as well (:-D just to learn) and will post if I come up with anything.
Tell me more.
For example, STIG DG0029 says, "If C2 Auditing is enabled (See Check DM0510: C2 audit mode), this check is Not a Finding." I think you are correct in saying that C2 is not *required,* per se, but it resolves a number of Cat 2 Findings in the STIG checklist, and that is where IA (i.e., the government flavor of data security) wants to go.
September 21, 2010 at 9:24 am
aah.. got your point. i thought you were trying to move the "zipped" file to a target location.
I'll assume your .zip files will stay on the current location for some period(as u said). Also i'll assume that if the file errorlog1.trc gets zipped you will name it to errorlog1.zip (means only extension changes).
will this loop server your purpose (only for deletion)? just a brief logic. This will only delete those .trc files which have been zipped.
for each file in the SourceFolder
if TypeOfFile is .zip
if its correspoing .trc file is present (compare name of the file without extension-substring?)
delete the .trc file
end if
end if
next
September 21, 2010 at 9:40 am
shew (9/21/2010)
Tell me more.For example, STIG DG0029 says, "If C2 Auditing is enabled (See Check DM0510: C2 audit mode), this check is Not a Finding." I think you are correct in saying that C2 is not *required,* per se, but it resolves a number of issues in the STIG checklist, and that is where IA (i.e., the government flavor of data security) wants to go.
NOTE: I'm going by the current checklist just released in August (v8r1.7). For those non-miltary folks out there DoD (Department of Defense) publishes security guidelines that can be used to harden a operating system or in our case a RDBMS. You can find these checklist here
STIG ID - DM0510 states for the long name
C2 Audit mode should be enabled or custom audit traces defined.
Then within that same STIG ID it states in the Checks where you see if C2 audit mode is enabled under sys.configurations this statement:
If the value of 0 (zero) is returned for the Config_Value, confirm that a valid audit trace is configured and implemented. See checks DG0029, DG0145, and DM5267.
Now DG0029 and DM5267 simply reference DG0145. If DG0145 is not used or in place, then C2 Audit mode is required. So as long as you just create the stored procedure provided in DG0145 you meet the security requirements for the STIG findings DG0029 and DM5267. Then DM0510 is marked as not a finding.
Most of the other findings have to do with regular review of the audit files and ensuring application name and other things are in the audit file itself.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 21, 2010 at 10:07 am
Shawn Melton (9/21/2010)
C2 Audit mode should be enabled or custom audit traces defined.
Good catch. Excellent response. Implementation of custom audit traces may reduce our overall volume of .trc file accumulation. I'm thinking that we will still need to work out a zip solution, but maybe the firehose of .trc data can be backed off a bit.
September 21, 2010 at 10:34 am
shew (9/21/2010)
I'm thinking that we will still need to work out a zip solution, but maybe the firehose of .trc data can be backed off a bit.
This should be tremendously less overhead and storage. C2 Audit mode is catching every change or action made to the instance. The trace in DG0145 is only capturing certain events so you should be able to go down to zipping maybe once a week.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 21, 2010 at 1:00 pm
FYI -
A coworker found this link:
http://blogs.technet.com/b/askds/archive/2008/09/29/command-line-compression-in-core.aspx
It appears to be sychronous, but its compression is not as good as zip. MAKECAB shrinks a 200 MB file to 10 GB, where zip shrinks the same file to less than 5 MB.
September 30, 2010 at 11:18 pm
ps. (9/21/2010)
Jeff Moden (9/21/2010)[hr
Yep... I pretty much got that from the OP, Pradeep. I'm just trying to figure out a work around without using any 3rd party software so that shew doesn't have to jump through any security hoops. You just don't know what a PITA following the computer security for a DOD related company can be. 🙂
You were too tied up with your test for past couple of days:w00t: so i mentioned that. pls dont mind 🙂
Ah... thanks for the "cover". I appreciate the leg up. 🙂
--Jeff Moden
November 24, 2010 at 1:56 pm
I am glad to see I am not the only having fun with DG0145. Could someone point out how to implement the fix recommended in dg0145? I tried entering the commands listed under the fix for dg0145, but I'm not sure how to. I have no database experience beyond running some SQL statements in college long ago.
I tried executing the commands from the query prompt in SQL server 2005 management studio. I get a message saying the commands executed and no errors, but when I look at the directory the audit log was supposed to be placed in there is no file...I looked at running these commands as a trace in the sql profiler, but I do not see where to enter the commands there either. The traces all seem to be predefined with an additional options in a table.
Thanks for any help you can provide.
October 27, 2011 at 3:45 pm
Sounds like you need to restart your sql server since the C2 auditing hasn't executed yet. In advanced options you can see if its turned on and executing or just turned on.
How to turn on and view advanced options
http://qa.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/
October 27, 2011 at 3:51 pm
This is correct. We use customs scripts to meet DG0145 requirements.
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply