I have one subset of applications sending metric LastSentId with tags Environment, EventType, EventSubType
Example query:
groupByTags(seriesByTag('Environment=Prod', 'name=LastSentId'), 'EventType', 'EventSubType', 'max')
Example data:
00:00:00 | Type1 | SubType1 | 2
00:00:00 | Type2 | SubType3 | 4
00:00:01 | Type1 | SubType1 | 3
00:00:01 | Type2 | SubType3 | 5
00:00:01 | Type3 | SubType4 | 1
Another subset of applications sending metric LastReceivedId with tags Environment, EventType, EventSubType, 'Application'
Example query:
groupByTags(seriesByTag('Environment=Prod', 'name=LastReceivedId'), 'Application', 'EventType', 'EventSubType', 'max')
Example data:
00:00:00 | App1 | Type1 | SubType1 | 1
00:00:00 | App2 | Type1 | SubType1 | 1
00:00:00 | App1 | Type2 | SubType3 | 2
00:00:00 | App2 | Type2 | SubType3 | 3
00:00:01 | App1 | Type1 | SubType1 | 2
00:00:01 | App2 | Type1 | SubType1 | 1
00:00:01 | App1 | Type2 | SubType3 | 4
00:00:01 | App2 | Type2 | SubType3 | 4
I want to plot difference for every row in second dataset with correspoding value from first dataset. Eg
00:00:00 | App1 | Type1 | SubType1 | 1 - 00:00:00 | Type1 | SubType1 | 2 = -1
with result like
00:00:00 | App1 | Type1 | SubType1 | -1
00:00:00 | App2 | Type1 | SubType1 | -1
00:00:00 | App1 | Type2 | SubType3 | -2
00:00:00 | App2 | Type2 | SubType3 | -1
00:00:01 | App1 | Type1 | SubType1 | -1
00:00:01 | App2 | Type1 | SubType1 | -2
00:00:01 | App1 | Type2 | SubType3 | -1
00:00:01 | App2 | Type2 | SubType3 | -1
If there is no data for type/subtype in dataset2 - i don't need them in final data. I can write such query in sql:
SELECT
a.minute,
a."Application",
a."EventType",
a."EventSubType",
(a.id - b.id) as diff
FROM
(
SELECT
date_part('minute', date) as minute,
"Application",
"EventType",
"EventSubType",
max("LastReceivedId") as id
FROM
"ReceivedData"
GROUP BY
date_part('minute', date),
"Application",
"EventType",
"EventSubType"
) a
left join (
SELECT
date_part('minute', date) as minute,
"EventType",
"EventSubType",
max("LastSentId") as id
FROM
"SentData"
GROUP BY
date_part('minute', date),
"EventType",
"EventSubType"
) b ON a.minute = b.minute
and a."EventType" = b."EventType"
and a."EventSubType" = b."EventSubType";
but don't understand how to do it in graphite. Or is it even possible. I tried to play with groupByNodes but can't get result i wanted.
I know i can do some math with Grafana expressions, but i need it to be pure graphite query for using it in monitoring software.