Creating a suspect list with cell tower data
A unknown criminal has robbed three stores in three neighboring towns, one after another, in under an hour. You don't have any suspects and need help determining how to narrow down the start of your investigation.
Required data
- Call detail record (CDR) data
- A lookup file that maps cell tower names to locations
How to use Splunk software for this use case
Most likely, the suspect had a cell phone in their pocket or car and that phone sent signals of its whereabouts to its corresponding cell tower. Therefore, a good place to begin an investigation is by looking at the cell towers associated with those stores and finding common cell numbers that involved all three towers.
Numbers found on all three do not automatically identify culprits. Someone could be driving from location to location in that time period for other reasons. However, you can use CDR data to quickly narrow down the list of suspects, then use other means to identify with more positivity whether someone is the criminal, such as a prior history of such acts.
Using the information from the cell tower logs, the Splunk platform can be used to correlate which cell phone towers had common phones attached to them. This example uses Greenboro, Newtown, and Roseville as fictitious names. Here is the search.
index=cell_tower_logs sourcetype="switch_cdr" |lookup switch_place location OUTPUT place |stats values(place) AS towers dc(place) AS distinguished_places BY Caller |makemv delim="," towers |where distinguished_places=3 AND towers = "Greenboro Noontown Roseville"
Search explanation
The table provides an explanation of what each part of this search achieves. You can adjust this query based on the specifics of your environment.
| Splunk Search | Explanation |
|---|---|
| index=cell_tower_logs sourcetype="switch_cdr" | Limit your search to the correct index and data source. |
| |lookup switch_place location OUTPUT place | Each cell tower has a cryptic name, so you need a lookup file to map the name to an actual place. |
| |stats values(place) AS towers dc(place) AS distinguished_places BY Caller | The Splunk stats command gives a list of unique values for the places where the towers are located and distinct counts (dc) of the number of towers (as we are interested in only 3). |
| |makemv delim="," towers | The makemv command turns the list of cell towers into a multi-value field. |
| |where distinguished_places=3 AND towers = "Greenboro Noontown Roseville" | The where clause tells us if the phone number was in three unique locations for the time period and if those are the locations where the crimes were committed. |
Results
The results have narrowed the options down to three cell phones. You can begin to investigate the owners of those numbers.
The approach shown in this article uses an absolute 30 or 60 minute period in Splunk's time picker to look for the information. How do you know which exact 30 or 60 minute period the robberies occurred in? To get around this shortcoming, you can use Splunk’s streamstats command. This is a lot like stats, but it allows a running time window to be used as an argument to the command so that any 30 or 60 minute time period from a longer duration will find the results.
With streamstats and the approximate start and end times for the robberies, you could select a one hour and 15 minute time period to look for the data, as shown in the following search. This search also uses the table command to get the same type of readable results as provided in the previous search, since streamstats is not a transforming command
index=cell_tower_logs sourcetype="switch_cdr" |lookup switch_place location OUTPUT place |streamstats time_window=30m values(place) AS towers dc(place) AS distinguished_places BY Caller |makemv delim="," towers where distinguished_places=3 AND towers = "Greenboro Noontown Roseville" |table Caller towers distinguished_places
Next steps
The content in this use case comes from a previously published blog, one of the thousands of Splunk resources available to help users succeed. You might also be interested in other processes associated with the building a data-driven law enforcement strategy use case.