topic Re: Calculate difference between 2 timestamp to find number of week? in Splunk Search

Calculate difference between 2 timestamp to find number of week?

ashishlal82 — Tue, 23 Aug 2016 13:48:03 GMT

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

Re: Calculate difference between 2 timestamp to find number of week?

somesoni2 — Tue, 23 Aug 2016 15:14:05 GMT

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.

Re: Calculate difference between 2 timestamp to find number of week?

ashishlal82 — Tue, 29 Sep 2020 10:40:27 GMT

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

Re: Calculate difference between 2 timestamp to find number of week?

somesoni2 — Tue, 23 Aug 2016 15:35:10 GMT

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*

Re: Calculate difference between 2 timestamp to find number of week?

dwaddle — Tue, 23 Aug 2016 15:36:20 GMT

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?

Re: Calculate difference between 2 timestamp to find number of week?

ashishlal82 — Tue, 23 Aug 2016 15:46:43 GMT

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

Re: Calculate difference between 2 timestamp to find number of week?

ashishlal82 — Tue, 29 Sep 2020 10:40:36 GMT

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

Re: Calculate difference between 2 timestamp to find number of week?

ashishlal82 — Tue, 29 Sep 2020 10:40:39 GMT

(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

Re: Calculate difference between 2 timestamp to find number of week?

somesoni2 — Tue, 23 Aug 2016 19:53:26 GMT

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)

Re: Calculate difference between 2 timestamp to find number of week?

ashishlal82 — Tue, 23 Aug 2016 20:08:34 GMT

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

Re: Calculate difference between 2 timestamp to find number of week?

dwaddle — Tue, 23 Aug 2016 20:18:56 GMT

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

Re: Calculate difference between 2 timestamp to find number of week?

somesoni2 — Tue, 23 Aug 2016 20:30:45 GMT

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

Re: Calculate difference between 2 timestamp to find number of week?

ashishlal82 — Tue, 23 Aug 2016 20:37:01 GMT

Got it thanks

Re: Calculate difference between 2 timestamp to find number of week?

ashishlal82 — Tue, 23 Aug 2016 21:07:40 GMT

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.

Re: Calculate difference between 2 timestamp to find number of week?

somesoni2 — Tue, 23 Aug 2016 21:14:37 GMT

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

Re: Calculate difference between 2 timestamp to find number of week?

ashishlal82 — Tue, 29 Sep 2020 10:51:46 GMT

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