Splunk Search

Query changes dynamically with selected index names?

ilyazs
Explorer

I am trying to fetch the project names from different logs which has different field name and it is depend on index names, and not able to find out correct way to do it.

  1. index1 =xyz and project=proj_n
  2. index2=yzx and project=project_n

index=xyz sourcetype=aaa | eval project_names=if(index="xyz","proj_n=""","project_n=""") | chart count(host) as hostnames by project_names

I used above query but not getting correct output.

Required Output:
project_names
abc1
bcd2
cde3
efg4
....
....

0 Karma
1 Solution

stephane_cyrill
Builder

I can see that your data are from 2 different log files not from 2 different indexes. So i guest what you want is: project names from both log files for a given index and for a given period.

1- the query :

source=LogFile1 OR source=LogFile-2 index=$index_token$ |eval projetN=coalesce(proj_n,project_n)|eval project_name=case("$index_token$"="abc","proj_n","$index_token$"="bbc","project_n","$index_token$"="*","projetN")|
chart count(host) as hostnames by project_name

2-to control the time periode,create a time picker. if your using splunk 6.2, put the above search in the tag QUERY and under this put $time.earliest$ and $time.latest$ in the tag EARLIEST and LATEST.

3-so finally you will have an input for index, an input for time picker. if you set them very well it will be ok for you.

good luck......

View solution in original post

ilyazs
Explorer

Thanks.
I got the list of project_names

0 Karma

stephane_cyrill
Builder

I can see that your data are from 2 different log files not from 2 different indexes. So i guest what you want is: project names from both log files for a given index and for a given period.

1- the query :

source=LogFile1 OR source=LogFile-2 index=$index_token$ |eval projetN=coalesce(proj_n,project_n)|eval project_name=case("$index_token$"="abc","proj_n","$index_token$"="bbc","project_n","$index_token$"="*","projetN")|
chart count(host) as hostnames by project_name

2-to control the time periode,create a time picker. if your using splunk 6.2, put the above search in the tag QUERY and under this put $time.earliest$ and $time.latest$ in the tag EARLIEST and LATEST.

3-so finally you will have an input for index, an input for time picker. if you set them very well it will be ok for you.

good luck......

View solution in original post

ilyazs
Explorer

Output of above query is count of project names, but i want list of names of project_name.

Suppose if I have index=abc, output list should be project_name=xyz, xxx, yzx, ....
if index=bbc, then output list should be project_name=yyy, zzz, bbb, .....

0 Karma

stephane_cyrill
Builder

Can you provide a sample data,I'm sure with that we can understand you better .....

You up there that you need project names for a given period. If the request was working then just add a time picker and tokenize it.

0 Karma

ilyazs
Explorer

Below are the two log files-
LogFile1
index,sourcetype,host,proj_n,entry_post
abc,aaa,10.11.190.12,xyz,text....
abc,aaa,10.11.190.12,xxx,text....
abc,aaa,10.11.190.12,yzx,text....

LogFile-2
index,sourcetype,host,project_n,entry_post
bbc,aaa,10.11.190.12,yyy,text....

bbc,aaa,10.11.190.12,zzz,text....
bbc,aaa,10.11.190.12,bbb,text....

0 Karma

ilyazs
Explorer

I tried using above query to fetch list of project_names from different index_names, project names has different field names for each index.

index=$index_name$ sourcetype=$source_type$ host=$host_name$ | eval Project_N=if($index_name$=="xyz", "project_n= * ", "proj_n= * ") | chart count(host) as hostname by Project_N

I tried using cases, if statement and coalesce function but not getting list of project names.

2 Logfiles for your undestanding -

Below are the two log files-
LogFile1
index,sourcetype,host,proj_n,entry_post
abc,aaa,10.11.190.12,xyz,text....
abc,aaa,10.11.190.12,xxx,text....
abc,aaa,10.11.190.12,yzx,text....

LogFile-2
index,sourcetype,host,project_n,entry_post
bbc,aaa,10.11.190.12,yyy,text....
bbc,aaa,10.11.190.12,zzz,text....
bbc,aaa,10.11.190.12,bbb,text....

