0

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"]

Raj
  • 15
  • 6
  • Can you please share data sample of what is available? For the sake of the question, you can use datatable() operator (to 'invent' data as part of the query). – Alexander Sloutsky Apr 09 '19 at 13:01
  • @AlexanderSloutsky Added link for sample data extracted from the logs in the post . This is usual w3CIISLogs which is available on Log Analytics workspace. I need to use KQL query to extract the necessary data. Thanks – Raj Apr 10 '19 at 11:34
  • It is still not very clear what fields of the picture are relevant, or what other fields you have. Can you please use datatable operator to describe the input, and provide example of the output? For example, here is what simulated input may be: datatable(TimeGenerated:datetime, csUriStem:string, csUserName:string) [ datetime(2019-04-02T22:47:01), "/Home/About", "User1", datetime(2019-04-02T22:47:02), "/Home/Contact", "User1", ] – Alexander Sloutsky Apr 10 '19 at 15:01
  • Hi @AlexanderSloutsky, added datatable operator in the question summary. From which, I need to exclude value 200 and exclude 302 of scStatus, exclude the unnecessary files accessed, calculate time diff between previous requests, extract summary data of page views and user activities. Thank you. – Raj Apr 12 '19 at 13:37

1 Answers1

1

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

Alexander Sloutsky
  • 2,827
  • 8
  • 13