Calculate percentage b/n 2 counted numbers

[swimena]

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:

1. Select entries with every 4 and 5 for the last month
2. calculate the days difference between 2 days to get the overdue days
3. count only items that are 30+ days overdue
4. count the total amount of items
5. calculate the percentage
6. print it in a table and get the % for every month for comparison
7. 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

[devastephen]

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.

[DMohn · Accepted solution]

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

[swimena]

Hi @DMohn,

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

Thank you very much for your help!

Cheers!

[swimena]

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.

[DMohn]

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.

[swimena]

Thank you very much. That's awesome!

[chrisyounger]

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?

[swimena]

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