I want to see how many times a user has accessed a database in a given time period. I used
sourcetype= h1 | stats count by database, user | sort -count|stats list(count), values(database) by user
However, after running a search for specific users, the numbers that come up from the above line, do not match up with the number of search results I find by searching for specific users. Is there a reason for the discrepancy ?
A) I don't see anything obvious - you probably (?) don't have over 10K users / databases combinations, but there is a default maximum number of records returned from sort
, so you should get in the habit of using sort 0
instead of plainsort
.
B) Also, when trying to compare two versions, always snap to the beginning and end of a time period, so you have a fixed set of results between the two to compare on.
C) Always rename count
, because occasionally it will bite you when a later stats
-type command or timechart
gets confused.
Try this and compare to see if it fixes your issue....
earliest=-1d@d latest=@d sourcetype= h1
| stats count as countbydb by database, user
| sort 0 - countbydb
| stats sum(countbydb) as theTotalCount, list(countbydb) as theCounts, values(database) as theDBs by user
| where user=="myselecteduser"
earliest=-1d@d latest=@d sourcetype= h1 user="myselecteduser"
| stats count as theTotalCount, values(database) as theDBs by user
If not, then consider something like this
earliest=-1d@d latest=@d sourcetype= h1
| stats count as countbydb by database, user
| eval DBandCount = database." - ".countbydb
| stats sum(countbydb) as theTotalCount, values(DBandCount) as theDBs by user
A) I don't see anything obvious - you probably (?) don't have over 10K users / databases combinations, but there is a default maximum number of records returned from sort
, so you should get in the habit of using sort 0
instead of plainsort
.
B) Also, when trying to compare two versions, always snap to the beginning and end of a time period, so you have a fixed set of results between the two to compare on.
C) Always rename count
, because occasionally it will bite you when a later stats
-type command or timechart
gets confused.
Try this and compare to see if it fixes your issue....
earliest=-1d@d latest=@d sourcetype= h1
| stats count as countbydb by database, user
| sort 0 - countbydb
| stats sum(countbydb) as theTotalCount, list(countbydb) as theCounts, values(database) as theDBs by user
| where user=="myselecteduser"
earliest=-1d@d latest=@d sourcetype= h1 user="myselecteduser"
| stats count as theTotalCount, values(database) as theDBs by user
If not, then consider something like this
earliest=-1d@d latest=@d sourcetype= h1
| stats count as countbydb by database, user
| eval DBandCount = database." - ".countbydb
| stats sum(countbydb) as theTotalCount, values(DBandCount) as theDBs by user
I believe the
sort -count
part is wrong. It sorts the number of databases, but does not move the name along with it. The names stay in the same order. How would I go about fixing that?
Instead of values(database)
use list(database)
to retain the record order.
That works! Thanks!
Use the by clause with sort.
sourcetype="h1" | stats count as countbydb by database, user |sort by countbydb |stats list(countbydb), values(database) by user
is what I am using. It does not affect the user column. I tried placing the sort by countbydb at the end, but that does not work either.
Please give an example of the other search (with the username obfuscated)
sourcetype="h1" user = "user1" database = bdp
In the search, bdp appears 10 times. But in the table, it only appears once.
In the table, it should appear only once, with a count of 10.