Querying Access logs when access to these has been more than twice

[colinmchugo]

Morning Guru's,

I am looking for a script to show the number of days a log was last accessed. Ive got this working but the problem is its common for people to dump in a log that then is never accessed again and then this has to be whitelisted.

So i am wondering if anyone knows how to only show a log/file that has been accessed more then twice in the last month please?

thanks

Colin

[FrankVl]

Based on our discussion in comments, I'd change your original query to filter for sources that had their last event on a different day then their first event (second and third line are the ones I added, I left the rest of your query as is):

| metadata index=m type=sources 
| eval firstToLast=round((lastTime-firstTime)/86400)
| where firstToLast>1
| eval ageInDays = round((now()-lastTime)/86400) 
| where ageInDays > 0.99 AND ageInDays<170.00 
| convert ctime(lastTime) 
| convert ctime(firstTime) 
| convert timeformat="%Y %D" mktime(ageInDays) 
| rename ageInDays as Days 
| sort by Days

[FrankVl]

What exactly do you mean by a log in this case? Can you perhaps share the query that you had so far?

[colinmchugo]

| metadata index=m type=sources
| eval ageInDays = round((now()-lastTime)/86400)
| where ageInDays > 0.99 AND ageInDays<170.00
| convert ctime(lastTime)
| convert ctime(firstTime)
| convert timeformat="%Y %D" mktime(ageInDays)
| rename ageInDays as Days
| sort by Days

thanks alot

[FrankVl]

So that gives you a list of sources in that index, which have had events ingested in the past 1-170 days. I assume source in this case relates to what you called "log/file" in your question?

What exactly do you mean with "accessed"? Because that metadata search will give you when latest events were received from a certain source (log/file). "Access" sounds like you want to know when someone last looked at it?

Also, what exactly is your goal? In what sense do you want to whitelist certain sources?

[colinmchugo]

Sorry Frank,

Yes i want to know when a file is uploaded too like a log. I am using a panel to show me if any logs have stopped logging for some reason.

So want a query to find out if for example a file/log logs once but then never logs again thats fine. i want to whitelist those. So some sort of counter i suppose. If a file doesnt log and it had been logging then show the amount of days it hasnt logged.

[FrankVl]

Right, so a file that was ingested once, but not again afterwards can be ignored, you want to list sources that have been ingesting for a while, but then suddenly stopped receiving new logs?

Perhaps you could look at comparing last time and first time? If both are on the same day: ignore, otherwise include the item in the results and show the days since lasttime (as you already do)?

[colinmchugo]

Yes that sounds good, if it logs on the same day and doesn't log again then we don't care. If it logs everyday for 7 days then suddenly stops count the amount of days it has stopped from.

Can you assist with the query? thanks.