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.security.com/blogs/threat-intelligence/cranefly-new-tools-technique-geppei-danfuan

–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:inetpublogsLogFilesW3SVC1u_ex221028.log’
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ‘ ‘,
ROWTERMINATOR = ‘n’
)