How to trend data over backlog data with input lookup

[Sfry1981]

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 .

[Sfry1981 · Accepted solution]

Thanks for your help everyone but i couldnt get it to work any of the ways suggested. I was able to create exactly what i needed by creating a summary index and storing the daily values in there and then appending that to my other data so gives me an overlay of the totals per day moving forward.

https://docs.splunk.com/Documentation/Splunk/7.3.1/Knowledge/Usesummaryindexing

[legogizmo]

I am having a similar problem, could you explain your solution in more detail?

What search did you use for the summary index?
How did you append it to your other data?
Were you able to create a backlog of data or were you limited by when you started running the summary index?

Thank you.

[Sfry1981]

Hi @legogizmo

I know you dont need all the below info but i have put it all down incase someone else wants to use it but it will answer your questions.

All i wanted was the backlog count every day so with my search i just made sure i had '| stats count' at the end and then once done i saved as a report. I dont the same with the other querys and all had '| stats count' at the end. I then went to settings>Searches, Reports, and Alerts and on each report i went to edit>edit schedule and set the below settings

Schedule> Run on cron schedule
Cron expression> 10 08-18 * * * (so it runs every 10 minutes past the hour between 08am and 06pm)
Time range > i selected 60 minutes as its pulling from an input lookup so no timeframe is really needed
Leave the other 2 dropdowns as they are
I added the action 'Log Event' just so i have a log for if there is an issue

Once thats done i went to edit>Edit summary index and saved it to my newly created index

This started to run every hour into my new index and i used the below query to append all the data and create a backlog trend

index=summary_product_data search_name="totals"
| timechart span=1d  values(count)
| rename values(count) as backlog 
| appendcols [search index=summary_product_data  search_name="total 1 count"
| rename "count" as total1count
| timechart span=1d values(total1count)]
| appendcols [search index=summary_product_data  search_name="total 2 count"
| rename "count" as total2count
| timechart span=1d values(total2count)]
| appendcols [search index=summary_product_data  search_name="total 3 count"
| rename "count" as total3count
| timechart span=1d values(total3count)]
| appendcols [search index=summary_product_data  search_name="total 4 count"
| rename "count" as total4count
| timechart span=1d values(total4count)]

This then allowed me to have a backlog chart of all the totals together but this only started from the day i created the index as we didnt store the previous values in our database as it was always overridden

[jawaharas]

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

[Sfry1981]

@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?

[jawaharas]

You can try below, if you want to compare months data.

.. | timechart count span=30d 

Can you accept the answer it it helped?. Thanks.

[jawaharas]

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

[Sfry1981]

@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

[Sukisen1981]

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

[Sfry1981]

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

[jawaharas]

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

[Sfry1981]

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

[Sukisen1981]

@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?