Archive

Report results that are new this month

Path Finder

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.

Tags (4)
0 Karma
1 Solution

Ultra Champion

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

View solution in original post

Engager

hi can i get the query for the same for day wise (every day) ?

Thanks & regards,
Sunnybmv

0 Karma

New Member

Hi,

I think you can try by changing month to day

like earliest=-1d@day latest=@d

0 Karma

Ultra Champion

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

View solution in original post

Contributor

Just what I was looking for! Thanks

0 Karma

Path Finder

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

0 Karma