How can I chart queries over time?

[zacksoft]

I have a query that end with

| table jra_conn bam_conn bib_conn

jra_conn, bam_conn, bib_conn are not Splunk fields. They are custom fields created using eval statements.

Example : jra_conns have values like

34
12
22
45
etc...

I want to be able to chart these values over time (individually for all three *_conns .)
But | timechart span=1m values(jra_conn)

won't work for me. I am thinking if I convert jra_conn to a multivalue field then may be timechart or something similar will work.
Any suggestions?

[somesoni2 · Accepted solution]

Give this a try (simplified the custom field extraction)

host="yelowpark.gamers.com" source="/apps/absan/jra/logs/access_log.data"
| eval headers=split(_raw,";") 
| table _time headers
| eval b=mvindex(headers,2)
| rex field=b "Current Conns: (?<Total_conn>.*)" 
| eval c=mvindex(headers,3)
| rex field=c "Current Conns: (?<jra_conn>.*)" 
| eval d=mvindex(headers,4)
| rex field=d "Current Conns: (?<bam_conn>.*)" 
| eval e=mvindex(headers,5)
| rex field=e "Current Conns: (?<bib_conn>.*)"  
| timechart avg(*_conn) as Avg_*_conn

[zacksoft]

Absolutely brilliant @somesoni2 ♦
Thank you very very much.
You simplified the extraction and timechart works too.

[somesoni2]

Please share your current full search.

[zacksoft]

| host="yelowpark.gamers.com" source="/apps/absan/jra/logs/access_log.data"
| eval headers=split(_raw,";")
| eval a=mvindex(headers,1)
| eval b=mvindex(headers,2)
| eval b_pipe=split(b,"|")
| eval b1=mvindex(b_pipe,3)
| eval b_colon=split(b1,":") | eval Total_Connection=mvindex(b_colon,1)

| eval c=mvindex(headers,3)
| eval c_pipe=split(c,"|")
| eval c1=mvindex(c_pipe,1) | eval c1_colon=split(c1,":") | eval c_target=mvindex(c1_colon,1)
| eval c2=mvindex(c_pipe,2) | eval c2_colon=split(c2,":") | eval c_status=mvindex(c2_colon,1)
| eval c3=mvindex(c_pipe,3) | eval c3_colon=split(c3,":") | eval jra_conn=mvindex(c3_colon,1)

| eval d=mvindex(headers,4)
| eval d_pipe=split(d,"|")
| eval d1=mvindex(d_pipe,1) | eval d1_colon=split(d1,":") | eval d_target=mvindex(d1_colon,1)
| eval d2=mvindex(d_pipe,2) | eval d2_colon=split(d2,":") | eval d_status=mvindex(d2_colon,1)
| eval d3=mvindex(d_pipe,3) | eval d3_colon=split(d3,":") | eval bam_conn=mvindex(d3_colon,1)

| eval e=mvindex(headers,5)
| eval e_pipe=split(e,"|")
| eval e1=mvindex(e_pipe,1) | eval e1_colon=split(e1,":") | eval e_target=mvindex(e1_colon,1)
| eval e2=mvindex(e_pipe,2) | eval e2_colon=split(e2,":") | eval e_status=mvindex(e2_colon,1)
| eval e3=mvindex(e_pipe,3) | eval e3_colon=split(e3,":") | eval bib_conn=mvindex(e3_colon,1)

Sample event below:
Type: VIP Status | Target: /Common/lera.mayhem.com-80-int-llb | Status: The children pool member(s) either don't have service checking enabled, or service check results are not available yet | Current Conns: ;
Type: VIP Status | Target: /Common/lera.mayhem.com-334-int-llb | Status: The virtual server is available | Current Conns: ;
Type: Pool Status | Target: /Common/lera.mayhem.com-334-int-llb | Status: The pool is available | Current Conns: 9069;
Type: Pool Member Status | Target: 20.239.118.200:8004 | Status: Forced down | Current Conns: 0;
Type: Pool Member Status | Target: 20.239.128.245:8004 | Status: Pool member is available | Current Conns: 566;
Type: Pool Member Status | Target: 20.239.189.243:8334 | Status: Pool member is available | Current Conns: 223;
Type: Pool Member Status | Target: 20.239.289.44:8004 | Status: Pool member is available | Current Conns: 113;
Type: Pool Member Status | Target: 20.239.209.11:8004 | Status: Pool member is available | Current Conns: 334;
Type: Pool Member Status | Target: 20.239.209.22:8004 | Status: Pool member is available | Current Conns: 114;

[DalJeanis]

It depends on what you are trying to chart. If you want to see individual dots for each of the connection speeds at any given time, then use a scatterplot instead of a timechart. If you want to see the average, then use timechart.

[zacksoft]

Thanks @DalJeanis ♦ for the suggestion.
I think (not sure) the timechart or scatterplot works when when we deal with splunk fields. Mine are custom fields generated as a result of eval statements .

I tried all the plots, But the visualization won't generate.
I think the fields(jra_conn etc.) needs to be converted to multivalued and then splitted or something similar before we make it work.

[DalJeanis]

I suspect you want...

| table _time jra_conn bam_conn bib_conn
| timechart  span=1m avg(jra_conn) as jra_conn avg(bam_conn) as bam_conn avg(bib_conn) as bib_conn 

[zacksoft]

Also when tried this
| xyseries _time jra_conn

Error generates and says "Error in 'xyseries' command: At least one data field must be specified"

[zacksoft]

tried it already. won't work. 😞

[DalJeanis]

or you can use max() or min()

[poete]

Hello,

how can you expect a timechart if the last line of the search is a table not containing _time?

To me, it should end with
| table _time jra_conn bam_conn bib_conn

[zacksoft]

Tried the following . Still I won't get a visualization
| table _time jra_conn
| stats values(jra_conn) by _time

[zacksoft]

I tried that.
| table _time jra_conn
still there won't come any visualization.
added | timechart span=1m values(jra_conn)
still no solution.