We're trying to extract page views, file download count, users list from w3c IIS logs. we want to define what's page view, i.e. any user stayed on same page more than 10 sec to be a one page view. anything less is not a page view. w3c logs doesn't seem to be having enough data to extract this. can this be possible with what's already available?
This is the data available to extract the above info from,
Datatable operator
datatable (TimeGenerated:datetime, csUriStem:string, scStatus:string, csUserName:string, sSiteName :string) [datetime(2019-04-12T11:55:13Z),"/Account/","302","-","WebsiteName", datetime(2019-04-12T11:55:16Z),"/","302","-","WebsiteName", datetime(2019-04-12T11:55:17Z),"/Account/","200","myemail@mycom.com","WebsiteName", datetime(2019-04-12T11:55:17Z),"/Content/site.css","200","-","WebsiteName", datetime(2019-04-12T11:55:17Z),"/Scripts/modernizr-2.8.3.js","200","-","WebsiteName", datetime(2019-04-12T11:55:17Z),"/Scripts/bootstrap.js","200","-","WebsiteName", datetime(2019-04-12T11:55:17Z),"/Content/bootstrap.css","200","-","WebsiteName", datetime(2019-04-12T11:55:18Z),"/Scripts/jquery-3.3.1.js","200","-","WebsiteName", datetime(2019-04-12T11:55:23Z),"/","302","-","WebsiteName", datetime(2019-04-12T11:56:39Z),"/","200","myemail@mycom.com","WebsiteName", datetime(2019-04-12T11:57:13Z),"/Home/About","200","myemail@mycom.com","WebsiteName", datetime(2019-04-12T11:58:16Z),"/Home/Contact","200","myemail@mycom.com","WebsiteName", datetime(2019-04-12T11:59:03Z),"/","200","myemail@mycom.com","WebsiteName"]
I am not sure I got all your requirements right, but here is something to get started and provide you initial direction.
datatable (TimeGenerated:datetime, csUriStem:string, scStatus:string, csUserName:string, sSiteName :string)
[datetime(2019-04-12T11:55:13Z),"/Account/","302","-","WebsiteName",
datetime(2019-04-12T11:55:16Z),"/","302","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Account/","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Content/site.css","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Scripts/modernizr-2.8.3.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Scripts/bootstrap.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Content/bootstrap.css","200","-","WebsiteName",
datetime(2019-04-12T11:55:18Z),"/Scripts/jquery-3.3.1.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:23Z),"/","302","-","WebsiteName",
datetime(2019-04-12T11:56:39Z),"/","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:57:13Z),"/Home/About","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:58:16Z),"/Home/Contact","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:59:03Z),"/","200","myemail@mycom.com","WebsiteName"]
| where scStatus !in ('302') // exclude status 302
| where csUriStem !startswith '/Scripts' and csUriStem !endswith ".css" // exclude pages coming from '/Script' and .css files
| order by TimeGenerated asc
| summarize t=make_list(TimeGenerated) by csUriStem, csUserName // create time-series of visit events
| mv-apply t to typeof(datetime) on // run subquery on each of the series
(
project isVisit = (t - prev(t)) > 1min // compare with previous timestamp, and see if >1min passed
| summarize Visits=sum(isVisit)
)
| project csUriStem, csUserName, Visits
Here are links to make_list() (aggregation function), prev() (window function), summarize operator, and mv-apply operator