Archive
Highlighted

How can I produce a report showing the host, oldest data, and newest data?

Explorer

How can I produce a report showing the host, oldest data, and newest data? I want to expand on what the metadata search can produce. I'd like to narrow down the hosts to a particular sourcetype.

0 Karma
Highlighted

Re: How can I produce a report showing the host, oldest data, and newest data?

Splunk Employee
Splunk Employee

Depending on your goals, the metadata command may work

| metadata type=hosts | convert ctime(firstTime) ctime(lastTime) | rename firstTime as oldestData lastTime as newestData | table host *Data

Edited to add: I missed the second part of your question. You may want to additionally leverage the metasearch command: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Metasearch

Off the top of my head, you could join result sets from both metadata and metasearch commands. An example showing first and last timestamp of events from each host having syslog data would look like this:
| metasearch sourcetype=syslog | dedup sourcetype host | join host [| metadata type=hosts] | convert ctime(firstTime) ctime(lastTime) | rename firstTime as oldestData lastTime as newestData | table sourcetype host *Data

0 Karma
Highlighted

Re: How can I produce a report showing the host, oldest data, and newest data?

Explorer

Is there a way to narrow the results of this search to a particular sourcetype?

0 Karma
Highlighted

Re: How can I produce a report showing the host, oldest data, and newest data?

SplunkTrust
SplunkTrust

What about this?

| tstats min(_time) as oldestData, max(_time) as newestData where index=YOURINDEXNAMEHERE, sourcetype=YOURSOURCETYPEHERE groupby host
| fieldformat oldestData = strftime(oldestData, "%Y-%m-%d %H:%M:%S")
| fieldformat newestData = strftime(newestData, "%Y-%m-%d %H:%M:%S")