Grafana Query question

Hello, I’m using graphite with grafana and I’ve created some metrics that store data in graphite in the form of:

ADStatus.ADDefault.USERNAMES.LockedOut (data will be a 0 if not locked out or 1 if locked out) TIMESTAMP

I have a script that queries our AD and checks each user and their locked out status.

I want to have a table that will show locked out users (if their value is 1).

I’m not sure how to build this query though…so far I have:
“aliasByNode(highestCurrent(ADStatus.ADDefault.*.LockOutStatus, 5), 2)”

But this just displays the ‘top 5’ regardless of their value…(all are 0 except 1 for testing).

How would I do a query like…

ADstatus | ADDefault | * | LockOutStatus | WHERE VALUE = 1 | aliasByNode(2)

Is this possible?

Hi,

Think these kind of things are hard to do using graphite. Also depending on your number of users you may hit a problem with high cardinality of usernames in your metrics (a limitation that many time series databases have). In your case it would be much easier to use a relational database like Postgres, MySQL or MSSQL or Influxdb (which is a time series database).

Marcus

Thanks Marcus,

I guess even if I were using SQL or another DB I’m not sure how to build the query to still show the users when locked out…

I figured it would be easy to have a table display ONLY the items where a value was 1 and not 0

Yes table and query where lockedout = 1 are probably the easiest.

Marcus

Ahh this is great! I wasnt even aware we could honestly use SQL with grafana…

When I go to >Datasources > Add a data source i dont see SQL in the drop down in type…am I missing a step on adding an SQL Db?

Nevermind I see it says only avail in 5.1+, I’m using 4.2…I’ll have to look for an easy way to upgrade

1 Like

I was able to find version 5.0.3 but I am unable to find 5.1

This linke: http://docs.grafana.org/v5.1/features/datasources/mssql/
shows that the MSSQL is only available in 5.1…where can I find this download?

Either nightly builds or building from source.

Marcus