I am trying to create a report that only returns results that are new this past month. Further, I want it to only return results for "accounts" that first appeared in the data this past month and were not in there 2 months ago. I'd also like to know that date the "account" first appeared in the data.
Ultimately, the report will display new accounts added in the previous month and the date created.
So the report would initially display new accounts created in february that were not there in February. When it becomes March it should display new accounts for March that were not in data for February.
I sounds like a simple subsearch might do the trick here, but...your information regarding how the event is formatted is lacking a little bit.
sourcetype=your_sourcetype earliest=@mon NOT [search sourcetype=your_sourcetype earliest=-2mon@mon latest=@mon | fields + account]| reverse | dedup account| table _time, account
The subsearch (within square brackets) return a set of "account=123 OR account=124 OR account=125
", i.e. all values of account
for the previous 2 months. This is then fed to the outer search with a negation, thus removing all occurrences of 'old' accounts
from the outer search results, which only cover the current month (@mon).
After that, we reverse the results, so we get oldest-first. Then dedup. Bam. Make a table. Show it to the boss. Go home early.
Hope this helps,
Kristian
hi can i get the query for the same for day wise (every day) ?
Thanks & regards,
Sunnybmv
Hi,
I think you can try by changing month to day
like earliest=-1d@day latest=@d
I sounds like a simple subsearch might do the trick here, but...your information regarding how the event is formatted is lacking a little bit.
sourcetype=your_sourcetype earliest=@mon NOT [search sourcetype=your_sourcetype earliest=-2mon@mon latest=@mon | fields + account]| reverse | dedup account| table _time, account
The subsearch (within square brackets) return a set of "account=123 OR account=124 OR account=125
", i.e. all values of account
for the previous 2 months. This is then fed to the outer search with a negation, thus removing all occurrences of 'old' accounts
from the outer search results, which only cover the current month (@mon).
After that, we reverse the results, so we get oldest-first. Then dedup. Bam. Make a table. Show it to the boss. Go home early.
Hope this helps,
Kristian
Just what I was looking for! Thanks
i can easily write a query to generate a report of accounts today, or in the previous month, but I don't know how I would find the accounts that were new last month relative to 2 months ago or how to identify the date the account first appeared in the data.
All of our data is daily in this case