topic Re: Calculate percentage b/n 2 counted numbers in Splunk Search

Calculate percentage b/n 2 counted numbers

swimena — Thu, 14 Feb 2019 12:18:22 GMT

Hello everyone,

I'm trying to calculate the % of overdue items and print the result for every month. It looks like I'm completely stuck with the query so any help will be greatly appreciated.

Here's what I'm trying to achieve:

Select entries with every 4 and 5 for the last month
calculate the days difference between 2 days to get the overdue days
count only items that are 30+ days overdue
count the total amount of items
calculate the percentage
print it in a table and get the % for every month for comparison
possibly visualize the results in a Area Chart

...........

index=something (SEVERITY=4 OR SEVERITY=5) earliest=-4w@w latest=now 
    | eval start=FIRST_FOUND_DATETIME| eval end=LAST_FOUND_DATETIME| eval duration = round((end-start)/86400)
    | stats count, values(round) AS Overdue
    | where round>30
    | stats count as Total
    | eval percent_difference=((Overdue/Total)*100) 
    | table percent_difference

Re: Calculate percentage b/n 2 counted numbers

chrisyounger — Tue, 29 Sep 2020 23:17:02 GMT

We need more information on how to determine FIRST_FOUND_DATETIME. Are there multiple events per ticket? If so, what field name is used to determine two events are for the same ticket?

Re: Calculate percentage b/n 2 counted numbers

swimena — Fri, 15 Feb 2019 06:48:47 GMT

Hi Chris,

This is a feed from a vulnerability scanner. Each event may have several dates and the format of the log is the following (per event):

IP DNS NetBIOS Tracking Method OS IP Status Title Vuln Status Type Severity Port Protocol FQDN First Detected Last Detected Times Detected Date Last Fixed CVE ID

I'm trying to calculate the days difference b/n first and last detected for each vulnerability for each host.
Then I want to extract only those events which days difference is higher than 30 and count their number. The last step would be to compare the count of those 30+ events to the count of the total number of events and find their ratio (%). I also would like to visualize the results for every month.

Thanks

Re: Calculate percentage b/n 2 counted numbers

DMohn — Fri, 15 Feb 2019 07:24:39 GMT

Hi @swimena

Your query is on the right trach, yet you might be missing a few points here. First, please check and confirm, that your fields FIRST_FOUND_DATETIME and LAST_FOUND_DATETIME are timestamp fields (UNIX time), and not time strings - because if you want to calculate a duration, these need to be a number format.

For the example I am assuming these are strings formatted "YYYY-MM-DD HH:MM:SS" - if it is different in your case, please adjust the strptime accordingly.

 index=something (SEVERITY=4 OR SEVERITY=5) earliest=-4w@w latest=now
| eval start = strptime(FIRST_FOUND_DATETIME,"%Y-%m-%d %H:%M:%S")
| eval end = strptime(LAST_FOUND_DATETIME,"%Y-%m-%d %H:%M:%S")
| eval duration = round((end-start)/86400)
| stats count as Total, count(eval(duration>30)) as Overdue
| eval percentage = ((Overdue/Total)*100)
| table Total Overdue percentage

Re: Calculate percentage b/n 2 counted numbers

swimena — Fri, 15 Feb 2019 07:59:27 GMT

Hi @DMohn,

This works like a charm. Readjusted the time as per your advice.

Thank you very much for your help!

Cheers!

Re: Calculate percentage b/n 2 counted numbers

swimena — Fri, 15 Feb 2019 08:21:03 GMT

Just remembered to ask - do you know how I can print the calculated percentage for the last 3 months and split by month - Nov/Dec/Jan?

Thanks.

Re: Calculate percentage b/n 2 counted numbers

DMohn — Fri, 15 Feb 2019 08:57:57 GMT

You can do this with the same query - almost 🙂

  index=something (SEVERITY=4 OR SEVERITY=5) earliest=-3m@m latest=@m
 | eval start = strptime(FIRST_FOUND_DATETIME,"%Y-%m-%d %H:%M:%S")
 | eval end = strptime(LAST_FOUND_DATETIME,"%Y-%m-%d %H:%M:%S")
 | eval duration = round((end-start)/86400)
 | eval month = strftime(_time,"$y/%m")
 | stats count as Total, count(eval(duration>30)) as Overdue by month
 | eval percentage = ((Overdue/Total)*100)
 | table month percentage

month being formatted as YY/MM here, because if you were using the month name only, sorting could be off.

Re: Calculate percentage b/n 2 counted numbers

swimena — Fri, 15 Feb 2019 11:54:01 GMT

Thank you very much. That's awesome!

Re: Calculate percentage b/n 2 counted numbers

devastephen — Tue, 09 Jul 2019 07:11:59 GMT

Hi , splunk fundamental elearning retest . can I take immediately after failing the first or I need to wait for 2 to 3 days to retake
,hi I have failed in splunk fundamental e1. I took the test again. cleared the test, but scores not reflected . it included the time I spend for the retake and shows the result as fail, any time period is required taking retest.