Query changes dynamically with selected index names?

[ilyazs]

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
....
....

[ilyazs]

Thanks.
I got the list of project_names

[stephane_cyrill · Accepted solution]

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......

[ilyazs]

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, .....

[stephane_cyrill]

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.

[ilyazs]

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....

[ilyazs]

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, .....

[masonmorales]

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]

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)

[masonmorales]

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?

[chimell]

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]

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)

[stephane_cyrill]

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]

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)

[gyslainlatsa]

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>

[ilyazs]

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.