How to restrict a search string to show only one value per day?

[mhornste]

Hi,

I have a SQL query running in Splunk counting the number of documents by data size (below 1MB, 1-5MB, 5-10MB, 10-50MB, 50-100MB, 100-500MB, 500-1000 MB, over 1000MB). I have already extracted the correct values and specified fields, so I have the data in my index:

OtcsDocsbelow1,OtcsDocs1to5,OtcsDocs5to10,OtcsDocs10to50,OtcsDocs50to100,OtcsDocs100to500,OtcsDocs500to1000,OtcsDocsOver1000

I would now like to display a table with the results. I have scheduled the SQL query to be running once in the night. If I use the following search string, I don't get a table with one value per day, but with multiple time stamps:

index=otcs sourcetype=Otcsdocuments* | table _time  OtcsDocsbelow1,OtcsDocs1to5,OtcsDocs5to10,OtcsDocs10to50,OtcsDocs50to100,OtcsDocs100to500,OtcsDocs500to1000,OtcsDocsOver1000

I'd like to have a table showing numbers for each value ("Documents below 1 MB:" " and would also like to display a pie chart of this document mix.

Can anyone help? I aldready tried a lot and also searched for questions/ answers, but couldn't find anything matching.

Thanks

Martin

[mhornste · Accepted solution]

Hi,

sorry for the late reply. I managed to get everything in one table always showing the latest count. This is the search string in case someone can use it.

index=otcs sourcetype="Otcsdocumentsbelow1" | eval category="Documents below 1 MB" | stats latest(OtcsDocsbelow1) as count by category | append [search index=otcs sourcetype="otcsdocuments1to5" |  eval category="Documents between 1 and 5 MB" |stats latest(OtcsDocs1to5) as count by category] | append [search index=otcs sourcetype="otcsdocuments5to10" |  eval category="Documents between 5 and 10 MB" |stats latest(OtcsDocs5to10) as count by category] | append [search index=otcs sourcetype="otcsdocuments10to50" |  eval category="Documents between 10 and 50 MB" |stats latest(OtcsDocs10to50) as count by category] | append [search index=otcs sourcetype="otcsdocuments50to100" |  eval category="Documents between 50 and 100 MB" |stats latest(OtcsDocs50to100) as count by category] | append [search index=otcs sourcetype="otcsdocuments100to500" |  eval category="Documents between 100 and 500 MB" |stats latest(OtcsDocs100to500) as count by category] | append [search index=otcs sourcetype="otcsdocuments500to1000" |  eval category="Documents between 500 and 1000 MB" |stats latest(OtcsDocs500to1000) as count by category]  | append [search index=otcs sourcetype="otcsdocumentsOver1000" |  eval category="Documents Over 1000 MB" | stats latest(OtcsDocsOver1000) as count by category]

I had different sourcetypes. Therefore - as far as I understand - I had to append different searches.

With this search string, I'm able to create a pie chart which shows me the data mix.

Regards

[ryanoconnor]

Have you tried limiting your search string to just one day? If the schedule for your SQL query is only one day, that should do the trick.

Can you show us what a raw event looks like?