Index query question (latest event from each source type by host)

[jcrensh]

I have been having a lot of problems with our Windows 2008 R2 Domain Controllers falling behind in just the security log sections from their local universal forwards. During the day, the latest indexed security event falls behind up to about 2 hours behind the current time. This happens when the workforce shows up on a workday. After 5:30 or so in the evening, the forwarders eventually catch up. It seems to be different universal forwarders (never the same from day to day), only the domain controllers, and only during the workday (7am - 5pm). At the peak, these DC's can have 150 to 200 events per second, but I am assuming that this is still workable from a universal forward perspective. At any rate, I have a ticket open with Splunk and they are investigating the issue now.

What I think would be helpful for me is a way to look into the index that I have the domain controllers reporting to. I would like to query a index and have a table that comes back with all hosts in that index, sourcetypes for that host, the latest time entry for that sourcetype and host, and then a field that shows the latest time entry latency from the current time.

Can someone help out with the query? I can get most of what I want if I already know the host names up front, however this query may have additional value for indexes with an X amount of hosts in it for other Splunk users.

Thanks in advance.

[jdunlea_splunk]

This should give you what you are looking for in that table:

index= | stats max(_time) as last_time by host, sourcetype | eval latency_minutes=((now()-last_time)/60) | convert ctime(last_time) as last_time | fields + host, sourcetype, last_time, latency_minutes

Hope this helps!

[jcrensh]

Awesome....thank you very much for this search query. This is working great and I can now check all my indexes for similar issues.

[reswob4]

jcrensh, you should mark this as answered....