How to compare two fields values counts ?

[karthi2809]

Hi Guys,

In my scenario i want to compare two column values .If its match its fine if the values is in difference i want to display both the field values in some colour in the splunk dashboard.

Field1	Field2
28	28
100	99
33	56
18	18

[Gr0und_Z3r0]

@karthi2809  one advice here, don't post your org-specific queries as is. Obfuscate content to its maximum before posting in any public forum.

[ITWhisperer]

One way is to use CSS and multivalue fields where the second value in the multivalue field is used to determine the colour

See the reply here for an example How to color the columns based on previous column... - Splunk Community

[karthi2809]

@ITWhisperer I want to compare two field values match.If does not match i want to colour both the fields.

index="mulesoft" applicationName="s-concur-api" environment=PRD priority timestamp (tracePoint="EXCEPTION") OR
    ("Concur Ondemand Started*") OR (message="Expense Extract Process started for jobName :*") OR ("Before Calling flow archive-Concur*") OR (message="*(SUCCESS): Concur AP/GL Extract V*") OR (message="Records Count Validation*") OR (message="API: START: /v1/expense/extract/ondemand*" OR message="API: START: /v1/fin*") OR (message="Post - Expense Extract processing to Oracle*") OR (message="Concur AP/GL File/s Process Status") OR (message="*(ERROR):*") 
| search NOT message IN ("API: START: /v1/expense/extract/ondemand/accrual*") 
| spath content.payload{} 
| mvexpand content.payload{} 
| transaction correlationId 
| rename content.SourceFileName as SourceFileName content.JobName as JobName content.loggerPayload.archiveFileName AS ArchivedFileName content.payload{} as response content.Region as Region content.ConcurRunId as ConcurRunId content.HeaderCount as HeaderCount content.SourceFileDTLCount 
    as SourceFileDTLCount content.APRecordsCountStaged
    as APRecordsCountStaged content.GLRecordsCountStaged
    as GLRecordsCountStaged content.TotalAPGLRecordsCountStaged as TotalAPGLRecordsCountStaged 
    content.ErrorMsg as errorMessage content.errorMsg as error content.errorMsg as error "content.payload{}.AP Import flow processing results{}.requestID" as RequestID "content.payload{}.GL Import flow processing results{}.impConReqId" as ImpConReqId 
| rex field=message max_match=0 "Expense Extract Process started for (?<FileName>[^\n]+)" 
| rex field=message max_match=0 "API: START: /v1/expense/extract/ondemand/(?<OtherRegion>[^\/]+)\/(?<OnDemandFileName>\S+)" 
| eval OtherRegion=upper(OtherRegion) 
| eval OnDemandFileName=rtrim(OnDemandFileName,"Job") 
| eval "FileName/JobName"= coalesce(OnDemandFileName,JobName) 
| eval JobType=case(like('message',"%Concur Ondemand Started%"),"OnDemand",like('message',"%API: START: /v1/expense/extract/ondemand%"),"OnDemand",like('message',"Expense Extract Process started%"),"Scheduled") 
| eval Status=case(like('message' ,"%Concur AP/GL File/s Process Status%"),"SUCCESS", like('tracePoint',"%EXCEPTION%"),"ERROR") 
| eval Region= coalesce(Region,OtherRegion) 
| eval OracleRequestId=mvappend("RequestId:",RequestID,"ImpConReqid:",ImpConReqId) 
| eval CheckMatch = if(isnull(SourceFileDTLCount) OR isnull(TotalAPGLRecordsCountStaged), "not matched", "matched") 
| eventstats min(timestamp) AS Logon_Time, max(timestamp) AS Logoff_Time by correlationId 
| eval StartTime=round(strptime(Logon_Time, "%Y-%m-%dT%H:%M:%S.%QZ")) 
| eval EndTime=round(strptime(Logoff_Time, "%Y-%m-%dT%H:%M:%S.%QZ")) 
| eval ElapsedTimeInSecs=EndTime-StartTime 
| eval "Total Elapsed Time"=strftime(ElapsedTimeInSecs,"%H:%M:%S") 
| eval sign=if(SourceFileDTLCount == TotalAPGLRecordsCountStaged,"GREEN", "YELLOW") 
| rename Logon_Time as Timestamp 
| table Status JobType "FileName/JobName" Timestamp Region ConcurRunId HeaderCount SourceFileDTLCount APRecordsCountStaged GLRecordsCountStaged TotalAPGLRecordsCountStaged ArchivedFileName ElapsedTimeInSecs "Total Elapsed Time" OracleRequestId correlationId 
| join correlationId type=left 
    [ search index="mulesoft" applicationName="s-concur-api" (message="*(SUCCESS): Concur AP/GL Extract V.3.02 - *. Concur Batch ID: * Company Code: * Operating Unit: *") 
    | eval Message=case(like('message',"%(SUCCESS): Concur AP/GL Extract V.3.02 - %. Concur Batch ID: % Company Code: % Operating Unit: %"),message) 
    | table Message correlationId
        ] 
