topic Re: How to trend data over backlog data with input lookup in Splunk Search

How to trend data over backlog data with input lookup

Sfry1981 — Wed, 07 Aug 2019 15:00:27 GMT

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 .

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

Sukisen1981 — Wed, 07 Aug 2019 15:05:36 GMT

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

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

Sfry1981 — Wed, 07 Aug 2019 15:11:12 GMT

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

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

jawaharas — Thu, 08 Aug 2019 06:17:37 GMT

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

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

Sfry1981 — Thu, 08 Aug 2019 07:03:36 GMT

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

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

Sukisen1981 — Thu, 08 Aug 2019 07:14:49 GMT

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

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

jawaharas — Thu, 08 Aug 2019 07:24:31 GMT

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

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

Sfry1981 — Thu, 08 Aug 2019 09:36:11 GMT

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

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

jawaharas — Thu, 08 Aug 2019 09:58:55 GMT

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

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

Sfry1981 — Fri, 09 Aug 2019 15:32:07 GMT

@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

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

jawaharas — Sat, 10 Aug 2019 15:56:56 GMT

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

.. | timechart count span=30d

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

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

Sfry1981 — Mon, 12 Aug 2019 13:24:31 GMT

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

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

legogizmo — Thu, 22 Aug 2019 20:25:46 GMT

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.

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

Sfry1981 — Fri, 23 Aug 2019 07:38:10 GMT

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