Example #1
0
def monthly_statistics(request):
    template = "registry/monthly_statistic.html"
    version = request.REQUEST.get('version', None)
    initial = {'version': version}

    context = {}
    if request.method == "POST":
        versionform = VersionForm(initial=initial, data=request.REQUEST.copy())
        if versionform.is_valid():

            args = list()

            startdate = versionform.cleaned_data['startdate']
            if startdate:
                args.append(Q(creation_date__gte=startdate))
                logger.debug("StartDate '%s'" % str(startdate))

            enddate = versionform.cleaned_data['enddate']
            if enddate:
                args.append(Q(creation_date__lte=enddate))
                logger.debug("EndDate '%s'" % str(enddate))

            agents = versionform.cleaned_data['agent']
            # agent is now mandatory
            #if not agents:
            #    agents = Agent.objects.all()
            agent_ids = tuple([a.id for a in agents])
            args.append(Q(agent__id__in=agent_ids))
            agents_dict = dict()
            for a in agents:
                agents_dict[a.id] = a
            logger.debug("Agents '%s'" % str(agents))

            v = versionform.cleaned_data['version']
            if v and len(v) > 0:
                subargs = list()
                if v.startswith("5.0"):
                    subargs.append((Q(version__startswith=v)
                                    | Q(version__startswith='4.5')))
                elif v.startswith("4.2"):
                    subargs.append((Q(version__startswith=v)
                                    | Q(version__startswith='Beta-4.2')))
                elif v.startswith("4.1"):
                    subargs.append((Q(version__startswith=v)
                                    | Q(version__startswith='Beta-4.1')))
                else:
                    subargs.append(Q(version__startswith=v))
                agent_version = AgentVersion.objects.filter(*subargs)
                #agent_version_ids = tuple([a.id for a in agent_version])
                args.append(Q(agentversion__in=agent_version))
            # 5.0 AND (registry_hit.agent_version LIKE %s  OR registry_hit.agent_version LIKE '4.5%%')
            # 4.1 or 4.2 AND (registry_hit.agent_version LIKE %s OR registry_hit.agent_version LIKE 'Beta-4.1%%')

            # ip adresses
            ip_address = IP.objects.exclude(ip__regex=settings.IPLOCALREGEX)
            args.append(Q(ip__in=ip_address))

            # monthly main query
            monthly_result = Hit.objects \
            .filter(*args) \
            .extra(select={'datestr':"to_char(creation_date, 'YYYY-MM')"}) \
            .values('agent_id', 'datestr') \
            .annotate(hit_total_count=Count('id')) \
            .annotate(hit_unique_count=Count('ip__id', distinct=True))

            # prepare table
            table = month_year_table(startdate, enddate, agents)

            try:
                for row in monthly_result:
                    if row['agent_id'] in (7, 13):
                        display_name = "BF (7,13)"
                    else:
                        display_name = "%s (%i)" % (agents_dict[
                            row['agent_id']].display_name, row['agent_id'])
                    creation_date = row['datestr']
                    total = row['hit_total_count']
                    unique = row['hit_unique_count']
                    try:
                        table[creation_date][display_name]['total'] += total
                        table[creation_date][display_name]['unique'] += unique
                    except:
                        pass
            except:
                logger.debug(traceback.format_exc())

            # All agents main query not as a sum
            all_monthly_result = Hit.objects \
            .filter(*args) \
            .extra(select={'datestr':"to_char(creation_date, 'YYYY-MM')"}) \
            .values('datestr') \
            .annotate(hit_total_count=Count('id')) \
            .annotate(hit_unique_count=Count('ip__id', distinct=True))

            try:
                for row in all_monthly_result:
                    display_name = "All*"
                    creation_date = row['datestr']
                    total = row['hit_total_count']
                    unique = row['hit_unique_count']
                    try:
                        table[creation_date][display_name]['total'] += total
                        table[creation_date][display_name]['unique'] += unique
                    except:
                        pass
            except:
                logger.debug(traceback.format_exc())

            result, column_names = sorted_table(table)
            context['result'] = result
            context['column_names'] = column_names
    else:
        versionform = VersionForm(initial=initial)

    context['version'] = version
    context['defaultversion'] = Version.objects.get(pk=1)
    context['versionform'] = versionform

    t = get_template(template)
    c = Context(request, context)
    rsp = t.render(c)
    return HttpResponse(rsp)
