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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...