Splunk Search
Highlighted

How to use Timechart count by a subsearch field

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
Highlighted

Re: How to use Timechart count by a subsearch field

Legend

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

0 Karma
Highlighted

Re: How to use Timechart count by a subsearch field

Engager

I do get this:

alt text

If I use usenull it results on nothng.

0 Karma
Highlighted

Re: How to use Timechart count by a subsearch field

Legend

Sorry i don't understand Your search:
in the first append: you missed another append or you repeat two times "index=indexcbopt"? 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=indexcbopt | eval CNPJPDV=CNPJ."-".PDVPT| search index=indexcbopt [search index=indexcbo "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
Highlighted

Re: How to use Timechart count by a subsearch field

Engager

Let me explain it.

I have 2 indexes:
a) indexcbopt
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 indexcbopt 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
Highlighted

Re: How to use Timechart count by a subsearch field

Legend

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
Highlighted

Re: How to use Timechart count by a subsearch field

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
Highlighted

Re: How to use Timechart count by a subsearch field

Legend

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=indexcbo "Scope State: 65280"
| eval CNPJPDV= CNPJ."-".PDV
| appendcols [ search
index=index
cbopt "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=indexcbopt "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
Highlighted

Re: How to use Timechart count by a subsearch field

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=indexcbopt 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
Highlighted

Re: How to use Timechart count by a subsearch field

Engager

Let me explain it.

I have 2 indexes:
a) indexcbopt
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 indexcbopt 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