Archive

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

synastraa
Path Finder

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.
alt text

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
alt text

Tags (1)
0 Karma
1 Solution

synastraa
Path Finder

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
          ]

View solution in original post

0 Karma

synastraa
Path Finder

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
          ]

View solution in original post

0 Karma

sumanssah
Communicator

Try

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

synastraa
Path Finder

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

0 Karma

gouravdashtcs
Observer

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.

0 Karma

synastraa
Path Finder

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!

0 Karma

gouravdashtcs
Observer

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.

0 Karma

synastraa
Path Finder

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
0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!