Splunk Search

What is the best way to streamline a search with a date variable that is based on x numbers of months back?

splunker1981
Path Finder

Hello experts,

I've been banging me head trying to figure out how to best approach this, keep in mind that I'm relatively new to Splunk. Monthly I go through and put together some sales metrics which shows the trends from the previous months. What I've been doing is manually changing the date in my searches and would like to have a more streamlined search that I don't have to update every month. I'm kinda stuck coming up with a way to set each month into their respective monthly group.

I have 4 main fields that I pull summary from:
acquired
sold
shipped
department

What I do is a stats count by the first three fields then group by department

First, I run some evals to set which month each event in those fields falls under, then I run stats count to count each one by month - sample search below.

initialSearch
|eval 1monthaq=if(like(acquired,"2016-11-%"), "Y", null())
|eval 2monthaq=if(like(acquired,"2016-10-%"), "Y", null())
|eval 1monthsold=if(like(sold,"2016-11-%"), "Y", null())
|eval 2monthsold=if(like(sold,"2016-10-%"), "Y", null())
|eval 1monthship=if(like(shipped,"2016-11-%"), "Y", null())
|eval 2monthship=if(like(shipped,"2016-10-%"), "Y", null())
|stats count each of the above by department

Since I always run this at the start or middle of the next month, is there an easy way for me to replace each of the 2016-11-% and 2016-10-% wildcards with something like strftime(now(),"%Y-%m") and then just subtract 1 for last month and 2 for 2 months ago from the %m in the date? Or this a better way to do this altogether?

Ultimately, what I'd like to do is get a count of each of the fields listed above (acquired, sold, shipped) by department and only include and group events that fall within each of their respective months.

sample data:

acquired=2016-09-01T01:22:56.000+0000
sold=2016-09-02T14:32:32.000+0000
shipped=2016-09-02T17:21:56.000+0000
department=mobile

acquired=2016-09-01T01:22:56.000+0000
sold=2016-09-03T14:32:32.000+0000
shipped=2016-09-03T17:21:56.000+0000
department=parts

acquired=2016-10-01T01:22:56.000+0000
sold=2016-10-02T14:32:32.000+0000
shipped=2016-10-02T17:21:56.000+0000
department=mobile

acquired=2016-10-03T01:22:56.000+0000
sold=2016-10-04T14:32:32.000+0000
shipped=2016-10-07T17:21:56.000+0000
department=mobile

Results I'm hoping for

Department       2016-10-shipped      2016-11-shipped     2016-10-sold    2016-11-sold
 mobile                 1                        0                  0                        1
 parts                  1                        0                  1                        1

Greatly appreciate the help.

0 Karma

sundareshr
Legend

Try like this

... | foreach * [eval month_<<FIELD>>=if('<<FIELD>>'="acquired" OR '<<FIELD>>'="sold" OR '<<FIELD>>'="shipped", strftime(strptime('<<FIELD>>', "%Y-%m-%dT%H:%M:%S.%3N")."_".<<FIELD>>, "%a"), <<FIELD>>) ] | eval {month_*}=1 | stats count(*sold) as *sold count(*acquired ) as *acquired count(*shipped) as *shipped by department

*OR*, use this logic in your query to dynamically get months, without having to change the date each month

| eval event_mon=strftime(_time, "%m")
| eval curr_mon=strftime(now(), "%m")
| eval 1monthship=if(curr_mon-event_mon=1, "Y", null())
0 Karma

splunker1981
Path Finder

Thanks for the response, Sundareshr. This is not quite what I'm looking for however, although cool what you did. What I would like to have is count displayed by month

Department       2016-10-shipped      2016-11-shipped     2016-10-sold    2016-11-sold
mobile                 1                        0                  0                        1
parts                    1                        0                  1                         1
0 Karma

sundareshr
Legend

Can you try the updated query and share the results if it doesn't work.

0 Karma

splunker1981
Path Finder

Thanks again for the response. The search works and I get results, the problem is that that it's not breaking it down by month. Meaning, if I do search for the previous two months then want to show each count specific to the month, it's not displaying it that way.

I get something like this instead and I can't tell what each count by month was, instead it's counting everything irregardless of whether if falls in the last month or last 2 months.

 Department       month_shipped      month_acquired     month_sold   
  mobile                 1                        0                  0                        1
  parts                  1                        0                  1                        1

Instead I would like to see something by month (I'm always filtering on the last two months) so in this case I would want anything that falls under 2016-10-% to be group and displayed by a count and everything 2016-11-% the same way.

Department 2016-10-shipped 2016-11-shipped 2016-10-sold 2016-11-sold
mobile 1 0 0 1
parts 1 0 1 1

The way I was doing that was by running the following evals, then doing a count by 1monthship, count(2monthship), but I was trying to avoid having to go in and change the dates every month I need to run this search.

|eval 1monthship=if(like(shipped,"2016-11-%"), "Y", null())
 |eval 2monthship=if(like(shipped,"2016-10-%"), "Y", null())
0 Karma

aaraneta_splunk
Splunk Employee
Splunk Employee

Hi @splunker1981 - Did one of sundareshr's updated queries help answer your question? If yes, please don't forget to click "Accept" to resolve this post. If no, please leave a comment with some feedback. Thanks!

0 Karma

sundareshr
Legend

Thanks for sharing. Try one of the two updated query above. See that works.

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 ...