How do you calculate a percentage from a lookup table?

[jhampton3rd]

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.

[jhampton3rd]

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

[Anam]

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

[msivill_splunk]

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.

[jhampton3rd]

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

[renjith_nair]

@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)

[jhampton3rd]

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

[renjith_nair]

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