topic Re: How to show output for last week? in Splunk Search

How to show output for last week?

wanda619 — Fri, 19 Aug 2022 16:53:55 GMT

I have spent days working on this, can someone help? how to populate previous week results? Also there are different license keys for same errors that is why it is showing 2 entries.

I have the following code

index=test sourcetype=dhi:testdata ErrorCode!=0
| `DedupDHI`
| bucket _time span=1w
| lookup table1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| stats count as Result by CustomerName,ErrorCode,_time
| eventstats sum(Result) as Total by CustomerName
| eval PercentOfTotal = round((Result/Total)*100,3)
| streamstats current=f latest(Result) as Result_Prev by CustomerName,ErrorCode
| eval PercentDifference = round(((Result/Result_Prev)-1)*100,2)
| fillnull value="0"
| append
[ search index=test sourcetype=dhi:testdata ErrorCode!=0
| `DedupDHI`
| lookup table1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| stats count as Result by CustomerName
| eval ErrorCode="Total", PercentOfTotal=100]
| fillnull value="0"
| lookup table2 ErrorCode OUTPUT Description
| lookup table1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| eval Error=if(ErrorCode!="Total", ErrorCode+" ("+coalesce(Description,"Description Missing - Update table2")+")", ErrorCode)
| rename Result_Prev as "Previous Week Results", PercentDifference as " Percent Difference", PercentOfTotal as "Percent of Total"
| fields CustomerName, Error, Result,"Previous Week Results", " Percent Difference" , "Percent of Total"
| sort CustomerName, Error, PercentDifference

OUTPUT -

CustomerName	Error	Result	Previous Week Results	Percent Difference	Percent of Total	_time
customer_1	1002 (Invalid Address State Code. The two digit state code is invalid)	4	0	0	3.361	2022-08-12T00:00:00.000-0500
customer_1	1003 (Invalid Birth Year)	1	0	0	0.84	2022-08-12T00:00:00.000-0500
customer_1	1006 (Invalid UnderwritingState)	1	0	0	0.84	2022-08-12T00:00:00.000-0500
customer_1	1013 (Invalid Drivers License Format)	12	0	0	10.084	2022-08-12T00:00:00.000-0500
customer_1	1013 (Invalid Drivers License Format)	1	12	-91.67	0.84	2022-08-19T00:00:00.000-0500
customer_1	1023 (Invalid Name)	3	0	0	2.521	2022-08-12T00:00:00.000-0500
customer_1	1027 (Invalid UnderwritingState)	87	0	0	73.109	2022-08-12T00:00:00.000-0500
customer_1	1027 (Invalid UnderwritingState)	1	87	-98.85	0.84	2022-08-19T00:00:00.000-0500
customer_1	1305 (Unable to connect to data provider)	9	0	0	7.563	2022-08-12T00:00:00.000-0500
customer_1	Total	119	0	0	100	1969-12-31T18:00:00.000-0500
customer_2	1023 (Invalid Name)	16	0	0	55.172	2022-08-12T00:00:00.000-0500
customer_2	1201 (Lookback Date Not Set / Offset = 0)	1	0	0	3.448	2022-08-12T00:00:00.000-0500
customer_2	1305 (Unable to connect to data provider)	11	0	0	37.931	2022-08-12T00:00:00.000-0500
customer_2	1305 (Unable to connect to data provider)	1	11	-90.91	3.448	2022-08-19T00:00:00.000-0500
customer_2	Total	29	0	0	100	1969-12-31T18:00:00.000-0500
customer_3	1023 (Invalid Name)	3	0	0	20	2022-08-12T00:00:00.000-0500
customer_3	1027 (Invalid UnderwritingState)	11	0	0	73.333	2022-08-12T00:00:00.000-0500
customer_3	9999 (Timeout expired (9999))	1	0	0	6.667	2022-08-12T00:00:00.000-0500
customer_3	Total	15	0	0	100	1969-12-31T18:00:00.000-0500
customer_4	1003 (Invalid Birth Year)	1	0	0	3.846	2022-08-12T00:00:00.000-0500
customer_4	1013 (Invalid Drivers License Format)	5	0	0	19.231	2022-08-12T00:00:00.000-0500
customer_4	1013 (Invalid Drivers License Format)	1	5	-80	3.846	2022-08-19T00:00:00.000-0500
customer_4	1023 (Invalid Name)	14	0	0	53.846	2022-08-12T00:00:00.000-0500
customer_4	1026 (Drivers License Number is a required field)	3	0	0	11.538	2022-08-12T00:00:00.000-0500
customer_4	9999 (Timeout expired (9999))	1	0	0	3.846	2022-08-12T00:00:00.000-0500
customer_4	9999 (Timeout expired (9999))	1	1	0	3.846	2022-08-19T00:00:00.000-0500
customer_4	Total	26	0	0	100	1969-12-31T18:00:00.000-0500

Re: How to show output for last week?

yuanliu — Sat, 20 Aug 2022 04:39:15 GMT

You have some code that calculates latest(Result) as Result_Prev (aka "Previous Week Results"). You have an output that shows "Previous Week Results" in the last column. Can you explain what is the question or rather, what is missing?

Re: How to show output for last week?

wanda619 — Mon, 22 Aug 2022 02:42:10 GMT

@yuanliu The Previous Week Results output shows incorrect values. The code I am trying shows incorrect values. The results column is the calculation for error codes for each customer for current week. I want to calculate the Results for previous week, so that i can also calculate Percentage difference further. did this clarify?

