Splunk Search

Calculate difference between 2 timestamp to find number of week?

ashishlal82
Explorer

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

Tags (2)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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* 

View solution in original post

ashishlal82
Explorer

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

0 Karma

dwaddle
SplunkTrust
SplunkTrust

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?

0 Karma

ashishlal82
Explorer

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma

ashishlal82
Explorer

(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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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)

0 Karma

ashishlal82
Explorer

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

0 Karma

dwaddle
SplunkTrust
SplunkTrust

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust
0 Karma

ashishlal82
Explorer

Got it thanks

0 Karma

ashishlal82
Explorer

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

ashishlal82
Explorer

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

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