| eval Response= coalesce(error,errorMessage,Message) 
| table Status JobType "FileName/JobName" Timestamp CheckMatch Region ConcurRunId HeaderCount SourceFileDTLCount APRecordsCountStaged GLRecordsCountStaged TotalAPGLRecordsCountStaged ArchivedFileName ElapsedTimeInSecs "Total Elapsed Time" sign OracleRequestId Response correlationId 
| fields - ElapsedTimeInSecs priority 
| where JobType!=" " 
| search Status="*"

[ITWhisperer]

OK so use eval with an if such that if the two fields are equal mvappend a value that the formatting picks up to change the colour to what you want. (See the example in the link I provided)

[karthi2809]

Hi @ITWhisperer 

I used to this stanze to check the values are match.If i append in mvappend its showing both the values.How to set rules in dashboard.could you pls help on it.

| eval match=if(SourceFileDTLCount=TotalAPGLRecordsCountStaged,"Match","Not Match")
| eval SourceFileDTLCount=mvappend(SourceFileDTLCount,match)

[ITWhisperer]

As I said earlier, you can use CSS - follow the example in this reply

Re: How to color the columns based on previous co... - Splunk Community

[karthi2809]

As you mentioned i tried the mvappend the fields and its showing both the values in the table .The thing i need to show only when if it is not matched then i need to show the colours.

| eval match=if(SourceFileDTLCount=TotalAPGLRecordsCountStaged, " ", if(SourceFileDTLCount!=TotalAPGLRecordsCountStaged, "Not Match","RED"))
| eval SourceFileDTLCount=mvappend(SourceFileDTLCount,match)

[ITWhisperer · Accepted solution]

Here are the essential parts you should consider using

| eval match=if(SourceFileDTLCount!=TotalAPGLRecordsCountStaged, "RED")
| eval SourceFileDTLCount=mvappend(SourceFileDTLCount,match)

Obviously, change the tableCellColourWithoutJS to be the id of your panel

    <panel depends="$stayhidden$">
      <html>
        <style>
          #tableCellColourWithoutJS table tbody td div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
    </panel>

        <format type="color">
          <colorPalette type="expression">case (match(value,"RED"), "#ff0000")</colorPalette>
        </format>

[karthi2809]

Hi @ITWhisperer 
Its working but if i want to use any multivalue filed in the table.So the result might be affect right.If there is any possible to hide values for particular table using css.

[ITWhisperer]

As I said, change the tableCellColourWithoutJS to be the id of your panel

[karthi2809]

@ITWhisperer  I asking for particular table column. Where to use id in it. In the below screenshot both the column has multi value field .If use the tableCellColourWithoutJS another column is affecting .So I want hide mv index for particular column

[ITWhisperer]

Got it. Try like this - you may have to adjust the child number depending on which column you want the CSS to apply to

    <panel depends="$stayhidden$">
      <html>
        <style>
          #tableCellColourWithoutJS table tbody td:nth-child(1) div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
    </panel>