topic Re: Query changes dynamically with selected index names? in Splunk Search

Query changes dynamically with selected index names?

ilyazs — Mon, 28 Sep 2020 19:37:58 GMT

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.

index1 =xyz and project=proj_n
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
....
....

Re: Query changes dynamically with selected index names?

gyslainlatsa — Mon, 27 Apr 2015 15:07:15 GMT

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>

Re: Query changes dynamically with selected index names?

ilyazs — Mon, 28 Sep 2020 19:38:01 GMT

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.

Re: Query changes dynamically with selected index names?

stephane_cyrill — Mon, 28 Sep 2020 19:38:12 GMT

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

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

Re: Query changes dynamically with selected index names?

chimell — Mon, 27 Apr 2015 22:27:19 GMT

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>

Re: Query changes dynamically with selected index names?

masonmorales — Tue, 28 Apr 2015 04:12:41 GMT

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

Re: Query changes dynamically with selected index names?

ilyazs — Mon, 28 Sep 2020 19:38:19 GMT

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)

Re: Query changes dynamically with selected index names?

ilyazs — Mon, 28 Sep 2020 19:38:22 GMT

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)

Re: Query changes dynamically with selected index names?

ilyazs — Mon, 28 Sep 2020 19:38:24 GMT

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)

Re: Query changes dynamically with selected index names?

ilyazs — Mon, 28 Sep 2020 19:38:27 GMT

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

Re: Query changes dynamically with selected index names?

stephane_cyrill — Tue, 28 Apr 2015 09:27:59 GMT

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.

Re: Query changes dynamically with selected index names?

ilyazs — Mon, 28 Sep 2020 19:38:29 GMT

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

Re: Query changes dynamically with selected index names?

stephane_cyrill — Mon, 28 Sep 2020 19:42:05 GMT

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

Re: Query changes dynamically with selected index names?

ilyazs — Wed, 29 Apr 2015 10:49:12 GMT

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

Re: Query changes dynamically with selected index names?

masonmorales — Wed, 29 Apr 2015 15:06:06 GMT

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?

Re: Query changes dynamically with selected index names?

ilyazs — Wed, 29 Apr 2015 15:24:21 GMT

Thanks.
I got the list of project_names