Suppose if I have index=abc, output list should be project_name=xyz, xxx, yzx, ....
if index=bbc, then output list should be project_name=yyy, zzz, bbb, .....

0 Karma

masonmorales
Influencer

Assuming it's just two indexes, you could simply do:

index=xyz OR index=yzx 
| rename proj_n as project_names
| eval project_names=if(isnull(project_names), project_n, project_names) 
| table project_names
| dedup project_names

ilyazs
Explorer

Hi Mason,
Thanks for your reply, this is not working
I want list of project names available in given timeperiod. I have to try by project_n=(star) or proj_n=(star) (project_n=star or asterisk)

0 Karma

masonmorales
Influencer

Right, I think the query I posted should do just that though. What exactly is not working? Could you use the job inspector to see where it's failing?

0 Karma

chimell
Motivator

Hi ilyazs
Look at this xml code , it will be help you well , just try it.

 <form>
    <label>Dropdown Form Input Element</label>
    <description>display results by index</description>
     <fieldset autoRun="true" submitButton="false">
    <input type="dropdown" token="index_tok" searchWhenChanged="true">
      <label>Select an index:</label>
      <default>*</default>
      <choice value="*">All</choice>
      <choice value="xyz">xyz</choice>
      <choice value="yzx">yzx</choice>
    </input>
     </fieldset>
    <row>
    <chart>
    <title>Chart of Events for index="$index_tok$" </title>
    <searchString>index=$index_tok$ sourcetype=aaa | eval project_names=if($index_tok$=="xyz","proj_n=""","project_n=""") | chart count(host) as hostnames by project_names</searchString>
    <earliestTime>0</earliestTime>
    <latestTime>now</latestTime>
    <option name="charting.chart">column</option>
    </chart>
    </row>
    </form>

ilyazs
Explorer

Hi Chimell,
Thanks for your reply, this is not working
I want list of project names available in given timeperiod. I have to try by project_n=(star) or proj_n=(star) (project_n=star or asterisk)

0 Karma

stephane_cyrill
Builder

HI try this:
1.this is for all project names in the two index.

index=xyz OR index=yxz sourcetype=aaa ..... |eval project_name=coalesce(proj_n,project_n)|
chart count(host) as hostnames by project_name

  1. Now if you want it for a chosen index,assuming that you are doing it in a dashboard:

a-create a dropdown, populate it with your different Indexes.

b-the query will look like this:

index=$index_token$ source=..... OR sourcetype=.......|eval project_name=if( "$index_token$"=xyz, "project_n","proj_n")|
chart count(host) as hostnames by project_name

NOTE: you can have nested if(...) if you have more than 2 index or you can use case(...)

hope this will help....

ilyazs
Explorer

Hi Stephane,
Thanks for your reply, this is not working
I want list of project names available in given timeperiod. I have to try by project_n=(star) or proj_n=(star) (project_n=star or asterisk)

0 Karma

gyslainlatsa
Motivator

hi
try like this

<form>

  <label>Dropdown Form Input Element</label>

  <description>Events Filtered by index using Dropdown Form Inputs</description>

  <fieldset autoRun="true" submitButton="false">
    <input type="dropdown" token="index" searchWhenChanged="true">
      <label>Select a index:</label>
      <default>*</default>
      <choice value="*">All</choice>
      <populatingSearch fieldForValue="index" fieldForLabel="index" earliest="0" latest="now">
        <![CDATA[index=* OR index=_* | stats count by index]]>
      </populatingSearch>
    </input>
  </fieldset>

  <row>
    <chart>
      <title>Chart of Events for index="$index$" </title>
      <searchString>index=$index$ OR index=_$index$  | chart count(host) as hostnames by project_names</searchString>
      <earliestTime>0</earliestTime>
      <latestTime>now</latestTime>
      <option name="charting.chart">column</option>
    </chart>
  </row>

</form>
0 Karma

ilyazs
Explorer

Thansk Gys,
I tried using your query but no output.

Let me tell you few things
We have project names with different fields names in different index. Suppose if I select index1 (abc) i want project names (fieldname of project names is "proj_n") from index1 list and if I select index2 (xyz) I want project names (fieldname of project name is "proj_names") from index2 list.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!