Splunk Search

Report results that are new this month

atornes
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

kristian_kolb
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

sunnybmv
Engager

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

Thanks & regards,
Sunnybmv

0 Karma

cdev24
New Member

Hi,

I think you can try by changing month to day

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

0 Karma

kristian_kolb
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

DanielFordWA
Contributor

Just what I was looking for! Thanks

0 Karma

atornes
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...