Example #2
0
def monthly_statistics(request):
    template = "registry/monthly_statistic.html"
    version = request.REQUEST.get('version', None)
    initial = {'version':version}

    context = {}
    if request.method == "POST":
        versionform = VersionForm(initial=initial, data=request.REQUEST.copy())
        if versionform.is_valid():

            args = list()

            startdate = versionform.cleaned_data['startdate']
            if startdate:
                args.append(Q(creation_date__gte=startdate))
                logger.debug("StartDate '%s'" % str(startdate))

            enddate = versionform.cleaned_data['enddate']
            if enddate:
                args.append(Q(creation_date__lte=enddate))
                logger.debug("EndDate '%s'" % str(enddate))

            agents = versionform.cleaned_data['agent']
            # agent is now mandatory
            #if not agents:
            #    agents = Agent.objects.all()
            agent_ids = tuple([a.id for a in agents])
            args.append(Q(agent__id__in=agent_ids))
            agents_dict = dict()
            for a in agents:
                agents_dict[a.id] = a
            logger.debug("Agents '%s'" % str(agents))

            v = versionform.cleaned_data['version']
            if v and len(v) > 0:
                subargs = list()
                if v.startswith("5.0"):
                    subargs.append((Q(version__startswith=v) | Q(version__startswith='4.5')))
                elif v.startswith("4.2"):
                    subargs.append((Q(version__startswith=v) | Q(version__startswith='Beta-4.2')))
                elif v.startswith("4.1"):
                    subargs.append((Q(version__startswith=v) | Q(version__startswith='Beta-4.1')))
                else:
                    subargs.append(Q(version__startswith=v))
                agent_version = AgentVersion.objects.filter(*subargs)
                #agent_version_ids = tuple([a.id for a in agent_version])
                args.append(Q(agentversion__in=agent_version))
            # 5.0 AND (registry_hit.agent_version LIKE %s  OR registry_hit.agent_version LIKE '4.5%%')
            # 4.1 or 4.2 AND (registry_hit.agent_version LIKE %s OR registry_hit.agent_version LIKE 'Beta-4.1%%')

            # ip adresses
            ip_address = IP.objects.exclude(ip__regex=settings.IPLOCALREGEX)
            args.append(Q(ip__in=ip_address))

            # monthly main query
            monthly_result = Hit.objects \
            .filter(*args) \
            .extra(select={'datestr':"to_char(creation_date, 'YYYY-MM')"}) \
            .values('agent_id', 'datestr') \
            .annotate(hit_total_count=Count('id')) \
            .annotate(hit_unique_count=Count('ip__id', distinct=True))

            # prepare table
            table = month_year_table(startdate, enddate, agents)

            try:
                for row in monthly_result:
                    if row['agent_id'] in (7,13):
                        display_name = "BF (7,13)"
                    else:
                        display_name = "%s (%i)" % (agents_dict[row['agent_id']].display_name, row['agent_id'])
                    creation_date = row['datestr']
                    total = row['hit_total_count']
                    unique = row['hit_unique_count']
                    try:
                        table[creation_date][display_name]['total'] += total
                        table[creation_date][display_name]['unique'] += unique
                    except:
                        pass
            except:
                logger.debug(traceback.format_exc())

            # All agents main query not as a sum
            all_monthly_result = Hit.objects \
            .filter(*args) \
            .extra(select={'datestr':"to_char(creation_date, 'YYYY-MM')"}) \
            .values('datestr') \
            .annotate(hit_total_count=Count('id')) \
            .annotate(hit_unique_count=Count('ip__id', distinct=True))

            try:
                for row in all_monthly_result:
                    display_name = "All*"
                    creation_date = row['datestr']
                    total = row['hit_total_count']
                    unique = row['hit_unique_count']
                    try:
                        table[creation_date][display_name]['total'] += total
                        table[creation_date][display_name]['unique'] += unique
                    except:
                        pass
            except:
                logger.debug(traceback.format_exc())

            result, column_names = sorted_table(table)
            context['result'] = result
            context['column_names'] = column_names
    else:
        versionform = VersionForm(initial=initial)

    context['version'] = version
    context['defaultversion'] = Version.objects.get(pk=1)
    context['versionform'] = versionform
    
    t = get_template(template)
    c = Context(request, context)
    rsp = t.render(c)
    return HttpResponse(rsp)
