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!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...