topic Re: How to use Timechart count by a subsearch field in Splunk Search

How to use Timechart count by a subsearch field

rafasalo — Sat, 24 Sep 2016 11:29:22 GMT

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,

Re: How to use Timechart count by a subsearch field

sundareshr — Sat, 24 Sep 2016 12:47:28 GMT

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

Re: How to use Timechart count by a subsearch field

rafasalo — Sat, 24 Sep 2016 13:35:11 GMT

I do get this:

If I use usenull it results on nothng.

Re: How to use Timechart count by a subsearch field

gcusello — Tue, 29 Sep 2020 11:05:41 GMT

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

Re: How to use Timechart count by a subsearch field

somesoni2 — Tue, 29 Sep 2020 11:09:03 GMT

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

Re: How to use Timechart count by a subsearch field

rafasalo — Tue, 29 Sep 2020 11:05:45 GMT

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!

Re: How to use Timechart count by a subsearch field

rafasalo — Tue, 29 Sep 2020 11:05:47 GMT

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!

Re: How to use Timechart count by a subsearch field

sundareshr — Sat, 24 Sep 2016 21:08:15 GMT

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

Re: How to use Timechart count by a subsearch field

rafasalo — Tue, 29 Sep 2020 11:05:52 GMT

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?

Re: How to use Timechart count by a subsearch field

gcusello — Sun, 25 Sep 2016 05:53:20 GMT

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

Re: How to use Timechart count by a subsearch field

rafasalo — Mon, 26 Sep 2016 10:28:58 GMT

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:

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

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

Re: How to use Timechart count by a subsearch field

gcusello — Tue, 29 Sep 2020 11:06:17 GMT

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