By Liisa Tallinn and Raido Karro
Parsing and analyzing IIS logs with SpectX is easy - copy the parsing script and start querying directly from the log storage. The most difficult question is of course 'What to ask?' To give some inspiration, we've put down 20 sample questions and copy-pastable queries to ask specifically from the IIS logs. The logs could be stored in the IIS server itself, in an Azure blob or a centralized log server collecting flat text files - SpectX can read them directly from that source location.
SpectX is a log analyzer that skips ingestion and indexing to parse data at runtime. It is quick and easy to set up with existing data sources - no need to configure and wait for the IIS logs to get ingested and indexed. The average processing speed for SpectX is 350MB per second per CPU core which makes it a great tool to tackle even large volumes of data in distributed locations. The Desktop edition of SpectX is free of charge.
To get started with the queries, read the instructions on pointing SpectX at the IIS logs, setting up the pattern (schema) and getting the data parsed. Having run the first query, continue with pasting the scripts below to the end of the initial query.
SpectX' query syntax is similar to piping UNIX commands. Queries are written by chaining commands one after another, directing the previous output to the next input.
| filter(c_ip = 0.0.0.0)2) How many requests are unique client IPs generating? Sort the results in descending order.
| select(cs_uri_stem, count(cs_uri_stem) as requestcount)
| group (cs_uri_stem)
| sort(requestcount DESC)
| select(c_ip,reqcount:count(c_ip))Note: if you wish to get a top list of c_ip’s, add a limit to this script. Eg to get top 20 IPs:
| group(c_ip)
| sort(reqcount desc)
| limit(20)3) How many hits per hour did the server get from a particular IP address?
| filter(c_ip = 0.0.0.0)Press on the chart button to get a line visualization
| select (date_time[1 hour], reqcount:count(*))
| group(date_time[1 hour])
| select(date_time, GEO(c_ip))Click on ‘Map’ and shorten the range to hit play and enjoy the scenery.
| sort(date_time)
| filter(cs_uri_stem like '%foo%')Note: If you wish to display this result on a chart, add one more line and then click on the Chart button
| select(c_ip, reqcount:count(c_ip))
| group (c_ip)
| sort(reqcount DESC)
| select(string(c_ip), reqcount) //transform the IP back to strings6) What is the domain associated with these IP addresses (ReverseDNS)
| filter(c_ip IN (0.0.0.0, 1.1.1.1, 1.2.3.4))
| select(c_ip, reverse:DNS_LOOKUP(c_ip))
| group(c_ip)
| select(cs_uri_stem, reqcount:count(cs_uri_stem))8) How many times were the URIs containing ‘foo’ or ‘bar’ hit?
| group(cs_uri_stem)
| sort(reqcount desc)
| filter(cs_uri_stem like '%foo%' or cs_uri_stem like '%bar%')9) How many times were the URIs containing ‘foo’ or ‘bar’ hit? When was the first and when the last hit?
| select(cs_uri_stem, reqcount:count(cs_uri_stem))
| group(cs_uri_stem)
| sort(reqcount desc)
| filter(cs_uri_stem like '%foo%' or cs_uri_stem like '%bar%')10) How many times did various IPs request a URI containing ‘foo’ or ‘bar’?
| select(cs_uri_stem, c_ip, reqcount:count(cs_uri_stem), first_seen:min(date_time), last_seen:max(date_time))
| group(cs_uri_stem, c_ip )
| sort(reqcount desc)
| filter(cs_uri_stem like '%foo%' or cs_uri_stem like '%bar%')11) Compare the hit count in time for URIs that contain ‘foo’, ‘bar’ and ‘baz’.
| select(cs_uri_stem, c_ip, reqcount:count(cs_uri_stem))
| group (cs_uri_stem, c_ip )
| sort(reqcount desc)
| select(date_time, cs_uri_stemPress on ‘chart’ to get a visualization on this graph.
,total_hits:count(*)
,foo:count(cs_uri_stem like '%foo%')
,bar:count(cs_uri_stem like '%bar%')
,baz:count(cs_uri_stem like '%baz%')
)
| group(cs_uri_stem)
| filter(foo > 0 AND bar > 0 AND baz > 0)
| filter(c_ip=0.0.0.0 and (cs_uri_stem like '%foo%' or cs_uri_stem like '%bar%'))13) What are the URIs visited after 1st September 2019 at 10 AM? How many hits did they get?
| select(date_time, cs_uri_stem, reqcount:count(*))
| group(date_time[1 hour], cs_uri_stem)
| sort(reqcount desc)
| filter(cs_uri_stem like '%foo%' or cs_uri_stem like '%bar%')
| filter(date_time > T('2019-09-01 10:00:00') )
| select(cs_uri_stem, c_ip, reqcount:count(cs_uri_stem))
| group(cs_uri_stem, c_ip )
| sort(reqcount desc)
| select(totalhits:count(*), browser:cs_user_agent)15) What external pages (referrers) refer to broken links?
| group(browser)
| sort(totalhits desc)
| limit(50)
| filter(cs_referer IS NOT NULL AND sc_status = 404 AND (sc_substatus IS NULL OR sc_substatus=0))16) How many times did the server return a particular status code?
| select(referer:cs_referer, uri:cs_uri_stem)
| group(referer)
| SELECT(status:sc_status,count(*) )17) Are there any requests originating from the TOR network?
| GROUP(status)
| SORT(status)
| join(PARSE(src:'https://check.torproject.org/cgi-bin/TorBulkExitList.py?ip=1.1.1.1', pattern:'ipaddr:exitAddress (EOL|EOS)') on left.c_ip = right.exitAddress)18) Are there any Googlebot user agents not coming from Google AS? That is - who's pretending to be a Googlebot?
|filter(asname(c_ip) != 'AS15169 Google LLC' and lower(cs_user_agent) like '%googlebot%')
| select(hour:date_time[1 hour], incomingKB:round(Sum(cs_bytes)/1024,2), outgoingKB:round(Sum(sc_bytes)/1024,2))20) What are the total, max and average bandwidth and max & average time of requests per URI in hourly slots?
| group(hour[1 hour])
| select( hour:date_time[1 hour], uri:lower(cs_uri_stem), reqcount:COUNT(*), TotBytesSent:SUM(sc_bytes), AvgBytesSent:AVG(sc_bytes), MaxBytesSent:Max(sc_bytes), AvgTime:(Avg(time_taken)*0.001), MaxTime:(MAX(time_taken)*0.001))
| group(hour[1 hour], uri)
| filter(reqcount> 10)
| sort(reqcount ASC)
| limit(80)
Meet Free SpectX
To help security analysts analyse raw logs and any ad-hoc data in time-critical investigations, SpectX is glad to launch the free edition of its rapid log analyzer. The parser and query engine help investigators quickly parse and query unlimited volumes of raw and compressed log files without ingestion or indexing directly from their storages like on-prem servers, AWS, Azure, Hadoop and/or SQL databases.
Analyzing IIS logs: Microsoft Exchange, OWA and ActiveSync Activities
Need to audit a specific user connecting to their mailbox? Troubleshoot why users are not able to sync their mobiles via ActiveSync? Investigate errors in time intervals? The answer to these and many other questions lie in the IIS logs of the Microsoft Exchange server.
Analyzing Raw IIS Logs
We've put down 20 copy-pastable queries to ask from your raw IIS logs by analyzing them directly from their current storage location.
Parsing IIS logs
This is a step-by-step guide on parsing IIS logs using SpectX. Once the parser matches the raw data, SpectX makes it quick and easy to run queries on raw log files without first importing and duplicating them to another location.
