Splunk Search

Timechart on dates in a file that may or may not change

Explorer

I want a rolling 12 month bar chart. I have a lookup file (flagcve.csv) as follows.

CVE,ReleaseDate
CVE-2017-0144, 03/14/17
CVE-2017-0145, 03/14/17
CVE-2017-0199, 04/10/17
CVE-2017-5689, 05/01/17
CVE-2017-5715, 01/03/18

I have this search/query.
|inputlookup flagcve.csv | convert timeformat="%m/%d/%y" mktime(ReleaseDate) AS _time
| timechart bins=12 span=1mon count(CVE)

I get this result.
2017-03 2
2017-04 1
2017-05 1
2017-06 0
2017-07 0
2017-08 0
2017-09 0
2017-10 0
2017-11 0
2017-12 0
2018-01 1

That's almost what I want. But if this is January, it should go back to 2017-02. If this is Feb 2018, and no change is made to the file, it should be

2017-03 2
2017-04 1
2017-05 1
2017-06 0
2017-07 0
2017-08 0
2017-09 0
2017-10 0
2017-11 0
2017-12 0
2018-01 1
2018-02 0

Whereas if a change was made to the file (such as CVE-2017-6666 02/15/18 was added), it would have the proper count (1 in this case) for 2018-02 instead of 0.

Ideas?

Tags (3)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

Try this -

| inputlookup flagcve.csv 
| eval _time = relative_time(strptime(ReleaseDate,"%m/%d/%y"),"@mon")
| stats count as mycount by _time
| rename COMMENT as "The above counts up the number of records per month that are present"

| rename COMMENT as "Now we add 12 flagged records with mycount=0 for the last twelve months"
| appendpipe [ 
   | stats count as mycount
   | eval _time = relative_time(now(),"@mon") 
   | eval months=mvrange(0,12) 
   | mvexpand months 
   | eval months="-".months."mon" 
   | eval _time = relative_time(_time,months) 
   | eval mycount = 0
   | eval myflag="keepme"
   | table _time mycount myflag
   ] 

| rename COMMENT as "roll up the records and throw out any that aren't flagged"
| stats values(myflag) as myflag, sum(mycount) as mycount by _time
| where isnotnull(myflag)

corrected typo @m to @mon

View solution in original post

SplunkTrust
SplunkTrust

Try this -

| inputlookup flagcve.csv 
| eval _time = relative_time(strptime(ReleaseDate,"%m/%d/%y"),"@mon")
| stats count as mycount by _time
| rename COMMENT as "The above counts up the number of records per month that are present"

| rename COMMENT as "Now we add 12 flagged records with mycount=0 for the last twelve months"
| appendpipe [ 
   | stats count as mycount
   | eval _time = relative_time(now(),"@mon") 
   | eval months=mvrange(0,12) 
   | mvexpand months 
   | eval months="-".months."mon" 
   | eval _time = relative_time(_time,months) 
   | eval mycount = 0
   | eval myflag="keepme"
   | table _time mycount myflag
   ] 

| rename COMMENT as "roll up the records and throw out any that aren't flagged"
| stats values(myflag) as myflag, sum(mycount) as mycount by _time
| where isnotnull(myflag)

corrected typo @m to @mon

View solution in original post

Explorer

That is a lot of Splunking for what seemed like a simple problem, but with some tweaks, it works. Had to change line 2 to use "@mon" instead of "@m", and add table statement to eliminate the unwanted myflag column.

Is there a way to rename _time? When I tried rename or eval, it converted the value from yyyy-mm back to epoch time.

Final solution is:

| inputlookup flagcve.csv
| eval _time = relative_time(strptime(ReleaseDate,"%m/%d/%y"),"@mon")
| stats count as mycount by _time
| rename COMMENT as "The above counts up the number of records per month that are present"
| rename COMMENT as "Now we add 12 flagged records with mycount=0 for the last twelve months"
| appendpipe [
| stats count as mycount
| eval _time = relative_time(now(),"@mon")
| eval months=mvrange(0,12)
| mvexpand months
| eval months="-".months."mon"
| eval _time = relative_time(_time,months)
| eval mycount = 0
| eval myflag="keepme"
| table _time mycount myflag
]
| rename COMMENT as "roll up the records and throw out any that aren't flagged"
| stats values(myflag) as myflag, sum(mycount) as mycount by _time
| where isnotnull(myflag)
| table _time mycount

Thanks!

0 Karma

SplunkTrust
SplunkTrust

@claatu - yes, _time is special. It is internally stored as epoch, but converts automatically to display. So, you can just eval another name to strftime(_time,"%Y-%m") and throw away _time, or you can rename it all the way through and then use eval or convert at the end.

 | inputlookup flagcve.csv 
 | eval Time = relative_time(strptime(ReleaseDate,"%m/%d/%y"),"@mon")
 | stats count as mycount by Time
 | rename COMMENT as "The above counts up the number of records per month that are present"
 | rename COMMENT as "Now we add 12 flagged records with mycount=0 for the last twelve months"
 | appendpipe [ 
    | stats count as mycount
    | eval Time = relative_time(now(),"@mon") 
    | eval months=mvrange(0,12) 
    | mvexpand months 
    | eval months="-".months."mon" 
    | eval Time = relative_time(Time,months) 
    | eval mycount = 0
    | eval myflag="keepme"
    | table Time mycount myflag
 ] 
| rename COMMENT as "roll up the records and throw out any that aren't flagged"
| stats values(myflag) as myflag, sum(mycount) as mycount by Time
| where isnotnull(myflag)
| eval Time = strftime(Time,"%Y-%m")
| table Time mycount

Yes, it is a bit of work, but I like to make sure that all conditions are checked and the code will work for all the foreseeable conditions - for instance, a month in the middle with no results.

0 Karma