Splunk Search

How to show output for last week?

wanda619
Path Finder

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
Labels (6)
Tags (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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?

0 Karma

wanda619
Path Finder

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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?

wanda619
Path Finder

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

CustomerNameErrorResultPercentOfTotalResult_Prev
abc1003 (Invalid Birth Year)10.952 
abc1006 (Invalid UnderwritingState)10.952 
abc1013 (Invalid Drivers License Format)1110.476 
abc1023 (Invalid Name)21.905 
abc1027 (Invalid UnderwritingState)8076.19 
abc1305 (Unable to connect to data provider)109.524 
abcTotal105100 
abc21023 (Invalid Name)1657.143 
abc21201 (Lookback Date Not Set / Offset = 0)27.143 
abc21305 (Unable to connect to data provider)1035.714 
abc2Total28100 
abc31027 (Invalid UnderwritingState)1593.75 
abc39999 (Timeout expired (9999))16.25 
abc3Total16100 

 

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

wanda619
Path Finder

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

CustomerNameErrorResult
ABC1003 (Invalid Birth Year)1
ABC1013 (Invalid Drivers License Format)10
ABC1023 (Invalid Name)2
ABC1027 (Invalid UnderwritingState)118
ABC1305 (Unable to connect to data provider)1
ABCTotal132
XYZ1023 (Invalid Name)12
XYZ1201 (Lookback Date Not Set / Offset = 0)3
XYZ1305 (Unable to connect to data provider)8
XYZTotal23
PQR1027 (Invalid UnderwritingState)17
PQR9999 (Timeout expired (9999))1
PQRTotal18
QPR1003 (Invalid Birth Year)1
QPR1013 (Invalid Drivers License Format)3
QPR1023 (Invalid Name)9
QPR1026 (Drivers License Number is a required field)2
QPR9999 (Timeout expired (9999))5
QPRTotal20

 

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? 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

@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

Screen Shot 2022-08-24 at 10.09.13 PM.png

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?

Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...