0

Here's what my models.py looks like in its most simplified form:

from django.db import models

class Device(models.Model):
    description = models.CharField(max_length=30)
    ignored = models.BooleanField()

class Mac(models.Model):
    mac = models.CharField(max_length=17, primary_key=True)
    device = models.ForeignKey('Device')

class SeenEvent(models.Model):
    mac = models.ForeignKey('Mac')
    date = models.DateTimeField()

In the SeenEvent model, I record times when a particular MAC address was seen. A Unix daemon scans the network periodically and then adds SeenEvent objects to the database.

I'd like to generate a report of which Devices (or MAC address) were seen in the last - for example - 10 minutes (the "timeout" period). For these devices, I'd also like to specify when were the latest streak of events started - i.e. when was the latest first appearance of the device. Can I achieve that without pulling all SeenEvents for the given MACs?

One of my ideas relied on creating streak_id in SeenEvent - before adding a new event, the Unix daemon would see when was the last time that the Mac was seen and - if it happened more than 10 minutes ago, assign a new *group_id* counter for the newly created event. Is that a good idea?

d33tah
  • 10,999
  • 13
  • 68
  • 158
  • `For these devices, I'd also like to specify when were the latest chain of events started` This is inside the 10 min boundaries? – Raydel Miranda Dec 20 '13 at 19:28
  • Well, yes, though I'd like to be able to tell the exact minute when the first event in the chain happened. – d33tah Dec 20 '13 at 19:31

2 Answers2

0

I'd say keep it simple, use django and python ability to sort by date and reply whatever you need, something like:

from datetime import datetime, timedelta

time_threshold = datetime.now() - timedelta(minutes=10)
result = SeenEvent.objects.filter(date__gt=time_threshold).order_by('-date')[0]
Guy Gavriely
  • 11,228
  • 6
  • 27
  • 42
  • So you're basically saying that I need to pull all the SendEvent objects (possibly, filtering just the ones related to the Device I'm interested in) as there's no better way? – d33tah Dec 20 '13 at 19:42
  • Im basically saying let python/django/mysql(?) do the job, they will probably do it much better than whatever you'll end up doing – Guy Gavriely Dec 20 '13 at 19:44
  • Well, I don't think it will do the job this way, it'll just pull all the records. – d33tah Dec 20 '13 at 19:45
  • No it won't, it will just pull the top 1, that's the query the will be generated – Guy Gavriely Dec 20 '13 at 19:49
  • Ah, just noticed the [0]. Anyway, this won't tell me when the first event in the chain happened. – d33tah Dec 20 '13 at 19:50
  • it will tell you what is the oldest event that happened in the last 10 minutes, isn't that what you want? – Guy Gavriely Dec 20 '13 at 20:00
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/43621/discussion-between-guy-and-d33tah) – Guy Gavriely Dec 20 '13 at 20:04
0

You can perform a raw sql query (filter is much easier) on your SeenEvent in order to obtain the events for the last 10 min. And then obtain which are consecutive (I assuming that's what you mean with chain). See this answer about consecutive values of a column.

How to find consecutive rows based on the value of a column?

Community
  • 1
  • 1
Raydel Miranda
  • 13,825
  • 3
  • 38
  • 60