What is wrong with my tstats command?

[wcooper003]

This search works fine but is slow:

host=host1 sourcetype="WinEventLog:Security" EventCode=5156  | timechart span=1d count 

So i'm attempting to convert it to tstats to see if it'll give me a little performance boost, but I don't know the secrets to get tstats to run. Here's what i've tried based off of Example 4 in the tstats search reference documentation (along with a multitude of other configurations):

| tstats prestats=t count  FROM index=wineventsec_us WHERE host=host1 AND sourcetype="WinEventLog:Security" AND EventCode=5156 BY _time span=1d | timechart span=1d count

Every time i tried a different configuration of the tstats command it has returned 0 events. Any thoughts would be appreciated.

[javiergn · Accepted solution]

You can't filter by EventCode unless it is indexed.

Try just:

 | tstats prestats=t count  FROM index=wineventsec_us WHERE host=host1 AND sourcetype="WinEventLog:Security" BY _time span=1d | timechart span=1d count

And if that works without filtering by EventCode, then this is your issue.

Alternatively you could try the first query but using Event Sampling. It won't be 100% accurate but at least it'll be much faster and give you a good idea. For instance, with a 1:10 sampling you could do:

host=host1 sourcetype="WinEventLog:Security" EventCode=5156  
| timechart span=1d count 
| eval count = 10 * count

With a 1:100 you would multiply by 100 and so on.

Hope that helps.

[mommyfixit]

Would you please explain what you mean by "You can't filter by EventCode unless it is indexed." Our Windows event codes are whitelisted in inputs.conf with oswin listed as the index, and we have oswin configured in indexes.conf.

I can see results if I just run index=oswin EventCode=8001.

But it tells me I have 0 results if I run:

| tstats values(host) where index=oswin AND EventCode=8001. 

[PickleRick]

There are two kinds of fields in splunk.

There are the "usual" fields which are extracted in search time which means that splunk extracts them from raw events on the fly as it's comparing the events to your given conditions (oversimplifying slightly the process). Since they are extracted during search time, you can modify the extraction definitions and get various fields from the same raw events, you can redefine the extractions and reapply the, to the same raw data and so on.

Then there is the second one - the indexed fields. Those fields are created only once during the initial ingestion of events. So once the event is indexed, all indexed fields possible for that event are created and written into separate files. The standard splunk's metadata fields - host, source and sourcetype are indexed fields.

Both types of fields have their pros and cons and you usually need a very good reason to create new indexed fields.

But coming back to the original issue - doing summary statistics using tstats is possible only over indexed fields since tstats doesn't touch raw events and only uses the summaries of indexed fields. It has no knowledge of search-time extracted fields. It's therefore much much faster but limited only to those indexed fields.

[wcooper003]

I can get this query working if I move the 'index=' from the FROM statement to the WHERE statement:

| tstats count where index=wineventsec_us sourcetype="WinEventLog:Security" by _time span=1d | timechart span=1d count

But it's as you say - EventCode doesn't work in the query.

[javiergn]

Yeah didn't notice that. You can use FROM to select from a datamodel for instance, but for field = value you have to do it within the WHERE clause.

[javiergn]

By the way, if you are using Enterprise Security maybe there's a datamodel you can use to search for your data in a much faster way

[wcooper003]

Thanks for the info. I tried to take out EventCode, but that didn't help. Even if I strip out the WHERE clause completely, it still doesn't run. E.g.:

| tstats prestats=t count  FROM index=wineventsec_us BY _time span=1d | timechart span=1d count

This is definitely the correct index name, why is this returning 0 events? I have tstats working for some data models, but can't figure out what's going on here.