Hi community,
I have table like below -
Client | Error_code | Error Results |
abc | 1003 | 2 |
abc | 1003 | 3 |
abc | 1013 | 1 |
abc | 1027 | 3 |
abc | 1027 | 5 |
abc | 1013 | 2 |
abc | Total | 16 |
I am trying to have distinct error codes in the table combining error results as well. I tries stats, dedup, didn't work
Hi @wanda619,
let me understand: you want to have the number or error_results for each client and error_code, is it correct'
you can use stats:
<your_search>
| stats sum(Error_Results) AS Error_Results BY Client Error_Code
If instead you want to know how many Error_Codes you have for each Client, you can try:
<your_search>
| stats dc (Error_Code) AS Error_Code_count BY Client
Ciao.
Giuseppe
Yes, I am looking for such output -
Client | Error_code | Error Results |
abc | 1003 | 5 |
abc | 1013 | 3 |
abc | 1027 | 8 |
abc | Total | 16 |
Suppose all you want is to tally by Error_code, this simple stats should do:
| stats values(Client) as Client sum(eval('Error Results')) as "Error Results" by Error_code
You may want to manage how "Client" column is presented if there are many. But the basic idea will be the same
Hi @wanda619,
ok, the first solution is the correct one:
<your_search>
| stats sum(Error_Results) AS Error_Results BY Client Error_Code
| addcoltotals labelfield="Error_Code" label="Total"
Ciao.
Giuseppe
hi @gcusello
Client | Error | Error Results | Error ResultsPrevious week | Percent of Total | PercentDifference |
abc | 1003 | 2 | 0 | 12.5 | 0 |
abc | 1003 | 3 |
| 12.5 | 0 |
abc | 1013 | 1 | 2 | 342 | -50 |
abc | 1027 | 3 | 3 | 5 | 0 |
abc | 1027 | 5 | xyz | 43 | zyz |
abc | 1013 | 2 | zyz | 432 | et |
abc | Total | 16 | zyds | 423 | tert |
My code is --
| bucket _time span=1w
| stats count as Result by LicenseKey, Error_Code
| eval Client=coalesce(Client,LicenseKey)
| eventstats sum(Result) as Total by Client
| eval PercentOfTotal = round((Result/Total)*100,3)
| sort - _time
| streamstats current=f latest(Result) as Result_Prev by LicenseKey
| eval PercentDifference = round(((Result/Result_Prev)-1)*100,2)
| fillnull value="0"
| append
[ search index=abc sourcetype=yxx source= bff ErrorCode!=0
| `DedupDHI`
| lookup abc LicenseKey OUTPUT Client
| eval Client=coalesce(Client,LicenseKey)
| stats count as Result by Client
| eval ErrorCode="Total", PercentOfTotal=100]
| lookup xyz_ErrorCodes ErrorCode OUTPUT Description
| lookup uyz LicenseKey OUTPUT Client
| eval Client=coalesce(Client,LicenseKey)
| eval Error=if(ErrorCode!="Total", ErrorCode+" ("+coalesce(Description,"Description Missing - Update xyz_ErrorCodes")+")", ErrorCode)
| fields Client, Error, Result, PercentOfTotal, PercentDifference, Error results previous week
| sort CustomerName, Error, PercentDifference
Still not able to figure out the duplicate row issue, single row for one each error combined with total.
any suggestions please?
Hi @wanda619,
the search you shared is incomplete, I cannot see the first part of your search.
Anyway, I see something not correct, e.g. when you write
| stats count as Result by LicenseKey, Error_Code
| eval Client=coalesce(Client,LicenseKey)
the second row is unuseful because after a stats command you have only the fields in stats, so you haven't the Client filed you used in coalesce, probably you have to put it before the stats command.
Then If you use a macro, I cannot see its content.
Then using the lookup command (| lookup uyz LicenseKey OUTPUT Client) you already have the "Client" field so in this way you override the original values for this field.
So, my hint is to re-design your search starting from the main search.
Ciao.
Giuseppe