question

GanaS-1606 avatar image
0 Votes"
GanaS-1606 asked OlafHelper-2800 answered

SQL Stored Procedure with dynamic columns

Team

I am working on a project in which i need to get column names from Security event log (like ex: Security ID, Account Name, Account Domain, Logon ID) & this column count will increase based on security events.
I need this to read from one SQL Table & and get columns (1st event may have 4 columns, 2nd event may have 10 columns & so on) after reading it need to update it in new SQL table with the columns (each column should have each event & its values).
Any suggestion please.

I am happy to have this in SQL Query OR PowerShell too.

================================
1st Event Log:
================================================
Message
An account was logged off.

Subject:
Security ID: S-1-5-21-4092471901-707098849-2603905285-1002
Account Name: IUSR
Account Domain:
Logon ID: 0x2D6330DA

Logon Type: 3

This event is generated when a logon session is destroyed. It may be positively correlated with a logon event using the Logon ID value. Logon IDs are only unique between reboots on the same computer.

====================================
2nd Event log
=======================
Key file operation.

Subject:
Security ID: S-1-5-18
Account Name: ETS03$
Account Domain: LAB
Logon ID: 0x3E7

Process Information:
Process ID: 9672
Process Creation Time: ‎2022‎-‎04‎-‎15T00:49:59.688316600Z

Cryptographic Parameters:
Provider Name: Microsoft Software Key Storage Provider
Algorithm Name: UNKNOWN
Key Name: ConfigMgrPrimaryKey
Key Type: Machine key.

Key File Operation Information:
File Path: C:\ProgramData\Microsoft\Crypto\Keys\f67681ddee923363c159c11a44f135de_670959a2-5d10-470b-952c-36572be379fb
Operation: Read persisted key from file.
Return Code: 0x0

sql-server-transact-sqlwindows-server-powershell
· 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.

We would need to see the CREATE TABLE statement for the table you are reading from as well as the one you are going to write to. INSERT statements with sample data for a couple of events would also help, as well as the expected result given the sample.

1 Vote 1 ·

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

Security event log ...1st event may have 4 columns

If you mean entries from Windows Security EventLog, there are no "columns", that's one plain text, someway separated with linebreaks.
You would have to parse the text for the required information; no big fun in plain T-SQL. Better use an application/PowerShell for it.
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.