Calculation multiple events for actual duration for each event and average duration for all users

[roy_tsangdd]

Hi All,

I am working on WIFI data, within the dataset, there are the following values
1. MAC_address
2. Association_Time
3. Disassociation_Time

Since WIFI log will register MAC address, and their association time and disassociation time, it is easily to calculate the duration based on these two values. The challenge is, when a device roaming to another AP, there is a period of co-exist for this device on both AP, hence, there will be some overlapping time period. I would like to write a query to calculate the "Actual Duration" for a device using the WIFI network, regardless of which AP it is associated.

So the logic is,
1. Identify duplicated MAC address.
2. Check their "Association Time" and "Disassociation Time" for all events to check if there is any overlapping period.
3. If overlapping period is identified, it will cut the total period of the previous event. e.g.
Event 1: 10:30AM-11:00AM
Event 2: 10:50AM-11:20AM
Event 3: 11:10AM-11:40AM
So the "Actual" total time for each event after adjusting the overlapping period will be
Event 1: 10:30AM-10:50AM (20Min)
Event 2: 10:50AM-11:10AM (20Min)
Event 3: 11:10AM-11:40AM (30Min)

Since we have to take the WIFI zoning into consideration, we can't simply take the total duration 10:30AM-11:40AM (1H:10MIn)

Anyone help is much appreciated!

[somesoni2]

Assuming both Association_Time and Disassociation_Time fields are in same event and the format of the field is HH:MM, then try something like this

 your base search |  eval Duration=strptime(Disassociation_Time, "%H:%M")-strptime(Association_Time,"%H:%M")  | stats sum(Duration) as Duration by MAC_Address

If Association_Time and Disassociation_Time fields are not in same event and the format of the field is HH:MM, then try something like this

your base search | stats list(Association_Time) as Association_Time list(Disassociation_Time) as Disassociation_Time by MAC_Address | eval temp=mvzip(Association_Time,Disassociation_Time,"#") | table MAC_Address temp | mvexpand temp | rex field=temp "(?<Association_Time>.+)#(?<Disassociation_Time>.+)" |  eval Duration=strptime(Disassociation_Time, "%H:%M")-strptime(Association_Time,"%H:%M")  | stats sum(Duration) as Duration by MAC_Address

[Richfez]

Nice answer. Funny part is I have this sort of data at work in a system we inherited a few weeks ago but hadn't realized it until just now. I will be bookmarking your answer here somesoni2 for my own use later.

[Richfez]

It might be that you could do this without any work at all by using transaction. If you have all the traffic (and not just the associate and disassociate events), then try this:

base search here | transaction maxpause=5m MAC 

Notice we don't specify anything about the AP they're connected to, because we actually don't care. This will connect up all events by MAC, and if it finds any period of time greater than 5 minutes where there wasn't any data from a particular MAC it'll break that transaction off at that point. In fact, you don't even need to worry about association and disassociation events AT ALL because transaction will just pile up ALL event together involving each MAC. If you have fairly constant traffic from the devices (to fill in "5 minute gaps" well) this should work fine.

If you find you have multiple shorter transactions per device instead of one longer one covering the whole period, you should be able to sum them over the duration with stats and get your total.

base search here | transaction maxpause=5m MAC 
| stats sum(duration) by MAC

If your base search only has associate and disassociate events in it and not all the traffic, then another option might be,;

base search | stats earliest(_time) as FirstAssociation latest(_time) as LastAssociation by MAC
| eval Duration=LastAssociation-FirstAssociation 

This should be "lighter" and faster than the transaction, as well. Duration will be in seconds.

Both of these may take fine tuning - I don't have your original events to make a good pretend attempt with. So if you need more help, pasting a couple of each type of event may be very helpful to us, as somesoni2 mentioned above.

And lastly, there's quite a few tricky things that can be done if those won't work, so don't hesitate to paste in some more-real data and ask!

[roy_tsangdd]

