Splunk Search

How to remove duplicates from the table?

wanda619
Path Finder

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

Labels (3)
Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

wanda619
Path Finder

Yes, I am looking for such output - 

Client

Error_code

Error Results

abc

1003

5

abc

1013

3

abc

1027

8

abc

Total

16

Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

wanda619
Path Finder

 

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? 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...