Report on IOT inventory of devices vs events generated

[mcuervo]

Hi,

I am new to Splunk, but after reading all the documentation, I am still not able to find a solution for my scenario.

I have a relatively big farm of IOT devices, around 100.000. And I have two sources of data:

1. An inventory of all the devices. This inventory changes daily as new devices are deploy and others are decommissioned.
2. A continuous flow of events coming from those IOT devices, with around 1.000.000 events per month

Each device has a unique identifier that can be found both in the inventory and in the events.

What I want to produce is a set of reports that respond to these questions:

• List of IOT devices in the inventory that has not produced any event in the last 30 days?
• List of IOT devices that are producing events but they are not listed in the inventory?

The first question will help me to identify broken devices. The second will help me to identify problems with the inventory.

How can I implement this with Splunk?

Thanks in advance,

Miguel

[smoir_splunk]

Alternately, you can check out Splunk Industrial Asset Intelligence: https://splunkbase.splunk.com/app/4218/ which has an asset hierarchy that should handle your inventory, and the events can be ingested as metrics data and associated with the assets accordingly.

[DalJeanis]

First, ingest the data.

Second, write reports that pull the information.

Your list of devices can be either a lookup table or an index.

If it's an index, then you will want to have a status flag so that you can identify which devices are decommissioned. Splunk does not have a capability to "update" a record, so the most recent record for each device has to tell you what you need to know.

There are more, and less efficient ways to code such a query, and they are data-dependent, so I'm just going to give you a workable one.

Let's suppose you have a lookup table called mylookup.csv with the following fields - deviceID, ActivationDate, ActivationEpoch, DecommissionDate, DecommissionEpoch. The two Epoch timestamps are in epoch time format so that they can be directly compared against the field _time.

your query that pulls all the events from the devices
| fields deviceID _time
| bin _time span=1d
| stats count by deviceID _time
| lookup mylookup.csv deviceID OUTPUT ActivationDate ActivationEpoch DecommissionDate DecommissionEpoch
| eval flag=case(isnull(ActivationDate),"Device Not on File",  
    ActivationEpoch>_time,"Reporting Before Activation",
    DecommissionEpoch<_time,"Reporting After Decommission",
    true(),"Active")

Once you have the above, you can filter the results based on the flags for your two requested reports.

I might put the results of this query into either a summary index for historical reporting, or a csv or lookup table and do the reporting off of those, but that's a minor consideration.

[mcuervo]

Thank a lot Dal

[mcuervo]

Hi Dal,

I have implemented your solution, but I found out that only respond to the second question, that is, devices that have produce events and that have some issues with the inventory.

But I am still not able to find those devices that have been without calling home for a period of time. How could I implement this case?

Thanks in advance,

Miguel

[DalJeanis]

@mcuervo - add this onto the end. It should create one record for each device in the lookup that is missing from the most recent date.

| appendpipe [
    | eventstats max(_time) as maxtime
    | where _time = maxtime
    | stats values(_time) as _time by deviceID
    | eval flag="killme" 
    | inputlookup mylookup.csv append=t 
    | eventstats max(flag) as flag by deviceID
    | eventstats max(_time) as _time 
    | where isnull(flag)
    | eval flag="missing" 
    ]