topic How to compare two fields values counts ? in Dashboards & Visualizations

How to compare two fields values counts ?

karthi2809 — Thu, 04 Apr 2024 15:09:42 GMT

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

Re: How to compare two fields values counts ?

ITWhisperer — Thu, 04 Apr 2024 15:41:53 GMT

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

Re: How to compare two fields values counts ?

karthi2809 — Thu, 04 Apr 2024 16:07:11 GMT

@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="*"

Re: How to compare two fields values counts ?

ITWhisperer — Thu, 04 Apr 2024 17:44:49 GMT

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)

Re: How to compare two fields values counts ?

karthi2809 — Mon, 08 Apr 2024 12:01:49 GMT

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)

Re: How to compare two fields values counts ?

ITWhisperer — Mon, 08 Apr 2024 12:58:31 GMT

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

Re: How to compare two fields values counts ?

karthi2809 — Mon, 08 Apr 2024 15:53:31 GMT

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)

Re: How to compare two fields values counts ?

ITWhisperer — Mon, 08 Apr 2024 15:10:50 GMT

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>

Re: How to compare two fields values counts ?

karthi2809 — Wed, 10 Apr 2024 11:57:51 GMT

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.

Re: How to compare two fields values counts ?

ITWhisperer — Wed, 10 Apr 2024 12:22:01 GMT

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

Re: How to compare two fields values counts ?

karthi2809 — Wed, 10 Apr 2024 12:48:13 GMT

@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

Re: How to compare two fields values counts ?

ITWhisperer — Wed, 10 Apr 2024 13:28:06 GMT

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>

Re: How to compare two fields values counts ?

Gr0und_Z3r0 — Wed, 10 Apr 2024 14:19:28 GMT

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