Splunk Search

Extract time range from previous searches and graph results

romantercero
Path Finder

Hi,

I'm trying to find out how frequently the data on splunk is accessed vs its age. For this I though I could craft a search that searches trough the audit index and maybe retrieves the time range of the searches the users have made so far. The problem is that I'm not finding that level of detail on the audit index. Any thoughts on where I can get this information from? This issue does not merit using the API.

Thanks!

Tags (4)
0 Karma
1 Solution

sideview
SplunkTrust
SplunkTrust

I took a look, and the audit log actually does log the times, although strangely it logs it in a somewhat arbitrary string format, and it wraps it in single quote chars.

apiStartTime='Wed May 2 20:33:00 2012', apiEndTime='Wed May 2 20:48:44 2012'

This can be worked with, but it requires some care. In the eval statements below I convert these two fields to epochTime values, ie seconds since 01/01/1970. And then between those two times and the time of the actual audit event, you should be able to get whatever you need.

index=_audit apiStartTime apiEndTime | eval searchStartTime=strptime(apiStartTime, "'%a %B %d %H:%M:%S %Y'") | eval searchEndTime=strptime(apiEndTime, "'%a %B %d %H:%M:%S %Y'") | rename _time as searchExecuteTime | table search searchExecuteTime searchStartTime searchEndTime

If you want to calculate deltas between these, it's just a little more eval, included here for convenience:

index=_audit apiStartTime apiEndTime | eval searchStartTime=strptime(apiStartTime, "'%a %B %d %H:%M:%S %Y'") | eval searchEndTime=strptime(apiEndTime, "'%a %B %d %H:%M:%S %Y'") | rename _time as searchExecuteTime | table searchExecuteTime searchStartTime searchEndTime | eval deltaFromStart=searchExecuteTime-searchStartTime | eval deltaFromEnd=searchExecuteTime-searchEndTime | eval searchTimeSpan=searchEndTime-searchStartTime

So, if you want to pipe this into timechart, you'll need to keep one of the times called "_time".

Here's an example search, and it's hard to describe, but it shows a timechart, where the yaxis is actually how many seconds back in time the users were running searches at that time, as an avg, min and max. Say that ten times fast.

index=_audit apiStartTime apiEndTime | eval searchStartTime=strptime(apiStartTime, "'%a %B %d %H:%M:%S %Y'") | eval searchEndTime=strptime(apiEndTime, "'%a %B %d %H:%M:%S %Y'") | eval searchExecuteTime=_time | eval deltaFromEnd=searchExecuteTime - searchStartTime | timechart max(deltaFromEnd) min(deltaFromEnd) avg(deltaFromEnd)

View solution in original post

sideview
SplunkTrust
SplunkTrust

I took a look, and the audit log actually does log the times, although strangely it logs it in a somewhat arbitrary string format, and it wraps it in single quote chars.

apiStartTime='Wed May 2 20:33:00 2012', apiEndTime='Wed May 2 20:48:44 2012'

This can be worked with, but it requires some care. In the eval statements below I convert these two fields to epochTime values, ie seconds since 01/01/1970. And then between those two times and the time of the actual audit event, you should be able to get whatever you need.

index=_audit apiStartTime apiEndTime | eval searchStartTime=strptime(apiStartTime, "'%a %B %d %H:%M:%S %Y'") | eval searchEndTime=strptime(apiEndTime, "'%a %B %d %H:%M:%S %Y'") | rename _time as searchExecuteTime | table search searchExecuteTime searchStartTime searchEndTime

If you want to calculate deltas between these, it's just a little more eval, included here for convenience:

index=_audit apiStartTime apiEndTime | eval searchStartTime=strptime(apiStartTime, "'%a %B %d %H:%M:%S %Y'") | eval searchEndTime=strptime(apiEndTime, "'%a %B %d %H:%M:%S %Y'") | rename _time as searchExecuteTime | table searchExecuteTime searchStartTime searchEndTime | eval deltaFromStart=searchExecuteTime-searchStartTime | eval deltaFromEnd=searchExecuteTime-searchEndTime | eval searchTimeSpan=searchEndTime-searchStartTime

So, if you want to pipe this into timechart, you'll need to keep one of the times called "_time".

Here's an example search, and it's hard to describe, but it shows a timechart, where the yaxis is actually how many seconds back in time the users were running searches at that time, as an avg, min and max. Say that ten times fast.

index=_audit apiStartTime apiEndTime | eval searchStartTime=strptime(apiStartTime, "'%a %B %d %H:%M:%S %Y'") | eval searchEndTime=strptime(apiEndTime, "'%a %B %d %H:%M:%S %Y'") | eval searchExecuteTime=_time | eval deltaFromEnd=searchExecuteTime - searchStartTime | timechart max(deltaFromEnd) min(deltaFromEnd) avg(deltaFromEnd)

sideview
SplunkTrust
SplunkTrust

Just noticed I had some extra "_audit" searchterms in there that were going to make your search only match the searches that were searching for _audit. Sorry about that. I went ahead and removed them from my answer but you should make sure you didn't copy and paste them along..

0 Karma

romantercero
Path Finder

Thanks!! 😄

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...