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