Dashboards & Visualizations

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

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

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

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

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

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

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