Tutorial SQL Server/IIS Logs: How to load and analyze(query) Internet Information Services Web Logs
In this tutorial, Dr. Todd Wolfe demonstrates how to use SQL Server to import IIS web logs for analysis. At the end of this tutorial you will know how to
1) Find the location of your IIS Web Logs
2) Create a SQL Server Table structure to match the IIS Log format
3) Bulk insert an IIS log to SQL Server
4) Query the SQL Server table for important website visitor information
Use this method if you do not currently have any existing SIEM or log analysis in place for your Windows-hosted websites.
This can be used to help discover potential malicious activity occurring along with the Symantec IIS report released earlier today about the new stealthy "Cranefly" campaign that uses IIS Logs.
You can read more here "Cranefly: Threat Actor Uses Previously Unseen Techniques and Tools in Stealthy Campaign"
https://symantec-enterprise-blogs.sec...
--SQL Commands if needed
DROP TABLE IF EXISTS dbo.IISLOG
CREATE TABLE dbo.IISLOG (
[DATE] [DATE] NULL,
[TIME] [TIME] NULL,
[s-ip] [VARCHAR] (48) NULL,
[cs-method] [VARCHAR] (8) NULL,
[cs-uri-stem] [VARCHAR] (255) NULL,
[cs-uri-query] [VARCHAR] (2048) NULL,
[s-port] [VARCHAR] (4) NULL,
[s-username] [VARCHAR] (256) NULL,
[c-ip] [VARCHAR] (48) NULL,
[cs(User-Agent)] [VARCHAR] (1024) NULL,
[cs(Referer)] [VARCHAR] (4096) NULL,
[sc-STATUS] [INT] NULL,
[sc-substatus] [INT] NULL,
[sc-win32-STATUS] [BIGINT] NULL,
[time-taken] [INT] NULL,
INDEX cci CLUSTERED COLUMNSTORE
)
--Bulk insert, update from with your path and filename.
BULK INSERT dbo.IISLog
FROM 'C:\inetpub\logs\LogFiles\W3SVC1\u_ex221028.log'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)
Watch video Tutorial SQL Server/IIS Logs: How to load and analyze(query) Internet Information Services Web Logs online without registration, duration hours minute second in high quality. This video was added by user Dr. Todd Wolfe Technology Training and Tutorials 28 October 2022, don't forget to share it with your friends and acquaintances, it has been viewed on our site 4,096 once and liked it 36 people.