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?
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
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
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!
@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.