Need help to combine inputlookup and map search for two sources

[kiragsplunk]

Team,

Need help to build a dashboard .

WH.csv content

XXX

YYY

I want to search in two different sources but wanna use the same variable from inputlookup variable.

existing Query

| inputlookup WH.csv
| table ware_house
| map search="search index=wh source=$ware_house$_WH_OVERVIEW| head 1
| stats list(Routes) AS ROUTE list(source) AS WH | appendcols [ search index=wh source=$ware_house$_WH_SHIPPING | head 5 | stats list(LabelsCreated) AS LabelsCreated by LabelType | stats sum(LabelsCreated) AS SUMMARY ] "

Issue : second search is not getting the variable $ware_house$ so it does not return any result.

As soon as the base search work would like to add it in the Dashboard.

[richgalloway]

How did you verify the token is not getting passed into the map command?

Have you tried using the concatenation operator?

| inputlookup WH.csv
| table ware_house
| map search="search index=wh source=$ware_house$."_WH_OVERVIEW" | head 1
| stats list(Routes) AS ROUTE list(source) AS WH | appendcols [ search index=wh source=$ware_house$."_WH_SHIPPING" | head 5 | stats list(LabelsCreated) AS LabelsCreated by LabelType | stats sum(LabelsCreated) AS SUMMARY ] "

Also, I think the subsearch to appendcols won't work.  The second stats command is trying to add up a multi-value field, which it probably won't do.

[kiragsplunk]

I ran the query manually to validate the second string.. it would be great if I get this fixed in search..

[richgalloway]

Let's break the query up, get the pieces working, then put them together.  Do these two searches produce the expected results?

index=wh [ | inputlookup WH.csv | eval source=ware_house . "_WH_OVERVIEW" | fields source | format ] 
| head 1
| stats list(Routes) AS ROUTE list(source) AS WH by source

index=wh [ | inputlookup WH.csv | eval source=ware_house . "_WH_SHIPPING" | fields source | format ] 
| head 5 
| stats list(LabelsCreated) AS LabelsCreated by LabelType 
| stats sum(LabelsCreated) AS SUMMARY

[kiragsplunk]

I am getting expected results in both search, could you please help me to append it, if I append the results I am getting time  range Error and no results found. 

[richgalloway]

We've verified each subsearch works. Good.
Now the problem is how to combine them. To correlate the events from each subsearch, there must be something in common between them. I see no commonality, but I'm not familiar with the data. Do the subsearches share a field?

[kiragsplunk]

No common in between.. those are two different sources from the same index.  I  don't see any common field in between both search. ( except the index)

[richgalloway]

That's a big stumbling block. The lack of something in common means Splunk has nothing to use to pair up events from each search.

[kiragsplunk]

May I use a python script to run each query separately then combine the results to a csv .

that results can be viewed is Dashboard right? Please advise.

[richgalloway · Accepted solution]

I'm not sure how a python script would merge the results, but I suppose it's possible. The resulting CSV file could then be displayed by a dashboard.

[kiragsplunk]

Thanks Rich,

Could you please recommend any search string to accomplish this. as you said appendcols not working in map.  if I manually assign value (XXX_WH_OVERVIEW & XXX_WH_SHIPPING) works fine. whats the best approach do you recommend?