Splunk Search

How do you calculate a percentage from a lookup table?

jhampton3rd
Explorer

I have a lookup table that is written to when a user clicks on a button to confirm that they have checked logs on a dashboard. In the lookup table, these are the fields that are available and how the values are written to the CSV file.

Time                   |          User
1521641008                  john.doe@splunk.com
1521641345                  jane.doe@splunk.com
1521641376                john.doe@splunk.com
1521641456                john.doe@splunk.com
1521727607                john.doe@splunk.com
1523969108                jane.doe@splunk.com

I want to check to verify that a user has checked logs per week over a span of time (lets say 6 months). I want to see how many times the logs were checked and give a percentage.

I've gotten to this point in my search but I'm unable to figure this out. I'm running this search over a the last 6 months

| inputcsv audit_check.csv
| rename Time as _time
| timechart span=1w count(User)
| addcoltotals

I get the following

_time                      |           count(User)
2018-03-01                                            0
2018-03-08                                            0
2018-03-15                                            4
2018-03-22                                            1
2018-03-29                                            0
2018-04-05                                            0
etc......
                                                       5

So anything over 1 is considered checked. 0 is considered not checked. I'd like to get the percentage of successful audit checks per week over the 6 month period.

Thanks for your help.

0 Karma

jhampton3rd
Explorer

I would like to thank everyone that assisted. Your queries helped me get to where I needed to be. Ultimately, I wanted to see the overall percentage over a 6 month period. Here's the final query that seems to be working.

|inputcsv audit_check.csv
| rename Time as _time
| timechart span=1w count(User) as count
| eval count=if(count>=1,"1", count)
| stats sum(count) as counttotal, count as totalcount
| eval perc=round((counttotal/totalcount)*100,2)
| fields perc

0 Karma

Anam
Community Manager
Community Manager

Hi @jhampton3rd

I am the Community Content Specialist for Splunk Answers. It is great to see that you were able to figure out a solution based on the answers that the community members provided you. Please go ahead and accept the appropriate answer from the user that was most useful to you, so other members of the community can benefit from the same solution.

Thanks

0 Karma

msivill_splunk
Splunk Employee
Splunk Employee

This will get you closer .....

| makeresults count=6 
| streamstats count 
| eval time = CASE( count==1, 1521641008 ,count==2, 1521641345 ,count==3, 1521641376,count==4, 1521641456 ,count==5, 1521727607 ,count==6, 1523969108 ) 
| eval user = CASE( count==1, "john.doe@splunk.com" ,count==2, "jane.doe@splunk.com" ,count==3, "john.doe@splunk.com",count==4, "john.doe@splunk.com",count==5, "john.doe@splunk.com" ,count==6, "jane.doe@splunk.com" ) 
| eval _time = time 
| eval end_time = relative_time( 1523969108,"+w@w") 
| eval twenty_four_weeks_before_end_time = (end_time - (6*4*7*24*60*60)) +1 
| where time >= twenty_four_weeks_before_end_time AND time <= end_time 
| timechart span=1w count
| stats count as weeks_checked 
| eval percentage_checked_in_24_weeks = (weeks_checked / 24) * 100

Everything up to the timechart command is selecting 24 weeks worth of data (snapped to the beginning of the week). This will need to be tweaked as I'm using self contained SPL without the lookup table.

The above will handle the scenario when no alerts are generated for a week.

0 Karma

jhampton3rd
Explorer

Thanks for your help. You helped me eventually come up with the correct query.

0 Karma

renjith_nair
Legend

@jhampton3rd,
Try

|inputlookup audit_check.csv
| rename Time as _time
| timechart span=1w count(User) as count
| eventstats sum(count) as total
| eval perc=round((count/total)*100,2)
---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

jhampton3rd
Explorer

Thanks for your help. You helped me eventually come up with the correct query.

renjith_nair
Legend

@jhampton3rd , glad to know. You can upvote the answers if it helped you 🙂

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...