topic Re: join question in Splunk Search

join question

mark_chuman — Tue, 01 Mar 2016 17:34:14 GMT

here is my search - | dbquery "TQOMA" "SELECT "System", "%busy" FROM TQSTDBO.CPUVMSUM where "System" LIKE '%ntx%'"

basically, this returns a result for each system every sampling period. So, what I have is one line that represents all the samplings. I'd like to have a line for each system, but can't figure out how to do this. I was thinking something like this, but doesn't seem to work.

join System [| dbquery "TQOMA" "SELECT "System", "%busy" FROM TQSTDBO.CPUVMSUM where "System" LIKE '%ntx%'"]

Thanks for any feedback!

Re: join question

somesoni2 — Tue, 01 Mar 2016 17:59:21 GMT

By "what I have is one line that represents all the samplings. I'd like to have a line for each system", do you mean in chart you're getting one line for overall %busy and you want to have a separate line for each System?

Can you provide some sample data as well? I don't think you need join here (join anyways is used to join two result sets, all I see it one single result set).

Re: join question

mark_chuman — Tue, 01 Mar 2016 18:19:27 GMT

I got it.

| dbquery "TQOMA" "SELECT "Time", "System", "%busy" FROM TQSTDBO.CPUVMSUM where "System" LIKE '%ntx%'" | eval _time=Time | timechart avg(%busy) by System

Re: join question

muebel — Tue, 01 Mar 2016 18:25:30 GMT

Hi Mark, I believe you will want to use the dbquery command to get your initial events, and then pipe it to stats values to get the set of %busy by system. Like so:

| dbquery "TQOMA" "SELECT "System", "%busy" FROM TQSTDBO.CPUVMSUM where "System" LIKE '%ntx%'" | stats values(%busy) by System

Or at least, something along those lines. Join is used to make a table with two data sets, joining on a particular common field, which I don't think is exactly what you are after in this case.

Please let me know if this helps!