Splunk Search

How to use Timechart count by a subsearch field

rafasalo
Engager

Hello,

I'm trying to use "timechart count by" a field from a subsearch. Bellow, my query that is not working.

index=index_cbo "Scope State: 65280" | eval CNPJPDV= CNPJ."-".PDV | append [search index=index_cbo_pt | eval CNPJPDV= CNPJ."-".PDV_PT| search index=index_cbo_pt [search index=index_cbo "Scope State: 65280" | eval CNPJPDV= CNPJ."-".PDV  | table CNPJPDV] | table CNPJPDV PaymentChannelName]| timechart span=5m count by PaymentChannelName

It's resulting in a timechart will NULL column.

Thanks in advance.

Cheers,

rs

0 Karma

sundareshr
Legend

Try this

(index=index_cbo "Scope State: 65280") OR index=index_cbo_pt | eval CNPJPDV= CNPJ."-".PDV_PT | bin span=5m _time | stats values(index) as index count by PaymentChannelName | where mvcount(index)=2
0 Karma

rafasalo
Engager

It doesn't work. I think becase eval CNPJPDV= CNPJ."-".PDV_PT is only possible for index_cbo_pt. For index_cbo, the eval command is eval CNPJPDV= CNPJ."-".PDV. Any ideais?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Could you explain what the query should do for you? Are you looking to plot timechart of count by PaymentChannelName for events in index=index_cbo_pt where CNPJPDV values matches the one from index_cbo? If yes, then try like this

index=index_cbo_pt | eval CNPJPDV= CNPJ."-".PDV_PT| search [search index=index_cbo "Scope State: 65280" | eval CNPJPDV= CNPJ."-".PDV  | stats count by CNPJPDV | table CNPJPDV] |  timechart span=5m count by PaymentChannelName
0 Karma

rafasalo
Engager

Let me explain it.

I have 2 indexes:
a) index_cbo_pt
b) index_cbo

In index_cbo there are events with the text "Scope State: 65280". All events with this text has CNPJ and PDV fields, that I concatenate to form CNPJPDV.

In index_cbo_pt there are events that indicates in which channel (PaymentChannelName) the CNPJPDV (that I also need to concatenate) are.

So, I want to see in which channels the events of "Scope State: 65280" are happening. Finally see it along a period of time, this is why I use timechart.

Please, tell me if I wasn't clear.

Thanks!

0 Karma

gcusello
SplunkTrust
SplunkTrust

Sorry i don't understand Your search:
in the first append: you missed another append or you repeat two times "index=index_cbo_pt"? if the second choice you could write your search in a different way.

Every way, if you didn't miss anything in the search, start debugging dividing the two searches and executing timechart command on each one:

  • index=index_cbo "Scope State: 65280" | eval CNPJPDV= CNPJ."-".PDV | timechart span=5m count by PaymentChannelName
  • index=index_cbo_pt | eval CNPJPDV=CNPJ."-".PDV_PT| search index=index_cbo_pt [search index=index_cbo "Scope State: 65280" | eval CNPJPDV= CNPJ."-".PDV | table CNPJPDV | table CNPJPDV PaymentChannelName]| timechart span=5m count by PaymentChannelName

In this way you can find if each search has events and all the required fields (CNPJPDV, PaymentChannelName), maybe there is a problem.
Bye.
Giuseppe

0 Karma

rafasalo
Engager

Let me explain it.

I have 2 indexes:
a) index_cbo_pt
b) index_cbo

In index_cbo there are events with the text "Scope State: 65280". All events with this text has CNPJ and PDV fields, that I concatenate to form CNPJPDV.

In index_cbo_pt there are events that indicates in which channel (PaymentChannelName) the CNPJPDV (that I also need to concatenate) are.

So, I want to see in which channels the events of "Scope State: 65280" are happening. Finally see it along a period of time, this is why I use timechart.

Please, tell me if I wasn't clear.

Thanks!

0 Karma

gcusello
SplunkTrust
SplunkTrust

try this

index=index_cbo_pt "Scope State: 65280" 
| eval CNPJPDV= CNPJ."-".PDV 
| join CNPJPDV [ search 
     index=index_cbo 
     | eval CNPJPDV= CNPJ."-".PDV 
      ] 
| timechart span=5m count by PaymentChannelName

Bye.
Giuseppe

0 Karma

rafasalo
Engager

Giuseppe,

I've changed a little bit your query (see the new one bellow) and it worked.

index=index_cbo "Scope State: 65280" 
 | eval CNPJPDV= CNPJ."-".PDV 
 | join CNPJPDV [ search 
      index=index_cbo_pt 
      | eval CNPJPDV= CNPJ."-".PDV_PT 
       ] 
 | timechart span=1h count by PaymentChannelName

Basically what I've changed is that the event "Scope State: 65280" happens in index_cbo.

The new problem is that by executing this query I don't get all the events. How do I now that? See below the chart of the new query:

alt text

Now, if I run just part of the new query: index=index_cbo_pt "Scope State: 65280" | timechart span=5m count, I get this:

alt text

It seems that using JOIN I don't get all the results. Do you have an alternative for the same query using a subsearch or append?

thanks

0 Karma

gcusello
SplunkTrust
SplunkTrust

Using join you take only events where there is CNPJPDV in both of them.
In other words: you use the second search to find the PaymentChannelName field and after you group the results of the first search by PaymentChannelName

You could also use appendcols instead join, but probably you'll have the same result
index=index_cbo "Scope State: 65280"
| eval CNPJPDV= CNPJ."-".PDV
| appendcols [ search
index=index_cbo_pt "Scope State: 65280"
| eval CNPJPDV= CNPJ."-".PDV_PT
]
| timechart span=1h count by PaymentChannelName

if the list of PaymentChannelName isn't so variable you also could extract them in a lookup by a night batch (it's surely more quick!)
index=index_cbo_pt "Scope State: 65280" | eval CNPJPDV= CNPJ."-".PDV_PT | fields CNPJPDV PaymentChannelName
and then use the lookup to give the correct PaymentChannelName to every event ans so make your stats.
index=index_cbo "Scope State: 65280" | eval CNPJPDV= CNPJ."-".PDV | lookup yourlookup CNPJPDV OUTPUT PaymentChannelName | timechart span=1h count by PaymentChannelName

Bye.
Giuseppe

0 Karma

sundareshr
Legend

Are you getting any valid results? If you are, add usenull=f to your timechart.

0 Karma

rafasalo
Engager

I do get this:

alt text

If I use usenull it results on nothng.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...