Splunk Search

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

claatu
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

DalJeanis
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

DalJeanis
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

claatu
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

DalJeanis
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...