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)
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)
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)
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)
