By Liisa Tallinn and Raido Karro
The most difficult question when analyzing logs is 'What to ask?' We've put down 20 sample questions and copy-pastable queries to ask from your Microsoft Internet Information Services logs or IIS logs by querying them directly in their current storage location with SpectX. For example, 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 data 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 full-functionality trial version of the software is free for 30 days.
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)
Analyzing IIS logs
We've put down 20 sample questions and copy-pastable queries to ask from your raw IIS logs by querying them directly in 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.
Parsing Windows 2008 DNS Debug Logs
Parsing Windows DNS logs can be a challenge. If your server runs post-2012 software, you are probably good as the output is formatted into Windows event logs. However, if you're up against an earlier version than Microsoft Server 2012 r2, then the output in plaintext log files is challenging to make sense of both for humans (developers) and machines. Here's how to parse Windows 2008 DNS debug logs quickly with SpectX.
Analysing Git Log Reports. Authors, Fridays and Batmen
Playing around with Git logs can give you fascinating insights into a project. Whether it's analysing your own work or checking how someone else's project is doing, Git logs have it all. Sometimes all you need is a quick general overview, e.g. counting contributions per author. The next minute, you really need to dig into a specific time period (Fridays!), look at a keyword or email domain. Zooming out and digging deep into Git repos is a walk in the park with the right tool - follow our lead.