Re: How to show output for last week?

yuanliu — Mon, 22 Aug 2022 05:19:30 GMT

What is "incorrect" about the values? Without data, there is no correctness. Can you illustrate sample data and reduce your sample code to the minimum for the purpose of demonstrating why the result is wrong?

Re: How to show output for last week?

wanda619 — Mon, 22 Aug 2022 17:45:19 GMT

@yuanliu so I tried another way - here is my code -

index=test sourcetype=dhi:testdata ErrorCode!=0 | `DedupDHI`
| lookup table1 LicenseKey OUTPUT CustomerName,FriendlyName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| stats count as Result by CustomerName, ErrorCode
| eventstats sum(Result) as Total by CustomerName
| eval PercentOfTotal = round((Result/Total)*100,3)
| streamstats current=f latest(Result) as Result_Prev by Errorcode,CustomerName
| eval PercentOfTotal=100
| append
[search index=test sourcetype=dhi:testdata ErrorCode!=0
| lookup table1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| stats count as Result by CustomerName
| eval ErrorCode="Total", PercentOfTotal=100]
| lookup table2 ErrorCode OUTPUT Description
| lookup table1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| eval Error=if(ErrorCode!="Total", ErrorCode+" ("+coalesce(Description,"Description Missing - Update table2")+")", ErrorCode)
| fields CustomerName, Error, Result,Result_Prev
| sort CustomerName, Error

Trying to calculate the output for previous week results (retreving stats for previous week) it, the output seems to be like this -

CustomerName	Error	Result	PercentOfTotal	Result_Prev
abc	1003 (Invalid Birth Year)	1	0.952
abc	1006 (Invalid UnderwritingState)	1	0.952
abc	1013 (Invalid Drivers License Format)	11	10.476
abc	1023 (Invalid Name)	2	1.905
abc	1027 (Invalid UnderwritingState)	80	76.19
abc	1305 (Unable to connect to data provider)	10	9.524
abc	Total	105	100
abc2	1023 (Invalid Name)	16	57.143
abc2	1201 (Lookback Date Not Set / Offset = 0)	2	7.143
abc2	1305 (Unable to connect to data provider)	10	35.714
abc2	Total	28	100
abc3	1027 (Invalid UnderwritingState)	15	93.75
abc3	9999 (Timeout expired (9999))	1	6.25
abc3	Total	16	100

Re: How to show output for last week?

yuanliu — Tue, 23 Aug 2022 05:10:30 GMT

I get that you are demonstrating a mockup that contains no output for Results_Prev. However, I still can't see why it should not be so. Can you illustrate sample data? Without data, there is no telling whether Results_Prev should contain any output other than null.

Also, it is impossible for another person to read through sample code this complex without access to real or emulated data. If you do not have ready-to-share data, at least simplify the code to an extent that it is easily understandable by someone who doesn't work in your environment.

Re: How to show output for last week?

wanda619 — Wed, 24 Aug 2022 18:59:35 GMT

@yuanliu This is the basic code from which I am trying to get the stats for previous week.

index=sample sourcetype=ABC Table ErrorCode!=0 | `DedupDHI`
| lookup Table_1 LicenseKey OUTPUT CustomerName,FriendlyName
| stats count as Result by CustomerName, ErrorCode
| eval PercentOfTotal=100
| append
[search index=sample sourcetype=ABC Table ErrorCode!=0
| lookup Table_1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| stats count as Result by CustomerName
| eval ErrorCode="Total", PercentOfTotal=100]
| lookup Table_2 ErrorCode OUTPUT Description
| lookup Table_1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| eval Error=if(ErrorCode!="Total", ErrorCode+" ("+coalesce(Description,"Description Missing - Update Table_2")+")", ErrorCode)
| fields CustomerName, Error, Result
| sort CustomerName, Error

Output from above code -

CustomerName	Error	Result
ABC	1003 (Invalid Birth Year)	1
ABC	1013 (Invalid Drivers License Format)	10
ABC	1023 (Invalid Name)	2
ABC	1027 (Invalid UnderwritingState)	118
ABC	1305 (Unable to connect to data provider)	1
ABC	Total	132
XYZ	1023 (Invalid Name)	12
XYZ	1201 (Lookback Date Not Set / Offset = 0)	3
XYZ	1305 (Unable to connect to data provider)	8
XYZ	Total	23
PQR	1027 (Invalid UnderwritingState)	17
PQR	9999 (Timeout expired (9999))	1
PQR	Total	18
QPR	1003 (Invalid Birth Year)	1
QPR	1013 (Invalid Drivers License Format)	3
QPR	1023 (Invalid Name)	9
QPR	1026 (Drivers License Number is a required field)	2
QPR	9999 (Timeout expired (9999))	5
QPR	Total	20

Based on this result I am trying to calculate the stats for previous week as results for previous week.

Unfornunatley, don't have ready- to-share data. But the results column is the error code for a particular customer for this week; I want to get a column where i could see results for previous week. Hope this helps?

Re: How to show output for last week?

yuanliu — Thu, 25 Aug 2022 05:13:32 GMT

@wanda619 This is why I find the question impossible to answer: The search contains no element that is related to time. How can someone who has not seen the data know which result will be what you want (from previous week) and which will be incorrect (from current week)?

If you want to change search period, i.e., you can always use the time selector, like

If you want to embed something in the search, you can do something like "earliest=-2w latest=-1w".

Is your question related to search period or something else?