topic Re: How to remove duplicates from the table? in Splunk Search

How to remove duplicates from the table?

wanda619 — Fri, 05 Aug 2022 15:14:14 GMT

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

Re: How to remove duplicates from the table?

gcusello — Fri, 05 Aug 2022 15:23:42 GMT

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

Re: How to remove duplicates from the table?

wanda619 — Fri, 05 Aug 2022 16:13:30 GMT

Yes, I am looking for such output -

Client	Error_code	Error Results
abc	1003	5
abc	1013	3
abc	1027	8
abc	Total	16

Re: How to remove duplicates from the table?

gcusello — Wed, 10 Aug 2022 06:01:59 GMT

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

Re: How to remove duplicates from the table?

wanda619 — Mon, 08 Aug 2022 21:10:29 GMT

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?

Re: How to remove duplicates from the table?

yuanliu — Wed, 10 Aug 2022 03:34:48 GMT

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

Re: How to remove duplicates from the table?

gcusello — Wed, 10 Aug 2022 06:17:59 GMT

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