Group By Top Level URL and Count Hits for every subsiteRSS

0 replies

Last post Aug 27, 2019 07:02 PM by Michael71

  • Group By Top Level URL and Count Hits for every subsite

    Aug 27, 2019 07:02 PM|Michael71|LINK

    Hello,

    I want to query the hits to each website and total them up under the top level site. I have 6 sites. Code below.

    Thank you,

    --------------------------------------------------------------------------------------------------------------------

     

    SELECT DISTINCT

    cs-uri-stem as Page

    count(cs-uri-stem) AS RequestCount

    FROM '[LOGFILEPATH]'

    WHERE cs-uri-stem LIKE '%/teams/tnt/%' OR cs-uri-stem LIKE '%/teams/tot/%' OR cs-uri-stem LIKE '%/teams/tet/%' OR cs-uri-stem LIKE '%/teams/tut/%' AND date >= '2019-01-01' AND date <= '2019-07-31'

    GROUP BY cs-uri-stem

    ORDER BY count(cs-uri-stem)

    -----------------------------------------------------------------------------------------------------------------

     

    Table should look similar: I have hundreds of subsites under the main Top level sites in the Logs. I'm getting results for all Top level and subsites now.

    Page                  RequestedCount

    TNT                        200

    TOT                        180

    TET                        150

    TUT                        100