How to create a time series graph from openSearch data

Grafana Dashboards
1

Hello Everyone,
I am using Grafana Cloud. I am trying to create a user activity tracker dashboard based on the log data I have in open search. I have 3 open-search datasources for Zscaler, Okta and Office365. I am trying to create a time-series graph where I need to plot the users and their activities over the time.

I am able to create individual graphs with the data from individual datasources seperately but my challenge is to combine the user-actions from all 3 data sources into a single time-series graph.

2

Welcome @bipin2 to the Grafana forum.

Which datasource(s) are you using to get the data? Can you share a sample set of data from Zscaler, Okta, and Office365 showing their data as queried by Grafana? Are you making a Rest API call and getting back JSON data?

3

Thanks @grant2 for the warm welcome!
I am using openSearch datasource connection to pull the data from 3 indices. Below are the sample records queried by Grafana from each sources(**replaced results with dummy values):

  • Zscaler:

{
“took”: 37,
“timed_out”: false,
“_shards”: {
“total”: 5,
“successful”: 5,
“skipped”: 0,
“failed”: 0
},
“hits”: {
“total”: {
“value”: 552,
“relation”: “eq”
},
“max_score”: 1.0,
“hits”: [
{
“_index”: “zscaler-audit-logs”,
“_id”: “jzmCn40BcqrWH8Bte_LT”,
“_score”: 1.0,
“_source”: {
“timestamp”: “2024-02-12T22:49:04.867732”,
“user”: “user_13”,
“user_agent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36”,
“action”: “allowed”,
“url”: “www.example6.com”,
“category”: “News”,
“policy”: “policy_1”,
“bytes”: 703,
“client_ip”: “192.168.1.40”,
“response_code”: 404
}
}

  • Okta:

{
“took”: 4,
“timed_out”: false,
“_shards”: {
“total”: 5,
“successful”: 5,
“skipped”: 0,
“failed”: 0
},
“hits”: {
“total”: {
“value”: 440,
“relation”: “eq”
},
“max_score”: 1.0,
“hits”: [
{
“_index”: “okta-audit-logs”,
“_id”: “9jmIn40BcqrWH8BtvfJN”,
“_score”: 1.0,
“_source”: {
“timestamp”: “2024-02-12T22:55:53.295280”,
“eventType”: “user.authentication.success”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36”,
“userName”: “user3”,
“ipAddress”: “192.168.1.1”
}
}

  • Office365:

{
“took”: 5,
“timed_out”: false,
“_shards”: {
“total”: 5,
“successful”: 5,
“skipped”: 0,
“failed”: 0
},
“hits”: {
“total”: {
“value”: 440,
“relation”: “eq”
},
“max_score”: 1.0,
“hits”: [
{
“_index”: “office365-audit-logs”,
“_id”: “GzmOn40BcqrWH8BtefOr”,
“_score”: 1.0,
“_source”: {
“timestamp”: “2024-02-12T23:02:10.460297”,
“service”: “SharePointAccess”,
“operation”: “SharePointAccess Operation 83”,
“userAgent”: “Mozilla/5.0 (Windows NT 6.1; WOW64; rv:54.0) Gecko/20100101 Firefox/54.0”,
“userName”: “user1”,
“ipAddress”: “192.168.1.1”
}
}

4

Can you repost your JSON data using the Preformatted Text (</>) formatting?

For example:

{
  "took": 37,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  }
}
5

Sure. Using community for the first time. Still exploring. :slight_smile:

  • Office365:
{
    "took": 5,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 440,
            "relation": "eq"
        },
        "max_score": 1.0,
        "hits": [
            {
                "_index": "office365-audit-logs",
                "_id": "GzmOn40BcqrWH8BtefOr",
                "_score": 1.0,
                "_source": {
                    "timestamp": "2024-02-12T23:02:10.460297",
                    "service": "SharePointAccess",
                    "operation": "SharePointAccess Operation 83",
                    "userAgent": "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:54.0) Gecko/20100101 Firefox/54.0",
                    "userName": "user1",
                    "ipAddress": "192.168.1.1"
                }
            }
  • Okta:
{
    "took": 27,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 640,
            "relation": "eq"
        },
        "max_score": 1.0,
        "hits": [
            {
                "_index": "okta-audit-logs",
                "_id": "9jmIn40BcqrWH8BtvfJN",
                "_score": 1.0,
                "_source": {
                    "timestamp": "2024-02-12T22:55:53.295280",
                    "eventType": "user.authentication.success",
                    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36",
                    "userName": "user3",
                    "ipAddress": "192.168.1.1"
                }
            },
  • ZScaler:
{
    "took": 1,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 802,
            "relation": "eq"
        },
        "max_score": 1.0,
        "hits": [
            {
                "_index": "zscaler-audit-logs",
                "_id": "jzmCn40BcqrWH8Bte_LT",
                "_score": 1.0,
                "_source": {
                    "timestamp": "2024-02-12T22:49:04.867732",
                    "user": "user_13",
                    "user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36",
                    "action": "allowed",
                    "url": "www.example6.com",
                    "category": "News",
                    "policy": "policy_1",
                    "bytes": 703,
                    "client_ip": "192.168.1.40",
                    "response_code": 404
                }
            },
6

Hi @bipin2

OK, so the first thing I noticed was that the JSON data you provided had some missing brackets or braces. For example, the Okta JSON should be this:

{
    "took": 27,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
                },
    "hits": {
        "total": {
            "value": 640,
            "relation": "eq"
             },
        "max_score": 1.0,
        "hits": [
            {
                "_index": "okta-audit-logs",
                "_id": "9jmIn40BcqrWH8BtvfJN",
                "_score": 1.0,
                "_source": {
                    "timestamp": "2024-02-12T22:55:53.295280",
                    "eventType": "user.authentication.success",
                    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36",
                    "userName": "user3",
                    "ipAddress": "192.168.1.1"
                }
            }
        ]
}
}

Also, I have no experience with OpenSearch Datasource (never heard of it, but here it is for others in case they want to look into it). My original thought was that using the Infinity datasource would be the best approach, so that’s what I tried…

Below, I pasted in your Okta JSON and assumed (based on your original post) that you want to plot the users & activities over time, so in the table returned from your JSON (above), you can see eventType, timestamp and userName.

image
image1383Ă—780 66.1 KB

However, I am puzzled as to how you would produce a time series graph with only this JSON data, which represents a moment in time. You would need a time series database or something that contains eventTypes, userNames, etc. from other timestamps, correct?

1 Like
7

@grant2 Thanks for the suggestions! I have done some customisation and able to find a solution. I made custom index in openSearch by taking timestamp, activity, log source, username and used that as a datasource. Added filters by username to track them across different log sources.

image
image2604Ă—810 203 KB

1 Like