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

View solution in original post

0 Karma

john_dagostino
Path Finder

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!