Splunk Search

How to timechart the count of assets per month based on a range between two dates for each asset?

john_dagostino
Path Finder

In my data, I have a list of assets that occur with a "First Found" date as well as a "Last Found" date. I need to generate a timechart so that each asset is counted for the months that they are "active" (eg anything between the first/last found dates).

asset  first_found  last_found
Host1  01/01/2016   05/01/2016
Host2  03/15/2016   04/01/2016
Host3  02/10/2016   05/01/2016
Host4  05/01/2016   06/26/2016
Host5  03/01/2016  

What I'm looking for using the sample data above is a timechart count by month of each asset that occurred during that month. For January, the count would be 1 (Host1), February would be 2 (Host1, Host2), March would be 4 (Host1, Host2, Host3, Host5), etc. Some events will not have the last_found date which means they are still active and should be counted up to and including the current month. Any help would be appreciated.

0 Karma
1 Solution

sundareshr
Legend

Try this

your base search 
| eval first_found=strptime(first_found, "%m/%d/%Y")  
| eval last_found=strptime(last_found, "%m/%d/%Y") 
| eval last_found=if(isnull(last_found), now(), last_found) 
| eval range=mvrange(first_found, last_found, "1mon") 
| mvexpand range 
| eval range=strftime(range, "%m-%b") 
| chart count over range by asset 
| addtotals

View solution in original post

0 Karma

sundareshr
Legend

Try this

your base search 
| eval first_found=strptime(first_found, "%m/%d/%Y")  
| eval last_found=strptime(last_found, "%m/%d/%Y") 
| eval last_found=if(isnull(last_found), now(), last_found) 
| eval range=mvrange(first_found, last_found, "1mon") 
| mvexpand range 
| eval range=strftime(range, "%m-%b") 
| chart count over range by asset 
| addtotals
0 Karma

john_dagostino
Path Finder

Thank you, I was able to get a modified version of this to work.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...