August 26, 2022 at 9:36 pm
Hello!
We have setup SQL Audit to write to file, we are then importing back to table and exporting to .txt file format as needed by other team to review.
The PS script below works , txt file is in readable format but is taking too long to execute.
exec xp_cmdshell 'powershell.exe "Invoke-Sqlcmd -InputFile "C:\A.sql" | Out-File -filePath "C:\File.txt" -force'
Output from PS:
user_defined_event_id :
user_defined_information :
audit_schema_version :
sequence_group_id :
transaction_id :
event_time :
sequence_number :
action_id :
succeeded :
permission_bitmask :
is_column_permission :
session_id :
server_principal_id :
database_principal_id :
target_server_principal_id :
target_database_principal_id :
object_id :
class_type :
session_server_principal_name :
server_principal_name :
server_principal_sid :
database_principal_name :
target_server_principal_name :
target_server_principal_sid :
target_database_principal_name :
server_instance_name :
database_name :
schema_name :
object_name :
statement :
additional_information :
file_name :
Is there any other way to export data which is faster as well as readable?Tried with SSIS with pipe-delimited but output does not look as readable as the one from PS.
Thanks in advance.
August 27, 2022 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 28, 2022 at 4:46 am
Instead of using PowerShell, why not make a call to BCP and us the "out" functionality? It's nasty fast. I'll also tell you that if they have SQL Server at "the other end", you can export in the "Native" mode and they can import that into the SQL Server on their end and both will be even faster.
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16
Contrary to what it says, you shouldn't have to download it and install it... it should already be available.
They also have some helpful hints on doing different things...
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2022 at 3:01 pm
Instead of using PowerShell, why not make a call to BCP and us the "out" functionality? It's nasty fast. I'll also tell you that if they have SQL Server at "the other end", you can export in the "Native" mode and they can import that into the SQL Server on their end and both will be even faster.
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16
Contrary to what it says, you shouldn't have to download it and install it... it should already be available.
They also have some helpful hints on doing different things...
The OP is working with audit files and loading that data to SQL Server. BCP is not an option here - but using PS to load the files into a table is also not the answer.
SQL Server has a built-in function for reading audit files sys.fn_get_audit_file as well as other functions/views such as sys.dm_audit_class_type_map and sys.dm_audit_actions.
Using these functions - you can read the current audit file filtered to the specified range to generate the output needed. No need to load the data into a table first or to use PS to load the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 28, 2022 at 3:24 pm
To clarify I am using built in function to read from SQL Audit file but the other team has capability to read from .txt file only, that is the reason I am using built in function to import SQL Audit to a table and then exporting data from that table to a flat file destination.
Thank you.
August 28, 2022 at 3:39 pm
To clarify I am using built in function to read from SQL Audit file but the other team has capability to read from .txt file only, that is the reason I am using built in function to import SQL Audit to a table and then exporting data from that table to a flat file destination.
Thank you.
Okay - I misunderstood the problem. You can use BCP out as @JeffModen recommended - but I doubt that is going to be any faster. What is the query you are using to pull the data - and are you pulling all of the data from the table or just a subset of data? If a subset of data - do you have indexes on the table to support the query?
And finally - how much data is being exported?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 28, 2022 at 4:21 pm
Everything in the table is being exported to text file, there is no indexes in the table just raw data\ dump from SQL Audit to a table.
SQL Audit file size is total of 1 GB.
Thanks.
August 28, 2022 at 10:55 pm
Jeff Moden wrote:Instead of using PowerShell, why not make a call to BCP and us the "out" functionality? It's nasty fast. I'll also tell you that if they have SQL Server at "the other end", you can export in the "Native" mode and they can import that into the SQL Server on their end and both will be even faster.
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16
Contrary to what it says, you shouldn't have to download it and install it... it should already be available.
They also have some helpful hints on doing different things...
The OP is working with audit files and loading that data to SQL Server. BCP is not an option here - but using PS to load the files into a table is also not the answer.
SQL Server has a built-in function for reading audit files sys.fn_get_audit_file as well as other functions/views such as sys.dm_audit_class_type_map and sys.dm_audit_actions.
Using these functions - you can read the current audit file filtered to the specified range to generate the output needed. No need to load the data into a table first or to use PS to load the data.
Perhaps not on the way "in" (which I never suggested) to the table the OP is creating but on the way out from the table to a text file, there won't be much that can even come close to the speed than BCP has to offer.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply