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.
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())
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
Can you try the updated query and share the results if it doesn't work.
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())
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!
Thanks for sharing. Try one of the two updated query above. See that works.