Splunk Search
Highlighted

How to trend data over backlog data with input lookup

Communicator

I have the below query which updates from an input lookup but what I want is trend data that shows what the total amount was each day.
i.e it was 275 on the 07/08 and it was 260 14/08

| inputlookup int_case | search latest_status__c!=Closed latest_status__c!=Approved latest_status__c!="Cancelled" (issue_url__c!="null" OR jira_issue_id__c!="null") labels__c!="*test1*" labels__c!=*test2*  issue_key__c=ip-* latest_status__c!=null system_area_1__c!=test1 system_area_1__c!=test2* owner_role__c=*test3* if_parent_case__c<=0000001 | stats count

I have tried using a timechart but the only way I can think to do with this is to somehow store the previous results so it can show the previous data .

Highlighted

Re: How to trend data over backlog data with input lookup

Champion

what happens if you use |timechart span=1d count

0 Karma
Highlighted

Re: How to trend data over backlog data with input lookup

Communicator

Not sure if my comment posted as i cant see it so will post again below

If i use that it just counts the amounts that fall on that date

_time count
2019-05-29 6
2019-06-01 1
2019-07-02 3
2019-07-30 4
2019-07-31 1
2019-08-02 2
2019-08-03 2
2019-08-04 1
2019-08-05 1
2019-08-06 1
2019-08-07 255

0 Karma
Highlighted

Re: How to trend data over backlog data with input lookup

Motivator
|timechart span=1d count 

Above code will give you number of events per day. Isn't this you are after?

"total amount for each day" --> What does this meant? You want to get sum of any field for a particular day?

0 Karma
Highlighted

Re: How to trend data over backlog data with input lookup

Communicator

its the total count f what it was for that day so if on the 06th august it was 251 and then on the 07th august its 265 then i want it to retain those values.

I think what i have not made clear is that its a live feed where a status can change for example:

on the 06th august there were 265 opn tickets and then in the 07th august 14 extra tickets were created but then on the 08th august 20 tickets were closed that was related to this total figure which brings its to 245 tickets left open so the 265 tickets no longer exist and only 245 do so i think what im looking for is for splunk to somehow store the previous days numbers so i can use them for a trend

0 Karma
Highlighted

Re: How to trend data over backlog data with input lookup

Champion

@Sfry1981 - I think i understood what you are looking for, for example say on a Monday you had 10 open tickets , and on tuesday 3 got closed and 2 new were reported, so for tuesday you would want 10-3+2=9. That part is ok but you also want Monday's 10 open to be showed.
No, that is not possible with the data structure you have, namely if you overwrite the status of a ticket based on its latest status. You need more base data - either have some audit logs with the status along with the live feed (in which case each ticket will have more than 1 row) or you manually write each day's info into an outputfile and then combine that with the live data for the current day.
But since you say it is a live feed , suppose a ticket got reported on Monday, ticket XXX and this got closed on Friday does your live feed have more than one row for ticket xxx?

0 Karma
Highlighted

Re: How to trend data over backlog data with input lookup

Motivator

Try this:

| [BASE SEARCH]
| timechart span=1d count as TodayCount
| streamstats current=f window=1 last(TodayCount) as YesterdayCount
| eval Total=YesterdayCount+TodayCount
| fields - YesterdayCount
0 Karma
Highlighted

Re: How to trend data over backlog data with input lookup

Communicator

@jawaharas

This looks very close to what i need but have a couple of questions

when i add the commands you provided it shows the total split by the _time which is expected but can it be that it just shows the 1 total from today and yesterday so essentially i need it to add all dates together?

Also is there a way for it to show as an ongoing trend rather than just today and yesterday?

0 Karma
Highlighted

Re: How to trend data over backlog data with input lookup

Motivator

Puzzled with the term 'ongoing trend'. Can you provide sample input and expected output in tabular format?

0 Karma
Highlighted

Re: How to trend data over backlog data with input lookup

Communicator

@jawaharas

When i mean ongoing trend i mean like timechart count span=1d over a 7 day period so rather than just recording a trend between yesterday and today i want it to record everyday so over a period of a month i can show what the count was a month ago compared to today

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.