Example #3
0
def local_statistic(request):
    template = "registry/local_statistic.html"
    version = request.REQUEST.get('version', None)
    initial = {'version': version}

    context = {}
    if request.method == "POST":
        versionform = VersionForm(initial=initial, data=request.REQUEST.copy())
        if versionform.is_valid():

            args = list()

            startdate = versionform.cleaned_data['startdate']
            if startdate:
                args.append(Q(creation_date__gte=startdate))
                logger.debug("StartDate '%s'" % str(startdate))

            enddate = versionform.cleaned_data['enddate']
            if enddate:
                args.append(Q(creation_date__lte=enddate))
                logger.debug("EndDate '%s'" % str(enddate))

            agents = versionform.cleaned_data['agent']
            # agent is now mandatory
            #if not agents:
            #    agents = Agent.objects.all()
            agent_ids = tuple([a.id for a in agents])
            args.append(Q(agent__id__in=agent_ids))
            logger.debug("Agents '%s'" % str(agents))

            v = versionform.cleaned_data['version']
            if v and len(v) > 0:
                subargs = list()
                if v.startswith("5.0"):
                    subargs.append((Q(version__startswith=v)
                                    | Q(version__startswith='4.5')))
                elif v.startswith("4.2"):
                    subargs.append((Q(version__startswith=v)
                                    | Q(version__startswith='Beta-4.2')))
                elif v.startswith("4.1"):
                    subargs.append((Q(version__startswith=v)
                                    | Q(version__startswith='Beta-4.1')))
                else:
                    subargs.append(Q(version__startswith=v))
                agent_version = AgentVersion.objects.filter(*subargs)
                #agent_version_ids = tuple([a.id for a in agent_version])
                args.append(Q(agentversion__in=agent_version))
            # 5.0 AND (registry_hit.agent_version LIKE %s  OR registry_hit.agent_version LIKE '4.5%%')
            # 4.1 or 4.2 AND (registry_hit.agent_version LIKE %s OR registry_hit.agent_version LIKE 'Beta-4.1%%')

            ip_address = IP.objects.exclude(ip__regex=settings.IPLOCALREGEX)
            args.append(Q(ip__in=ip_address))
            # main query
            result = Hit.objects \
            .filter(*args) \
            .values('ip__domain__name', 'ip__organisation__name', 'agent__display_name') \
            .annotate(hit_total_count=Count('id')) \
            .annotate(hit_unique_count=Count('ip__id', distinct=True))

            stat_dict = dict()
            try:
                for row in result:
                    domain = row['ip__domain__name']
                    org = row['ip__organisation__name']
                    ag = row['agent__display_name']
                    counter = row['hit_total_count']
                    unique = row['hit_unique_count']
                    if org in stat_dict:
                        stat_dict[org][ag]["total"] = counter
                        stat_dict[org][ag]["unique"] = unique
                    else:
                        stat_dict[org] = dict()
                        stat_dict[org]["domain"] = domain
                        for a in agents:
                            if ag == a.display_name:
                                stat_dict[org][ag] = {
                                    "total": counter,
                                    "unique": unique
                                }
                            else:
                                stat_dict[org][a.display_name] = {
                                    "total": None,
                                    "unique": None
                                }
            except:
                logger.debug(traceback.format_exc())
            logger.debug("Organisations: %s" % (len(stat_dict.keys())))

            context['result'] = stat_dict
            context['agents'] = agents

    else:
        versionform = VersionForm(initial=initial)

    context['version'] = version
    context['defaultversion'] = Version.objects.get(pk=1)
    context['versionform'] = versionform

    t = get_template(template)
    c = Context(request, context)
    rsp = t.render(c)
    return HttpResponse(rsp)
