Dashboards & Visualizations

How do you chart two searches with separate time ranges on the same chart?

johnward4
Communicator

I'm trying to chart open tickets (using a time range of "All-time" and resolved tickets by user for the current month. I've been able to chart the two fields data in the same chart but am looking for help on setting different time ranges for the two searches or fields.

For example: show open tickets using an all-time time range to show all open tickets regardless of month & total tickets resolved within the selected month from the dropdown.

This is the current query I'm using but my open ticket numbers are not accurate as it's only showing opened tickets for the selected month.

index=test sourcetype="test*" User=* Group="HelpDesk"
 | dedup Tickets
 | eval State=if(Closed!="0" OR Status="Closed" OR Status="*Reject*" OR Status="Abort*","Resolved","Open")
| eval Time=strftime(_time, "%m/%d/%Y %I:%M:%S %p")
| rex field=Time "(?<date_month>\d+)/"
| rex field=Time "(?<date_year>\d{4})"
| lookup datemonth.csv date_month OUTPUT datemonth 
| search datemonth="August" date_year=2018"        
 | chart count by User State



**** The datemonth and date_year fields are populated by dropdown tokens in the dashboard
0 Karma

horsefez
SplunkTrust
SplunkTrust

Hi @johnward4,

how about using a subsearch and an append command for that?

 index=test sourcetype="test*" User=* Group="HelpDesk"
  | dedup Tickets
  | eval State=if(Closed!="0" OR Status="Closed" OR Status="*Reject*" OR Status="Abort*","Resolved","Open")
 | eval Time=strftime(_time, "%m/%d/%Y %I:%M:%S %p")
 | rex field=Time "(?<date_month>\d+)/"
 | rex field=Time "(?<date_year>\d{4})"
 | append [| lookup datemonth.csv date_month OUTPUT datemonth 
 | search datemonth="August" date_year=2018"]    
  | chart count by User State
0 Karma

johnward4
Communicator

I think that may be going down the right road but my results are showing Resolved Tickets by User using the "all time" time range using the query above and I'm trying to query only open tickets by user using "all time" but resolved tickets by user using the datemonth and date_year tokens

 index=test sourcetype="test*" User=* Group="HelpDesk"
   | dedup Tickets
   | eval State=if(Closed!="0" OR Status="Closed" OR Status="*Reject*" OR Status="Abort*","Resolved","Open")
  | eval Time=strftime(_time, "%m/%d/%Y %I:%M:%S %p")
  | rex field=Time "(?<date_month>\d+)/"
  | rex field=Time "(?<date_year>\d{4})"
  | append [| lookup datemonth.csv date_month OUTPUT datemonth 
  | search datemonth=$Month$ date_year=$Year$]    
   | chart count by User State
0 Karma

johnward4
Communicator

I've tested the following query and it looks like it's matching my ticket counts for open and resolved tickets. Any tips of how this can be written from efficiently would be helpful

| multisearch 
    [search index=test sourcetype=testUser=* HierarchyLevel=* GroupName="HelpDesk" 
    | eval State=if(Closed!="0" OR Status="Closed" OR Status="*Reject*" OR Status="Abort*","Resolved","Open") 
    | search State="Open"]
    [ search index=test sourcetype=test User=* GroupName="HelpDesk" 
    | eval State=if(Closed!="0" OR Status="Closed" OR Status="*Reject*" OR Status="Abort*","Resolved","Open") 
    | search State="Resolved"]
| dedup Tickets
| eval Time=strftime(_time, "%m/%d/%Y %I:%M:%S %p")
| rex field=Time "(?<date_month>\d+)/"
| rex field=Time "(?<date_year>\d{4})"
| append [| lookup datemonth.csv date_month OUTPUT datemonth 
  | search datemonth="August" date_year="2018" State="Resolved"]    
| lookup datemonth.csv date_month OUTPUT datemonth 
| strcat datemonth ", " date_year MonthAndYear
 | chart count by User State
0 Karma

johnward4
Communicator

Unfortunately, this is still not working to show the resolved column for just the month and year I'm passing in by tokens.

0 Karma

johnward4
Communicator

@pyro_wood do you see anything from my recent queries that can be tweaked to get the result I'm looking for? Thank you

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...