Creating a Chart that shows count of Reported ticket and Resolved ticket by week

[synastraa]

Hi ,

I am trying to come up with a chart that looks like this. The chart would consist of tickets logged and resolved in a weekly manner.

So far, I have tried the code below which allows me to get the results for INC tickets logged by reported time field. However I am unsure on how to continue so that I am able to get the results for INC Tickets Resolved by resolved time field. Could anyone advise on how I am able to get the results I am looking for? Thanks
|eval resolved=strftime(Last_Resolved_Date, "%Y-%U")|eval reported=strftime(Reported_Date, "%Y-%U")| stats count as "INC Tickets Logged" by reported

[synastraa · Accepted solution]

This is the solution i found for my question by using the appendcols function.

|dedup Incident_Number|table Incident_Number Reported_Date _time
          |eval reported=strftime(Reported_Date, "%Y-%W")
          |eval _time=strftime(Reported_Date , "%Y-%W")
          |stats count(reported) as Reported_Ticket by _time
          | appendcols
          [ search 
        |dedup Incident_Number|table Incident_Number Last_Resolved_Date _time 
              |eval resolved=strftime(Last_Resolved_Date, "%Y-%W") 
          |eval _time=strftime(Last_Resolved_Date , "%Y-%W")
          |stats count(resolved) as Resolved_Ticket by _time
          ]

[sumanssah]

Try

| timechart span=1w count as "INC Tickets Logged" by reported

[synastraa]

Hi if i want to add the results of INC Tickets Resolved by resolved into this search , how can i do it? thanks

[gouravdashtcs]

Hello Synastraa,

Kindly find the code which I have prepared for you for Last_Resolved_Date for INC's.
host=abc
|table Last_Resolved_Date, INC
|eval week_no=strftime(Last_Resolved_Date, "%W")
|stats earliest(Last_Resolved_Date) as first_week_date, latest(Last_Resolved_Date) as last_week_date by week_no
|convert timeformat="%d-%m-%Y" ctime(first_week_date) ctime(last_week_date)
|eval Week = first_time. " To " .last_time
|stats count(INC) as INC_Count by Week

The same thing you can do for Reported_Date as well.
Hope this helps.

[synastraa]

HI gouravdashtcs,

I have tried the code above but my results display were " No Results Found"

Not sure if its regarding the format my Last_Resolved_Date is in , but here is the format of my Last_Resolved_Date="1557384854"

index=* |dedup Incident_Number|table Last_Resolved_Date Incident_Number|eval week_no=strftime(Last_Resolved_Date, "%W") |stats earliest(Last_Resolved_Date) as first_week_date, latest(Last_Resolved_Date) as last_week_date by week_no |convert timeformat="%d-%m-%Y" ctime(first_week_date) ctime(last_week_date) |eval Week = first_time. " To " .last_time |stats count(Incident_Number) as INC_Count by Week

Thanks!

[gouravdashtcs]

Hello Synastraa,

Apologies for the type which I have done in my query.

index=* 
|dedup Incident_Number
|table Last_Resolved_Date Incident_Number
|eval week_no=strftime(Last_Resolved_Date, "%W") 
|stats earliest(Last_Resolved_Date) as first_week_date, latest(Last_Resolved_Date) as last_week_date by week_no 
|convert timeformat="%d-%m-%Y" ctime(first_week_date) ctime(last_week_date) 
|eval Week = first_week_date. " To " .last_week_date 
|stats count(Incident_Number) as INC_Count by Week

This might fetch you results.

Also for the heads up, the format of the Last_Resolved_Date which you getting is known as Epoch date which is also known as unix timeformat. It is always recommended to use unix time for any operations if we want to do any operations with Time in Splunk.

[synastraa]

Hi gouravdashtcs ,

Thanks for the help!

I have tried the code above and found out that the stats earliest didn't produce any results when i ran a table for it. Hence I am not fetching any results currently. Am trying to work on it to see what is wrong. Would appreciate your help on this! Thanks.

 |stats earliest(Last_Resolved_Date) as first_week_date, latest(Last_Resolved_Date) as last_week_date by week_no 

I have tried using first and last command instead , but the results fetched were all 1.

     |stats first(Last_Resolved_Date) as first_week_date, last(Last_Resolved_Date) as last_week_date by week_no 

However, I would not need the date to be display in that format. Just showing the week and year is enough. My trouble now is on how I could display both the INC report and INC resolved results together . I would like to count the INC_reported by week_no while INC_Resolved by week_no2. I have also realise that when i try to do 2 stats count with count(Incident_Number) only 1 column of value would show up.

    index=* 
     |dedup Incident_Number
     |table Last_Resolved_Date Incident_Number Reported_Date
     |sort -Last_Resolved_Date
     |sort -Reported_Date
     |eval week_no=strftime(Last_Resolved_Date, "%Y-%W") 
     |eval week_no2=strftime(Reported_Date, "%Y-%W")
     |stats count(Incident_Number) as INC_Resolved, count as INC_Reported by week_no