Alert on stale access keys that have not been rotated

[OiskyPoisky]

Afternoon Team,

Was hoping to get some assistance with building an alert in Splunk Enterprise Security that can pick up on old aws keys that were created in the past at a certain point - potentially 6 months for example. The below is a preliminary alert that im building but could use some guidance. 

We are ingesting events from AWS cloudtrail.

index="aws-cloud-trail" responseElements.accessKey.accessKeyId=AKIA*
| spath eventName
| search eventName=CreateAccessKey userIdentity.type!=AssumedRole
| rename responseElements.accessKey.createDate as creationdate
| eval creationdate=strptime(creationdate, "%H:%M.%S %p, %a %m/%d/%Y")
| where creationdate < (now() - (86400 * 30))
| table sourceIPAddress userName src_user userIdentity.type userAgent action status creationdate
responseElements.accessKey.status responseElements.accessKey.accessKeyId

Has anyone else come across something similar? 

Be great to hear from you! Happy to lend more context if needed.

[OiskyPoisky]

Hey Rich,

Sorry for the delay in responding. Confirming that really helped with being more specific before the first pipe.

Much Appreciated 🙂 

[richgalloway]

What seems to be the problem with that query?

[OiskyPoisky]

Hey Rich,

I can run this query at max for the past 7 days or so before the search times (using the standard time picker in search) out as its quite intensive on the search head. In my particular query here I know its looking at past 30 days but as the search can only target 7 days worth of information before timing out, Im missing the whole picture? 

Just wondering if there is a way to make it more efficient by using a tstats based search? although feel like that would need a separate datamodel to pick up on the search fields?

Thanks!

Tom

[richgalloway · Accepted solution]

tstats won't help in this case because you need to return entire events rather than just statistics.

Try being more specific before the first pipe.

index="aws-cloud-trail" responseElements.accessKey.accessKeyId=AKIA* sourcetype=foo "eventName"
| spath eventName
...