topic Re: get total results per client per item for the last 7 days in Splunk Search

get total results per client per item for the last 7 days

jdepp — Wed, 30 Apr 2014 15:31:45 GMT

I would like to create a panel that displays in a table a historical records of counts for the last 7 days. The totals would be per client per item which follows this json format:

  {"authorUrl":"michael_scooter","pubDate":"2014-04-30 11:27:49","clientId":"665",
  "itemSold":"((samsung TV))"}

So basically I would like to view the total counts of itemSold for each clientId for the last 7 days.Here is what i have now:

search sourcetype="itemsSold.newIndex.stats"| timechart count by itemSold

Thanks.

Re: get total results per client per item for the last 7 days

somesoni2 — Wed, 30 Apr 2014 15:47:26 GMT

If with search

sourcetype="itemsSold.newIndex.stats" | table _time, clientId, itemSold

You get output like

_time                clientId     itemSold
2014-04-30 11:27:49    665     ((samsung TV)) 
2014-04-30 11:29:49    669     ((apple TV))        -- example
....
....

Updated Query

Try this

sourcetype="itemsSold.newIndex.stats" earliest=-7d@d | bucket span=1d _time | stats count by _time, clientId,itemSold

sourcetype="itemsSold.newIndex.stats" earliest=-7d@d | stats count by clientId, itemSold

Re: get total results per client per item for the last 7 days

jdepp — Thu, 01 May 2014 13:51:01 GMT

Thanks for replying. For some reason I am getting the following error:

Error in 'timechart' command: The argument 'itemSold' is invalid.

sourcetype="itemSold.newIndex.stats" earliest=-7d@d | timechart span=1d count by clientId, itemSold

I'm not sure about your search query:

mine just looks like this:

source="/itemSold.newIndex/tcp/xxxx6"

Re: get total results per client per item for the last 7 days

somesoni2 — Thu, 01 May 2014 14:19:22 GMT

My bad, try the updated answer.

Re: get total results per client per item for the last 7 days

jdepp — Thu, 01 May 2014 20:42:53 GMT

you have been extremely helpful and I don't want to push my luck here but unfortunately its almost there but not quite. I think I need to see total itemSold for each clientId over 7 days within its own panel. That would make more sense, so I just need to break it down per clientID; so how do I code the limit: clientId = '665'

Re: get total results per client per item for the last 7 days

somesoni2 — Thu, 01 May 2014 21:18:46 GMT

Add the criteria in the base search. before first pipe.

e.g.
sourcetype="itemsSold.newIndex.stats" earliest=-7d@d clientId='665'| stats count by clientId, itemSold

Re: get total results per client per item for the last 7 days

jdepp — Thu, 01 May 2014 21:31:06 GMT

thanks got it.