Splunk DB Connect: How to get a report of the latest event (device status) for each device in my database?

[rameshlpatel]

Hi,

I have configured DB Connect to get latest data from database to get latest device status like Online/Offline. I have more then 12k devices and same data coming from the database each minute. At the time of creating the SPLUNK report, I have to find the latest event coming from each device to get latest device status.

Please could you help me how I can achieve this?

[sanjay_shrestha]

You can create a scheduled saved search to create a lookup file

| dbquery "YOURDATABASE" "select * from  deviceinfo"|outputlookup device_status.csv

Then use it in your query

index=your_index .....|lookup device_status.csv device_id output device_status

[rameshlpatel]

thanks Sanjay for your answer, however I am storing data in index as key-value pair and not using csv.

[sanjay_shrestha]

you can try like this:

index=device_index... | join device_id [search index=yourdevicestatus_index|stats first(device_status)  by device_id]