BTW, How to write a query to search for overlapping events with the same MAC address?

[Richfez]

You can get concurrency with the concurrency command. This is a little harder without some sample events, but there are good examples at the link above, especially the run-anywhere example #4. The key is you'll want to be sure to give it the duration field.

You could also, possibly, just pipe to timechart count by MAC. For instance, if I do something like this to my data, which is not quite like yours but works as close enough for now...

index=* host="(U7P*" src_mac="*" |  timechart count by src_mac useother=f

Gives me a nice little chart of how "much stuff" each MAC "does". If you piped your output to timechart, you should get a count of how many APs it was associated with as a chart (because that's the only information left after the transaction).

It's worth asking, though - So you have wireless devices that roam, and as they roam the "old" access point they had been attached to doesn't disassociate them immediately but instead "times out" after a while of no data. What you are trying to record here seems to be a very poor substitute of how many times they hop from AP to AP. The better answer (that of how many times they hopped in some timespan) is an answer which is mostly already contained in the above searches somesoni2 and I have supplied. We can probably help more with that, but having actual events would be helpful, and knowing to what use you are going to put this information to will help a lot too. Sometimes the answer we need isn't the answer we thought we wanted.

[somesoni2]

Can you provide some events?

[roy_tsangdd]

Thanks for your help, kindly find the sample event in CSV format

Client Username,Client IP Address,Client MAC Address,Association Time,Vendor,AP

Name,Device Name,Map Location,SSID,Profile,VLAN ID,Protocol,Session

Duration,Policy Type,Avg. Session Throughput (Kbps),CCX,AP MAC Address,AP IP,AP

Radio,Association ID,Disassociation Time,Encryption Cipher,EAP

Type,Authentication Algorithm,Web Security,Bytes Sent,Bytes Received,Packets

Sent,Packets Received,SNR (dBm),RSSI,Status,Reason,E2E,Data Retries,RTS

Retries,Mobility Status
,192.168.1.49,00:00:02:ad:f0:fe,Wed Sep 30 10:30:00 HKT 2015,Apple,AP1,AP1,Zone

1,Free WIFI,Free WIFI,1,802.11n(2.4GHz),30 min,NOTAVAILABLE,97.9,Not

Supported,aa:ba:ee:c3:e3:c0,168.199.1.10,802.11b/g/n,3,Wed Sep 30 11:00:03 HKT

2015,None,Unknown,OPENSYSTEM,Disable,3419443,39010680,38187,39318,47,-

52,Disassociated,Disassociation detected,Not Supported,48901,0,Export Anchored
,192.168.2.49,00:00:02:ad:f0:fe,Wed Sep 30 10:50:44 HKT 2015,Apple,AP2,AP2,Zone

2,Free WIFI,Free WIFI,1,802.11n(2.4GHz),30 min,NOTAVAILABLE,3.8,Not

Supported,aa:ba:ee:f8:03:80,168.199.1.11,802.11b/g/n,150,Wed Sep 30 11:20:08 HKT

2015,None,Unknown,OPENSYSTEM,Disable,668637,1731468,10873,3093,31,-

68,Disassociated,New association detected,Not Supported,7557,0,Export Anchored
,192.168.3.49,00:00:02:ad:f0:fe,Wed Sep 30 11:10:08 HKT 2015,Apple,AP3,AP3,Zone

3,Free WIFI,Free WIFI,1,802.11n(2.4GHz),30 min,NOTAVAILABLE,18.4,Not

Supported,aa:ba:ee:c3:f7:d0,168.199.1.12,802.11b/g/n,114,Wed Sep 30 11:40:31 HKT

2015,None,Unknown,OPENSYSTEM,Disable,936071,3494265,14309,5197,31,-

66,Disassociated,No longer seen from controller,Not Supported,13071,0,Export

Anchored

[roy_tsangdd]

How to write a query to search for overlapping events with the same MAC address?