How to plot values from two reports or saved searches on the same dashboard panel side by side

[johnward4]

I'm trying to see if there's a way to plot the values of two searches in dashboard bar graph panel side by side showing the values by User...

example : search one, I'm displaying Open Tickets by User --- time picker showing total tickets open by user - all-time
search two, displaying Resolved Tickets by User -- time picker showing resolved tickets within a given month

[niketn]

@johnward4, could you please elaborate what is your exact issue? You seem to have two panel already. So is that mock screenshot? Or do you need assistance with something else?

It would be easy for the Community members to assist you if you provide more details like your current Splunk search and exact issue that you are facing!

[johnward4]

Hello,

I'm asking if it's possible to combine the two panels into one. Showing Open Tickets and Resolved Tickets by User each with their respective bar on the graph so in my bar graph panel the display would look like this :

|Open Tkts| |Resolved Tkts| |Open Tkts ||Resolved Tkts| |Open Tkts| |Resolved Tkts|

User 1 User 2 User 3

[niketn]

For us to assist you better you might have to give us the individual queries for above two panels. Theoritically it is possible...

Instead of Open and Close, I have used Splunk's _internal index in the following run anywhere example where log_level ERROR and WARN for each component has been plotted in the same panel.

    index=_internal sourcetype=splunkd log_level!=INFO 
    | chart count by component log_level limit=5 useother=t

Following is the simple XML dashboard code.

<dashboard>
  <label>Two stats in same Bar Chart</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!=INFO 
| chart count by component log_level limit=5 useother=t</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">bar</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="height">400</option>
      </chart>
    </panel>
  </row>
</dashboard>

[johnward4]

Open Tickets Panel query :

| multisearch 
    [search index=test sourcetype="test:tkts" TicketID=* HierarchyLevel=* GroupName=$GroupName$ NOT Closed="1" TechUserName="$Agentname$"]
    [search index=testsourcetype="test:tkts" TicketID=* HierarchyLevel=* GroupName=$GroupName$ NOT Closed="1" ManagerUserName="$Agentname$"
    | eval TechUserName=ManagerUserName]
| dedup TicketID
| stats count values(TicketID) by TechUserName
| rename TechUserName as Agents, "values(TicketID)" as "Ticket ID", count as "Open Tickets"
| table Agents, "Ticket ID", "Open Tickets"
| sort - "Open Tickets"
| fields - "Ticket ID"

Resolved Tickets query :

index=test sourcetype="test:tkts" TechUserName=* HierarchyLevel=* (Closed!="0" OR Status="Closed" OR Status="*Reject*" OR Status="Abort*")
| dedup TicketID
| eval Time=strftime(_time, "%m/%d/%Y %I:%M:%S %p")
| rex field=Time "(?<date_month>\d+)/"
| rex field=Time "(?<date_year>\d{4})"
| eval comma = ", "
| lookup datemonth.csv date_month OUTPUT datemonth 
| strcat datemonth comma date_year MonthAndYear
| search datemonth=$Month$ date_year=$Year$ GroupName=$GroupName$
| chart count(TicketID) as "Resolved Tickets" by TechUserName
| sort - "Resolved Tickets"

[niketn]

@johnward4, any reason why Open Tickets have an additional index testsourcetype? Also if you are looking for Just Open and Close tickets by User, is formatting of date (any other operation not related to Ticket State and User Name) really required?
I know the following could be over simplification, but I would need to know the answers to above question to assist.

index=test sourcetype="test:tkts" TechUserName=* HierarchyLevel=* 
| dedup TicketID
| eval State=if(Closed!="0" OR Status="Closed" OR Status="*Reject*" OR Status="Abort*","Resolved","Open")
| chart count by TechUserName State

[johnward4]

I've edited my open tickets query

index=test sourcetype="test:tkts" TicketID=* HierarchyLevel=* GroupName="Store Systems Help Desk" TechUserName="*" NOT Closed="1"  
| dedup TicketID
| stats count values(TicketID) by TechUserName
| rename TechUserName as Agents, "values(TicketID)" as "Ticket ID", count as "Open Tickets"
| table Agents, "Ticket ID", "Open Tickets"
| sort - "Open Tickets"
| fields - "Ticket ID"

The Resolved Tickets query uses a piped search line to show just the Resolved Tickets for the selected Month and Year. The Dashboard has a dropdown for both month and year and I call that token in the piped search for REsolved Tickets.

[johnward4]

I think that the eval will work great for what I'm trying to do, would I be able to compare two different time ranges... I want to show Open Tickets with a time range of 'All-time' and the Resolved Tickets I'd like to call fields called datemonth and date_year which use a token in the dashboard to allow the user to show data from each month. The example below doesn't work but shows kind of what I'm trying to acomplish

index=test sourcetype="test:tkts" TechUserName=* HierarchyLevel=* GroupName=$GroupName$
 | dedup TicketID
 | 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})"
| eval comma = ", "
| lookup datemonth.csv date_month OUTPUT datemonth 
| strcat datemonth comma date_year MonthAndYear
| append datemonth=$Month$ date_year=$Year$ Status="Resolved"
 | chart count by TechUserName State

[johnward4]

@niketnilay