How can I use timestamps from 2 different sources and calucate them inorder to find the difference and convert in number of weeks?
Give this a shot
index=nvd sourcetype=vuln_feed CVE="CVE-2016-0646" |stats first(_time) as Recent_Time | appendcols [search index=nessusta sourcetype=nessus:plugin cve="CVE-2016-0646" |stats first(_time) as Recent_Time2] | eval NoOfWeeks=floor(abs(Recent_Time2-Recent_Time)/86400/7) | convert ctime(Recent_Time*) as Recent_Time*
Update
Try like this to compare individual CVE/cve values.
(index=nvd sourcetype=vuln_feed) OR (index=nessusta sourcetype=nessus:plugin) | eval CVE=coalesce(cve,CVE) |chart first(_time) as Recent_Time over CVE by index | rename nvd as Recent_Time nessusta as Recent_Time2 | eval NoOfWeeks=floor(abs(Recent_Time2-Recent_Time)/86400/7) | convert ctime(Recent_Time*) as Recent_Time*
(index=nvd sourcetype=vuln_feed ) OR (index=nessusta sourcetype=nessus:plugin)|eval CVE1=coalesce(CVE,cve)|chart first(_time) as Recent_Time over CVE1 by index | rename nvd as Recent_Time, nessusta as Recent_Time2 | eval NoOfWeeks=floor(abs(Recent_Time2-Recent_Time)/86400/7) | convert ctime(Recent_Time*) as Recent_Time*| eval Sev= case(NoOfWeeks>=0 AND NoOfWeeks<=4,"High",NoOfWeeks>=5 AND NoOfWeeks<=8,"Medium",NoOfWeeks>=8 AND NoOfWeeks<=16,"Low")
Need some suggestion, in the above statement coalesce(CVE,cve), the list of" CVE" ahs 100+ values where as cve has only 2 values
and when I run the above query. this is what I see below
CVE1 Recent_time2 NoOfWeeks Recent_time Sev
CVE-2016-5672 8/16 0 08/08/2016 22:04:24 High
However I am find how I can find % of CVE and cve and then search it by Sev(High Low Medium)
.
.
A point of clarification - do you mean "week" as in a Sunday - Saturday interval or any 7 days? If I give you the timestamps "2016-08-10 12:00:00" and "2016-08-15 12:00:00" is there a 1 week difference, or a zero week difference?
so based on your timestamps its 5 days and my objective is to identify the #ofdays (I am sorry if that was a confusion) in the earlier post) and eventually bucket them into different categories. for eg if #days difference is 14 days, then its 2 weeks and its being categorized into a specific bucket
Give this a shot
index=nvd sourcetype=vuln_feed CVE="CVE-2016-0646" |stats first(_time) as Recent_Time | appendcols [search index=nessusta sourcetype=nessus:plugin cve="CVE-2016-0646" |stats first(_time) as Recent_Time2] | eval NoOfWeeks=floor(abs(Recent_Time2-Recent_Time)/86400/7) | convert ctime(Recent_Time*) as Recent_Time*
Update
Try like this to compare individual CVE/cve values.
(index=nvd sourcetype=vuln_feed) OR (index=nessusta sourcetype=nessus:plugin) | eval CVE=coalesce(cve,CVE) |chart first(_time) as Recent_Time over CVE by index | rename nvd as Recent_Time nessusta as Recent_Time2 | eval NoOfWeeks=floor(abs(Recent_Time2-Recent_Time)/86400/7) | convert ctime(Recent_Time*) as Recent_Time*
Even though the above query works, it only work for specific equal values. How can I make it more generalized for CVE = cve and list out all those CVE values.
index=nvd sourcetype=vuln_feed |table CVE |dedup CVE|stats first(_time) as Recent_Time | appendcols [search index=nessusta sourcetype=nessus:plugin |table cve |dedup cve|stats first(_time) as Recent_Time2] |where CVE=cve | eval NoOfWeeks=floor(abs(Recent_Time2-Recent_Time)/86400/7) | convert ctime(Recent_Time*) as Recent_Time*
The first index is large dataset compared to the second one. that why I had to table it and then dedup it
(index=nvd sourcetype=vuln_feed) OR (index=nessusta sourcetype=nessus:plugin) | eval CVE=coalesce(cve,CVE) |chart first(_time) as Recent_Time over CVE by index | rename nvd as Recent_Time nessusta as Recent_Time2 | eval NoOfWeeks=floor(abs(Recent_Time2-Recent_Time)/86400/7) | convert ctime(Recent_Time*) as Recent_Time*
I tried this and it only display values from nessusta index and not nvd. when switch around in coalesce(CVE,cve) it displays values of nvd but not nessusta. So I think coalesce would not be the right option
Does the field CVE OR cve exists in both the sources? I assumed that both fields doesn't exists in the same event. If that is not the case, replace | eval CVE=coalesce(cve,CVE)
with | eval CVE=if(index="nvd",CVE,cve)
Can you explain the coalesce function in splunk? Does it append values from 2 different fields into one field?
coalesce
in Splunk works just like coalesce
in SQL. Given a set of arguments it returns the first not-NULL argument.
When in doubt, look at Splunk documentation.
Search Commands
http://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/Commandsbycategory
Eval/Where functions
http://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/CommonEvalFunctions
Got it thanks
Question: I wrote this following query
index=nessusta sourcetype=nessus:plugin |table cve{},factor| mvexpand cve{}|stats dc(cve{}) as totalcve by factor |eval TotalCVE = [search index="summary" source="Total Number of CVE from NVD" | return $TotalCVE ] |eval perc=tostring(totalcve/TotalCVE*100,"commas")."%"
OUtPUT
factor totalcve TotalCVE perc
High 24798 78548 31.57%
Low 1253 78548 1.60%
Medium 12099 78548 15.40%
is there a way to list out CVE % coverage by factor.
Could you provide (based on your sample data above) what field to be created and what expected value will be?
You would need to provide more information than this to get an accurate answer. What are these two sources? Share a sample query, field names and sample events from each source highlighting the relevant data? Share your mock output with examples.
index=nvd sourcetype=vuln_feed CVE="CVE-2016-0646" |stats first(_time) as TimeStamp | convert ctime(TimeStamp) as Recent_Time | fields - TimeStamp | appendcols [search index=nessusta sourcetype=nessus:plugin cve="CVE-2016-0646" |stats first(_time) as TimeStamp1 | convert ctime(TimeStamp1) as Recent_Time2 | fields - TimeStamp1]
In the above query, Currently I am displaying timestamp from 2 different sources for a particular value which hare present in both the sources but has different timestamp. One is 4/4 and other is 11/4. I am trying to dval the difference in dates here to find # of weeks