Not able to get all results to show stats when all field and value events are present with search. What am i missing?

[bab4684]

Using the tutorialdata.zip tutorial dataset but cant seem to get the results I want using
index=main ("categoryId=*" OR "AcctID=*" OR status=400) | stats count(AcctID) count(status) as 400 by categoryId

looks like it should be simple, but not getting counts to show for AcctID to.

Trying to get the the number of AcctID(s) that have status=400 broken down by categoryId. Then eventually chart it.

[DalJeanis · Accepted solution]

First, do this to get the proper capitalization of AcctID and categoryID:

index=main status=400 | head 1 | untable status fieldname fieldvalue
| where like(fieldname,"categoryid") OR like(fieldname,"acctid")

Then change this code to match that capitalization. This will get the count of total events and distinct count of acctIDs by categoryID

 index=main CategoryID=* AcctID=* status=400 
| stats dc(AcctID) as AcctsWith400, count as EventsWith400  by CategoryID

You can also do this to see it over time...

 index=main CategoryID=* AcctID=* status=400 
| timechart span=1d dc(AcctID) as AcctsWith400, count as EventsWith400  by CategoryID

[woodcock]

Try this:

index=main ("categoryId=*" OR "AcctID=*" OR status=400) | fillnull value="N/A" categoryId | stats count(AcctID) count(status) as 400 by categoryId

Thel problem is surely that most (maybe even all) of your events do not have a field named categoryId. Are you sure that you spelled and capitalized it correctly (case matters)? Turn on verbose mode and check the raw events.

[bab4684]

Guess i don't have enough Karma to post SS yet. But here is the fieldsummary.

field count distinct_count values
status 27673 9 [{"value":"200","count":24013},{"value":"503","count":680},{"value":"408","count":528},{"value":"406","count":493},{"value":"404","count":491},{"value":"500","count":490},{"value":"400","count":480},{"value":"505","count":346},{"value":"403","count":152}]

categoryId 12013 8 [{"value":"STRATEGY","count":3237},{"value":"ARCADE","count":1894},{"value":"ACCESSORIES","count":1483},{"value":"NULL","count":1431},{"value":"TEE","count":1426},{"value":"SIMULATION","count":1028},{"value":"SHOOTER","count":980},{"value":"SPORTS","count":534}]

AcctID 21313 500 [{"value":"1010558415713236","count":1},{"value":"1011101960657692","count":1},{"value":"1012823059693579","count":1}, ------plus a bunch more----

In short, trying to find out count of status=400 by catergoryId. Was trying to use AcctID to tie events together but not getting results expected

[bab4684]

I reconfirmed spelling (i usually copy/paste from clicking filed and grabbing the header), verbose on as well.

This was the results

categoryId count(AcctID) 400
ACCESSORIES 0 1483
ARCADE 0 1894
N/A 21313 277
NULL 0 1431
SHOOTER 0 980
SIMULATION 0 1028
SPORTS 0 534
STRATEGY 0 3237
TEE 0 1426

Any other ideas? I'll post screenshot of fieldsummary below.

[woodcock]

I do not think that we will get to the bottom of this until you post a minimal subset of the actual events.

[richgalloway]

The as clause of the stats command requires a valid field name, which "400" is not. Changing "400" to something like "Status" should help a lot.

Also, if you only want to see events with status=400 then you should use "AND status=400" rather than "OR status=400".

[woodcock]

"400" would not be valid as an index-time field name but the rules are looser for search-time and should be valid (although certainly it lacks clarity).

[bab4684]

Made this change { index=main ("categoryId=" OR "AcctID=" AND status=400) | stats count(AcctID) count(status) as status by categoryId }

Now the results show tabled like
categoryId count(AcctID) status
NULL 0 224

What else could be wrong that the catergoryId are showing as NULL and AcctID =0?