Welcome!
One day I was pondering how I might use log parser to map visitors to a website by state. I am aware this is easily done with tools like Google Analytics, but I was interested in using existing logs for the info.
Using the PowerShell and Log Parser functions from the library listed in a previouse post. Log parser can easily get the visitors by IP Address from an IIS log.
|
1 2 3 4 5 6 7 8 9 |
# Get unique ip addresses to working file $query = @" SELECT Distinct c-ip As IPAddress INTO D:Unique_IP_Addresses.txt From C:Logsex080310.log "@ $LPInputFormat = Get-LPInputFormat "w3c" $LPOutputFormat = Get-LPOutputFormat "csv" Invoke-LPExecuteBatch -query $query -inputtype $LPInputFormat -outputtype $LPOutputFormat | Out-Null |
The next task is to get the location of the IP addresses. The tool I chose for this task was the free GeoLite City from MaxMind http://www.maxmind.com/app/geolitecity. Here is an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
# Get location for IP addresses from GEOIP DB $geodbpath = "C:GeoLiteCity" $visitors = Import-Csv -Path "D:Unique_IP_Addresses.txt" $ipinfo = New-Object -ComObject "GeoIPCOMEx.GeoIPEx" $ipinfo.set_db_path($geodbpath) | Out-Null Add-Content -Path "D:IP_Locations.txt" "Location" foreach($visitor in $visitors){ $ipinfo.find_by_addr($visitor.ipaddress) | Out-Null if($ipinfo.Region){ Add-Content -Path "D:IP_Locations.txt" $ipinfo.Region } else { Add-Content -Path "D:IP_Locations.txt" "Unknown" } } # Get State counts $query = @" SELECT Location, Count(Location) As Visitors INTO D:VisitorsByState.txt From D:IP_Locations.txt Group By Location "@ $LPInputFormat = Get-LPInputFormat "csv" $LPOutputFormat = Get-LPOutputFormat "csv" Invoke-LPExecuteBatch -query $query -inputtype $LPInputFormat -outputtype $LPOutputFormat | Out-Null |
There are a couple of ways to use the MaxMind GeoIP database. It can be used in it’s native binary format or it can be imported into SQL from csv files. MaxMind recommends using the binary format, which is what I chose to do. MaxMind also provides API’s for use with a variety of platform’s. I chose to use the COM version.
After the location is determined the counts are calculated. This brings us to the point where we need to chart the results. The tool I chose for this operation is PowerGadgets. This is a charting tool made for use with PowerShell, it can be handy. Here is an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
# Create PowerGadgets Map $charttitle = "Daily visitors by state" $visits = Import-Csv -Path D:VisitorsByState.txt $visits | Out-Map -mapsource "USUSA-StatesAbrev" ` -label location ` -values visitors ` -palette "highcontrast" ` -output $chartout ` -size 600,400 ` -BackColor White ` -Titles_0_BackColor White ` -Titles_0_Font "Arial, 14" ` -Titles_0_Text $charttitle ` -Titles_0_TextColor Black ` -conditionalattributes_0_condition_from 1 ` -conditionalattributes_0_condition_to 10 ` -conditionalattributes_0_color Red ` -conditionalattributes_0_text "1 to 10" ` -conditionalattributes_1_condition_from 11 ` -conditionalattributes_1_condition_to 25 ` -conditionalattributes_1_color Blue ` -conditionalattributes_1_text "11 to 25" ` -conditionalattributes_2_condition_from 26 ` -conditionalattributes_2_condition_to 50 ` -conditionalattributes_2_color Green ` -conditionalattributes_2_text "26 to 50" ` -conditionalattributes_3_condition_from 51 ` -conditionalattributes_3_condition_to 100 ` -conditionalattributes_3_color Yellow ` -conditionalattributes_3_text "51 to 100" ` -conditionalattributes_4_condition_from 101 ` -conditionalattributes_4_condition_to 1000 ` -conditionalattributes_4_color Violet ` -conditionalattributes_4_text "101 to 1000" |
And here is our final Chart.
This works pretty well the only drawback to this solution is that PowerGadgets is a pay tool, but since I own a copy it suites my needs.
Regards,
Dave