scatter plot

[mm12]

Hi,

I have the below query, I need the scatter point visualozation for this. time on the x axis and the count on the y axis. How to achieve this.

|inputlookup hsbc_es_pr_mapping.csv | eval "Configuration Item" = lower('Configuration Item') | lookup hsbc_dc_app_eim_lookup_eim_basic_extract.csv hostname as "Configuration Item" OUTPUT IT_SERVICE | search Status = Open | fields "Problem Number" IT_SERVICE | stats count as "Count of PR's" by IT_SERVICE | sort 10 - "Count of PR's"

Thanks

[ITWhisperer]

Since you don't appear to have a time element in your search, and even if you did, it would have been removed by the stats command (since it isn't in the by clause), the first thing you need to do is define a search that will provide this data. When you have sorted that out, the x-axis (and indeed the y-axis) of a scatter chart need numeric fields and since _time is represented as a string in visualisations, you are going to need a different way to represent the passage of time, e.g. renaming _time as time so you keep the epoch value, or perhaps subtracting the minimum time from all the other time values.

[mm12]

@ITWhisperer - Thanks for the reply . I have modified the query little but in the x axis I am getting values like 

100,000,000 200,000,000 300,000,000 etc. How to overcome this

|inputlookup hsbc_es_pr_mapping.csv | eval "Configuration Item" = lower('Configuration Item') | rename "Open Date/Time(UTC/GMT)" as Open_Date | lookup hsbc_dc_app_eim_lookup_eim_basic_extract.csv hostname as "Configuration Item" OUTPUT IT_SERVICE | search Status = Open | stats count as "Count of PR's" by Open_Date IT_SERVICE | sort 10 - "Count of PR's" | eval Open_Time_timestamp =strptime(Open_Date, "%d %b %y %l:%M:%S")| table IT_SERVICE Open_Time_timestamp "Count of PR's"

Thanks

[ITWhisperer]

You could finish the search by subtracting the earliest time, and then perhaps converting to minutes (since earliest). Or perhaps dividing again by 60 to get hours. Or you could subtract from now() to find how far back in the past the timestamp is. Whatever makes sense for you.

| eventstats min(Open_Time_timestamp) as beginning
| eval Open_Time_timestamp = Open_Time_timestamp - beginning
| eval Open_Time_timestamp = floor(Open_Time_timestamp / 60)
| table IT_SERVICE Open_Time_timestamp "Count of PR's"