Count unique field values using API


(Toby J) #1

Hello, we log all user requests to our site and I’m trying to figure out via the API how many unique users have performed a specific action over the past 60 seconds.

So once I have a query for the subset I need via search/universal/relative API, is there a way to get the API to then give me a count of how many unique userId values exist in that subset?

It seems like https://github.com/Graylog2/graylog2-server/issues/3649 is related but I’m not sure how to do this via API.


(Philipp Ruland) #2

Hey @irontoby,

You could call this:

https://graylog.example.com/api/search/universal/relative/terms?query=*&range=300&field=userId&order=userId:desc&size=50

query = your query
range = range in seconds
field = field that should be used by the term aggregation
order = Order of results, sorted by which field and ascending/descending
size = how many terms to report

Example response:

{
    "time": 235,
    "terms": {
        "user1": 100273,
        "user2": 4,
        "user3": 4,
        "user4": 794,
        "user5": 1,
        "ext-user1": 7,
        "baduser": 17570,
        "user1337": 123931,
        "user6": 4475,
        "ext-user2": 4,
        "ext-user3": 4
    }, <<< OUTPUT TRUNCATED for readability >>>
}

Only be aware, that even though the query was performed using your order instruction, the returned values are not in order.

Greetings,
Philipp


(Toby J) #3

Thanks for the answer Philipp, I was unaware of the “terms” search, and in fact I’m getting a 403 Unauthorized when I try to run that query (which I’m guessing is why it wasn’t in my API browser). I’ll get w/ our admin and get that fixed.

This looks very close to what I need, I don’t suppose it’s possible to then count up the various userId terms and return just a single count?


(Philipp Ruland) #4

Of course that’s possible :slight_smile:

https://graylog.example.com/api/search/universal/relative/stats?query=*&range=300&field=userId
{
  "time": 390,
  "count": 374283,
  "sum": "NaN",
  "sum_of_squares": "NaN",
  "mean": "NaN",
  "min": "NaN",
  "max": "NaN",
  "variance": "NaN",
  "std_deviation": "NaN",
  "built_query": "<TRUNCATED>",
  "cardinality": 10
}

Query the stats endpoint and use the count. The count is the sum of all userIds and the cardinality is the amount of unique userIds.

Greetings,
Philipp


(Toby J) #5

So the 403 is because I didn’t filter by stream. Any thoughts on getting a “grand total” would be appreciated! Thanks again.

Edit: replies crossed in the mail :slight_smile: trying the stats query now.


(Philipp Ruland) #6

I was a little quicker than your second response :wink: :smiley:


(Toby J) #7

Yep, tried your suggestion & it works great, thanks!!


(Philipp Ruland) #8

You’re welcome :slight_smile:

I’m happy that I was able to help :slight_smile:


(system) #9

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.