Help with charting results from two searches with data populated from a drop-down

[jbouch03]

I have created a dashboard that uses a drop down menu to populate the data for a search using Django bindings. I know that the a single search works, but when I try to append a second search I don't receive the correct data. I receive a 1 for both results and I expect to see over 3000 for both. I want to put it on a chart that show the average per location versus the current event count. I'm assuming that I'm doing something wrong, but I can't figure out what it is. Here is my search query:

sourcetype=serverstatus | lookup locCode2Location locCode OUTPUT location as location | where location="$location$" | bucket _time span=1d | stats count as avgCount by date_mday | stats avg(avgCount) as avgCount | eval avgCount=round(avgCount,0) | eval ReportKey="Average" | append [search sourcetype=serverstatus earliest=-24h@h latest=now | lookup locCode2Location locCode OUTPUT location as location | where location="$location$" | stats count | eval ReportKey="Current"] | chart count by ReportKey

The main search runs on a 30 day search, and the sub search should run at a 24 hour search. The result I get from running the searches individually is approx 3000, but again when I run them together I get 1 for each. Thank you in advance for any assistance you can provide.

[jbouch03 · Accepted solution]

I ended up changing the requirements based on the comment above, and just created a single value with the percent of change from the average instead of trying to chart the information. The search string that I have now is the following:

sourcetype=serverstatus earliest=-7d@d | lookup locCode2Location locCode OUTPUT location as location | where location="$location$" | bucket _time span=1d | stats count as avgCount by date_mday | stats avg(avgCount) as avgCount | eval avgCount=round(avgCount,0) | rename avgCount as Average | appendcols [search sourcetype=serverstatus earliest=-24h@h | lookup locCode2Location locCode OUTPUT location as location | where location="$location$" | stats count as Current]| eval Change=(Current - Average) / Average * 100 | eval Change=round(Change,0) | fields Change

So far the information has been accurate and the search runs surprisingly quickly, even though I thought it would take too long to run. Thanks for all your help.

[somesoni2]

In your first search, your have "| stats avg(avgCount) as avgCount | " which will always return 1 row and in second search your have "| stats count |" which will again return just one row, so with append in between those, you would get 2 rows only, one for each. I am very sure if you run the search individually (with stats you have) it would not return 3000 entries as you said. May be validate the requirement that you have and let us know the same so that we can give you appropriate help.