How to execute annotate in Django model with foreign keys?

user_2926503 0
Viewed 72

Django novice question here :)

I have the following model

Domain:
   domain_name
   domain_description

These domains can be scanned multiple times

The other model is

ScanHistory:
   last_scan_date
   scan_status
   domain_name = models.ForeignKey(Domain, on_delete=models.CASCADE)

When scanned, these domains produce several subdomains

ScannedHost:
    subdomain
    cname
    scan_history = models.ForeignKey(ScanHistory, on_delete=models.CASCADE)

These subdomains will have vulnerabilities associated with it

VulnerabilityScan:
    vulnerability_of = models.ForeignKey(ScanHistory, on_delete=models.CASCADE)
    severity
    vulnerability_name

How do I find out the domain which are top 3 most vulnerable Domain? A most vulnerable Domain is any domain that has the highest count of Unique Vulnerability despite n numbers of time it is scanned.

I stumbled upon Django annotate, but I don't seem to solve this.

django-models django
1 Answers

You can annotate with the Count of distinct vulnerability_names:

Domain.objects.annotate(
    num_vul=Count(
        'scanhistory__scannedhost__vulnerabilityscan__vulnerability_name',
        distinct=True
    )
).order_by('-num_vul')[:3]

The distinct=True [Django-doc] part will ensure that we count each vulnerability_name only once.

We here query the ForeignKeys in reverse, we do this by using the related_query_name=… [Django-doc] which specifys how to query in reverse. In case that is not specified, Django will use the related_name=… [Django-doc], and if both are not specified use the name of the class in lowercase, so scanhistory, scannedhost, and vulnerabilityscan.

That being said, it might be better to model this with a Vulnerability model, that can contain the name of the vulnerability, the severity, etc. so:

class Vulnerability(models.Model):
    name = models.CharField(max_length=128, unique=True)
    severity = models.IntegerField()

class VulnerabilityScan(models.Model):
    vulnerability_of = models.ForeignKey(ScanHistory, on_delete=models.CASCADE)
    vulnerability = models.ForeignKey(
        Vulnerability,
        on_delete=models.CASCADE
    )

Then we can count with:

Domain.objects.annotate(
    num_vul=Count(
        'scanhistory__scannedhost__vulnerabilityscan__vulnerability',
        distinct=True
    )
).order_by('-num_vul')[:3]
user_675790
Related