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.
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
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
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.
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
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.
I do not think that we will get to the bottom of this until you post a minimal subset of the actual events.
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".
"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).
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?