NRQL query for new transactions

Hi ,

I would like to create a NRQL query that pulls the transactions’ names that did not exist in the last 24 Hours.
I couldn’t succeed in creating it, if someone has an idea it would help.
SELECT count(name) FROM Transaction FACET name WHERE name ,
I couldn’t find the right condition for it.

What are you trying to accomplish exactly? You want names that have or have not shown up in the last 24 hours or were or were not present more than 24 hours previous?

I want names of the transactions that weren’t shown on the last 24 hours.
Their RPM was 0 until for example 1 hour ago.

You have some complexity there that requires some context and knowledge of the application over time. It would likely take some api calls and some logic to look for deltas over time. Like you want new transactions. How far back do you have to go to look for any instance of them…

For transaction growth you can maybe take a more simple approach to first identify if you have growth and then make some queries to find the new ones.

This will show you distinct transaction names over time and compare them with yesterday.

select uniqueCount(name) from Transaction since 23 hours ago TIMESERIES 1 hour COMPARE WITH 1 day ago

Typically, it’s not so simply to do this since applications with a number of transaction have some variable throughput of them over time. Like there may not be any traffic on a transaction at 3am.

You can try to table the transactions and then sort and look for gaps on an hourly basis if you don’t want to write some code for the logic.

SELECT filter(count(*), where hourOf(timestamp) = '12:00') as '.12',filter(count(*), where hourOf(timestamp) = '13:00') as '.1pm', filter(count(*), where hourOf(timestamp) = '14:00') as '.2pm' from Transaction facet name since 23 hours ago limit MAX

Its possible someone else has some clever tricks.

3 Likes

Ok i think i found what i would want to do , i built it upon your second query can you help me fix the timestamp?

SELECT filter(count(), where hourOf(timestamp) >= ‘00:00’ AND hourOf(timestamp) < ‘08:00’ AND appname = ‘yanir’) as ‘Transaction number from 0-8’,filter(count(), where hourOf(timestamp) >= '08:00’AND hourOf(timestamp) <= ‘16:00’ AND appname = ‘yanir’) as ‘transaction number from 8-16’, filter(count(*), where hourOf(timestamp) > ‘16:00’ AND hourOf(timestamp) < ‘16-0’ AND appname = ‘yanir’) as ‘transaction number from 16-0’ from Transaction facet name since 23 hours ago limit MAX

It seems that 00:00 does not count as the timestamp needed.

You wan to use = and not >=. To find out what the text values needs to be for matching the time you can do this:

Select count(*) from transaction facet hourof(timestamp) since 24 hours ago limit max

You do not need to put your appName in the filters if they are all the same. You can put it in a where clause after Transaction.

2 Likes

@yanir.cohen Following up to see if @6MM suggestion helped you create this query?

Yes, ofc sorry for not responding , I succeeded because of him

1 Like

@yanir.cohen Great to hear! I will mark this as resolved then. :slight_smile: