Filter data and count them and to graph with, keeping other data

, ,
1

Hello,

I’m trying to count how many the same data on the json table. Json table send via telegraf(source) and on Influxdb(destination).
For example, my josn table consider this table:

{
“items” : [
{
“ID” : “10.1003”, “host” : “Storage”, “AlarmLevel” : “Major”, “iSerialNo” : 806868,“dtOccurTime” : “1661144989915”,
},
{
“ID” : “10.1003”, “host” : “Storage”, “AlarmLevel” : “Major”, “iSerialNo” : 806896, “dtOccurTime” : “1661756400000”,
},
{
“ID” : “10.1003”, “host” : “Storage”, “AlarmLevel” : “Minor”, “iSerialNo” : 806842, “dtOccurTime” : “1661729238683”,
},
{
“ID” : “10.1002”, “host” : “Network”, “AlarmLevel” : “Major”, “iSerialNo” : 806872, “dtOccurTime” : “1661720934451”,
},
{
“ID” : “10.1003”, “host” : “Storage”, “AlarmLevel” : “Major”, “iSerialNo” : 806875, “dtOccurTime” : “1661241960000”
}
],
}
I’d like to be able to query this table and count how many all of the data where

host == Storage and AlarmLevel == Major

Can I put on the graph with time(x-axis) and count(y-axis):
now: 3 (Storage:Major)

2

My code is following:
from(bucket: “influxdb_bucket”)
|> range(start: -10d)
|> filter(fn: (r) => r[“ID”] == “10.1003”)
|> filter(fn: (r) => r[“_field”] == “AlarmLevel” or r[“_field”] == “host”)
|> pivot(
columnKey: [“_field”],
rowKey: [“_time”],
valueColumn: “_value”)
|> filter(fn: (r) => r.AlarmLevel== “Major”)

3

Hello @B_M,
I’d try something like:

from(bucket: “influxdb_bucket”)
|> range(start: -10d)
|> filter(fn: (r) => r[“ID”] == “10.1003”)
|> filter(fn: (r) => r[“_field”] == “AlarmLevel” or r[“_field”] == “host”)
|> pivot(
columnKey: [“_field”],
rowKey: [“_time”],
valueColumn: “_value”)
|> filter(fn: (r) => r.AlarmLevel== “Major” and  r.host == "Storage")
|> group() 
|> count()

Please let me know if this is what you’re looking for! I hope you have a great weekend :slight_smile:

5

Hello. Thank you for your answer. After enter code and run command, there is error like this:


 runtime error @8:6-8:13: count: column "_value" does not exist

What advice do you have?

6

Hello @Anaisdg. Thank you for your answer. After enter code and run command, there is error like this:

runtime error @8:6-8:13: count: column “_value” does not exist

What advice do you have?

7

Hello @B_M,
Oh doi! Silly me.
Please specify the right column name (after the pivot) with

|> count(column: "host")

Does that work now?

8

Hello @Anaisdg.
Yes, There is something yes. But, there are vague numbers like this:

image
image1837×516 24.5 KB

I have modified the code a bit, more precisely I will show you my complete code:

from(bucket: "influxdb_bucket")
  |> range(start: -10d)
  |> filter(fn: (r) => r["url"] == "http://A.A.A.A/af2e9dca-9292-4a91-aed7-0eda55f19a39_alarms_raw.json")
  |> filter(fn: (r) => r["svAlarmID"] == "15.1005003")
  |> filter(fn: (r) => r["_field"] == "iAlarmLevel" or r["_field"] == "svMoc")
  |> pivot(columnKey: ["_field"], rowKey: ["_time"] , valueColumn: "_value")
  |> filter(fn: (r) => r.iAlarmLevel == "Critical" and r.svMoc == "DATASTORE")
  |> group()
  |> count(column: "svMoc")

and my json format like this:

{
   "pageno" : 0,
   "viewId" : 0,
   "itemSize" : 100,
   "total" : "614",
   "items" : [
				{
      "svAlarmID" : "15.1005003",
         "svMoc" : "DATASTORE",
         "dtClearTime" : "-",
         "dtUpdateTime" : "-",
         "iAlarmCategory" : "Original alarm",
         "iDisplay" : "Unmasked",
         "iAlarmLevel" : "Critical",
         "iSerialNo" : 809045,
         "dtOccurTime" : "1661434642059",
         "iSyncNo" : 7889359,
         "objectType" : "No",
         "svClearAlarmUserName" : "-",
         "svAlarmCause" : "Storage capacity exceeds the threshold",
         "svLocationInfo" : "-",
         "iParse" : 0,
         "urnByName" : "LUN_FOR_1",
         "dtArrivedTime" : "1661434642796",
         "svAlarmName" : "Data Store Usage Exceeds the Threshold"
      },
	  ...
	  ...
	  ...
	     ],
   "updateFlag" : 1
}

and my telegraf config file like this:

[[inputs.http]]
  urls = ["http://A.A.A.A/af2e9dca-9292-4a91-aed7-0eda55f19a39_alarms_raw.json"]
  data_format = "json"
  json_strict = true
  json_query = "items"
  tag_keys = ["svAlarmID"]
  json_string_fields = ["svMoc", "iAlarmLevel", "urnByName", "svAlarmCause", "svAlarmName"]
  json_name_key = ""
  json_time_key = ""
  json_time_format = "unix"
  json_timezone = ""

At the moment, in my case, the number of critical alarms on Datastore is 7. But, on the graph, other numbers(see the first picture).

9

It would be great if it was possible to see the time on the x-axis and the number of alarms on the y-axis.
My goal with this job is to notify me when a new alarm appears in the JSON file. So I want to output the number of alarms. If there is an easier way to achieve this goal, please advise me. Thanks

10

Hello @B_M,
Unfortunately you can’t visualize more than one column. However you can visualize the count by specifying the column you want to visualize in the customize option:
Before customizing:

Screen Shot 2022-09-08 at 12.12.35 PM
Screen Shot 2022-09-08 at 12.12.35 PM2664×1182 252 KB

After customizing:
Screen Shot 2022-09-08 at 12.14.17 PM
Screen Shot 2022-09-08 at 12.14.17 PM2672×1218 295 KB

Notice how i did the count function in the aggregate window function to give me a pretty line graph. If you’re only returning one count I’d suggest changing the visualization type to a stat instead of a line graph.

alternatively you can also rename the count column to _value with:

  |> rename(columns: {svMoc: "_value")