Getting Data In

Display the count of a field in a multi value field

Alaza
Explorer

I have a CSV with 10 values for the field ABLA.
In my events I have a field ALIAS and I want to display on a dashboard the count of events with the ALIAS matching ABLA by time.

index="events_index"

| append [| inputlookup CSV.csv |stats values(ABLA) as liste]
| where alias IN liste

How can I do that ?

Tags (2)
0 Karma

prabhu77749
Explorer

index="events_index"

| lookup CSV.csv ALIAS | timechart count by ABLA

ALIAS from events
ABLA from CSV
csv file header format
ALIAS ABLA

0 Karma

gcusello
Legend

Hi Alaza,
you should put the csv file in a lookup and then run a search like this:

index="events_index" [ | inputlookup CSV.csv | rename ABLA AS ALIAS | fields ALIAS ]
| ...

In this way tou have filtered events in index="events_index" for each value of field ABLA to use for the following elaborations.
Remember that the result of the subsearch must have the same field name of the main search.

If instead you want to search ABLA values not in ALIAS field but in all the _raw you can run something like this:

index="events_index" [ | inputlookup CSV.csv | rename ABLA AS query | fields query ]
| ...

Bye.
Giuseppe

0 Karma

Alaza
Explorer

Thanks but to be more precise, I want to display on a dashboard the count of events with the ALIAS matching ABLA by time.

Example :
Date ALIAS1 ALIAS2 ALIAS3 ALIAS4 ALIAS5 ALIAS6 ALIAS7 ... ALIAS10
09/03/2018 1 4 0 7 2 1 0 3

0 Karma

gcusello
Legend

Hi Alaza,
yes, using my filter you have only events that match, then you can display them as you want: by time using timechart, or as stats or listing the events.
The important thing is that you filter events and results are only the ones that matches, e.g.;

index="events_index" [ | inputlookup CSV.csv | rename ABLA AS ALIAS | fields ALIAS ]
| timechart count BY ALIAS

Bye.
Giuseppe

0 Karma

Alaza
Explorer

I understand but I want to display the count of event matching for each value of the field ABLA.
If the match result is null I want to display it too.

0 Karma

gcusello
Legend

Hi Alaza,
if you don't need time in your results you could run a search like this:

index="events_index"
| stats count BY ALIAS
| append  [ | inputlookup CSV.csv | dedup ABLA | rename ABLA AS ALIAS | eval count=0 | fields ALIAS count]
| stats sum(count) AS Total BY ALIAS

in this way you have also the null ALIAS.

it's not so easy to add null values to timechart, I'll try tomorrow.

Bye.
Giuseppe

0 Karma

Alaza
Explorer

I see, but yes I need time.

0 Karma

somesoni2
Revered Legend

Extending @cusello's answer,

index="events_index" [ | inputlookup CSV.csv | stats count by ABLA | rename ABLA AS ALIAS | fields ALIAS ]
 | timechart count BY ALIAS
| table _time [| inputlookup CSV.csv | stats values(ABLA) as search | nomv search ]
| fillnull value=0
0 Karma
Get Updates on the Splunk Community!

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...