Example #4
0
def local_statistic(request):
    template = "registry/local_statistic.html"
    version = request.REQUEST.get('version', None)
    initial = {'version':version}

    context = {}
    if request.method == "POST":
        versionform = VersionForm(initial=initial, data=request.REQUEST.copy())
        if versionform.is_valid():

            args = list()

            startdate = versionform.cleaned_data['startdate']
            if startdate:
                args.append(Q(creation_date__gte=startdate))
                logger.debug("StartDate '%s'" % str(startdate))

            enddate = versionform.cleaned_data['enddate']
            if enddate:
                args.append(Q(creation_date__lte=enddate))
                logger.debug("EndDate '%s'" % str(enddate))

            agents = versionform.cleaned_data['agent']
            # agent is now mandatory
            #if not agents:
            #    agents = Agent.objects.all()
            agent_ids = tuple([a.id for a in agents])
            args.append(Q(agent__id__in=agent_ids))
            logger.debug("Agents '%s'" % str(agents))

            v = versionform.cleaned_data['version']
            if v and len(v) > 0:
                subargs = list()
                if v.startswith("5.0"):
                    subargs.append((Q(version__startswith=v) | Q(version__startswith='4.5')))
                elif v.startswith("4.2"):
                    subargs.append((Q(version__startswith=v) | Q(version__startswith='Beta-4.2')))
                elif v.startswith("4.1"):
                    subargs.append((Q(version__startswith=v) | Q(version__startswith='Beta-4.1')))
                else:
                    subargs.append(Q(version__startswith=v))
                agent_version = AgentVersion.objects.filter(*subargs)
                #agent_version_ids = tuple([a.id for a in agent_version])
                args.append(Q(agentversion__in=agent_version))
            # 5.0 AND (registry_hit.agent_version LIKE %s  OR registry_hit.agent_version LIKE '4.5%%')
            # 4.1 or 4.2 AND (registry_hit.agent_version LIKE %s OR registry_hit.agent_version LIKE 'Beta-4.1%%')

            ip_address = IP.objects.exclude(ip__regex=settings.IPLOCALREGEX)
            args.append(Q(ip__in=ip_address))
            # main query
            result = Hit.objects \
            .filter(*args) \
            .values('ip__domain__name', 'ip__organisation__name', 'agent__display_name') \
            .annotate(hit_total_count=Count('id')) \
            .annotate(hit_unique_count=Count('ip__id', distinct=True))
            
            stat_dict = dict()
            try:
                for row in result:
                    domain = row['ip__domain__name']
                    org = row['ip__organisation__name']
                    ag = row['agent__display_name']
                    counter = row['hit_total_count']
                    unique = row['hit_unique_count']
                    if org in stat_dict:
                        stat_dict[org][ag]["total"] = counter
                        stat_dict[org][ag]["unique"] = unique
                    else:
                        stat_dict[org] = dict()
                        stat_dict[org]["domain"] = domain
                        for a in agents:
                            if ag == a.display_name:
                                stat_dict[org][ag] = {"total":counter, "unique": unique}
                            else:
                                stat_dict[org][a.display_name] = {"total": None, "unique": None}
            except:
                logger.debug(traceback.format_exc())
            logger.debug("Organisations: %s" % (len(stat_dict.keys())))
            
            context['result'] = stat_dict
            context['agents'] = agents
        
    else:
        versionform = VersionForm(initial=initial)

    context['version'] = version
    context['defaultversion'] = Version.objects.get(pk=1)
    context['versionform'] = versionform
    
    t = get_template(template)
    c = Context(request, context)
    rsp = t.render(c)
    return HttpResponse(rsp)