Calculate difference between 2 timestamp to find number of week?

[ashishlal82]

How can I use timestamps from 2 different sources and calucate them inorder to find the difference and convert in number of weeks?

[ashishlal82]

(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)
.
.

[dwaddle]

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?

[ashishlal82]

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

[somesoni2 · Accepted solution]

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* 

[ashishlal82]

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

[ashishlal82]

(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

[somesoni2]

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)

[ashishlal82]

Can you explain the coalesce function in splunk? Does it append values from 2 different fields into one field?

[dwaddle]

coalesce in Splunk works just like coalesce in SQL. Given a set of arguments it returns the first not-NULL argument.

[somesoni2]

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

[ashishlal82]

Got it thanks

[ashishlal82]

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.

[somesoni2]

Could you provide (based on your sample data above) what field to be created and what expected value will be?

[somesoni2]

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.

[ashishlal82]

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