topic Did some math on a chart but need to take results to a timechart in Splunk Search

Did some math on a chart but need to take results to a timechart

nqjpm — Tue, 05 Feb 2019 20:40:32 GMT

Have a working query, but the boss has now asked me to timechart for SuccessRateByPlatformPCT per week and I am having a bear of a time getting it from here to there. Any insights appreciated.

    index=foo
    | eval date=strftime(_time, "%x") 
    | eval ComputerPlatformString=len(AssetID) 
    | eval ComputerPlatform=case(ComputerPlatformString=0, "Unknown/Multiple_LVDI_and_Desktop", ComputerPlatformString=3, "Laptop/Desktop", ComputerPlatformString=8, "Laptop/Desktop", ComputerPlatformString=9, "LVDI", ComputerPlatformString=10, "PSI/VSI", ComputerPlatformString=11, "AppSphere", ComputerPlatformString=12, "AppSphere", ComputerPlatformString=13, "AppSphere", ComputerPlatformString=14, "WorkStation", ComputerPlatformString=15, "VSI", ComputerPlatformString=18, "VDesk Terminal", ComputerPlatformString=20, "VDesk") 
    | where ComputerPlatform="AppSphere" OR ComputerPlatform="LVDI" OR ComputerPlatform="Laptop/Desktop" OR ComputerPlatform="VDesk" 
    |  transaction UniqueIdentifier 
    | chart dc(UniqueIdentifier) by ComputerPlatform Reply
    | eval SuccessRateByPlatformPCT = round((RESOLVED) / (RESOLVED + NOTRESOLVED)*100, 2) 
    | eventstats sum(RESOLVED) as TOTALRESOLVED , sum(NOTRESOLVED) AS TOTALNOTRESOLVED, sum(NORESPONSE) AS TOTALNORESPONSE 
    | addtotals  NORESPONSE NOTRESOLVED RESOLVED fieldname=TotalUses 
    | addcoltotals NORESPONSE NOTRESOLVED RESOLVED TotalUses labelfield=ComputerPlatform label=Total
    | table ComputerPlatform NORESPONSE NOTRESOLVED RESOLVED SuccessRateByPlatform TotalUses

resulting chart 
| ComputerPlatform | NORESPONSE | NOTRESOLVED | RESOLVED | SuccessRateByPlatformPCT | TotalUses
| LVDI          |   1       |      2      |     8    |             80.00        |     11
| Laptop/Desktop   |    3       |      3      |     7    |             70.00        |     13

Re: Did some math on a chart but need to take results to a timechart

chrisyounger — Tue, 05 Feb 2019 20:45:00 GMT

We will be able to help you better if you can provide a couple of lines of sample data.

Re: Did some math on a chart but need to take results to a timechart

somesoni2 — Tue, 05 Feb 2019 20:59:44 GMT

Give this a try. If you want to calculate success rate on more granular spans, change the span in bucket command to 1d or whatever you need.

index=foo
     | eval date=strftime(_time, "%x") 
     | eval ComputerPlatformString=len(AssetID) 
     | eval ComputerPlatform=case(ComputerPlatformString=0, "Unknown/Multiple_LVDI_and_Desktop", ComputerPlatformString=3, "Laptop/Desktop", ComputerPlatformString=8, "Laptop/Desktop", ComputerPlatformString=9, "LVDI", ComputerPlatformString=10, "PSI/VSI", ComputerPlatformString=11, "AppSphere", ComputerPlatformString=12, "AppSphere", ComputerPlatformString=13, "AppSphere", ComputerPlatformString=14, "WorkStation", ComputerPlatformString=15, "VSI", ComputerPlatformString=18, "VDesk Terminal", ComputerPlatformString=20, "VDesk") 
     | where ComputerPlatform="AppSphere" OR ComputerPlatform="LVDI" OR ComputerPlatform="Laptop/Desktop" OR ComputerPlatform="VDesk" 
     |  transaction UniqueIdentifier 
     | bucket span=1w _time
     | chart dc(UniqueIdentifier) by _time ComputerPlatform Reply
     | eval SuccessRateByPlatformPCT = round((RESOLVED) / (RESOLVED + NOTRESOLVED)*100, 2) 
     | timechart span=1w avg(SuccessRateByPlatformPCT) by ComputerPlatform

Re: Did some math on a chart but need to take results to a timechart

nqjpm — Tue, 05 Feb 2019 21:01:33 GMT

I'm unfortunately limited by our security policy. I cant be very specific.

Re: Did some math on a chart but need to take results to a timechart

nqjpm — Tue, 05 Feb 2019 21:07:28 GMT

When I use
| chart dc(UniqueIdentifier) by _time ComputerPlatform Reply
It now gives me an error "Error in 'chart' command: The argument 'Reply' is invalid."
Not sure why _time kills the search.

Re: Did some math on a chart but need to take results to a timechart

woodcock — Fri, 08 Feb 2019 20:59:26 GMT

Try this:

index=foo
| eval ComputerPlatformString=len(AssetID) 
| eval ComputerPlatform=case(ComputerPlatformString=0, "Unknown/Multiple_LVDI_and_Desktop", ComputerPlatformString=1, "Laptop/Desktop", ComputerPlatformString=2, "Laptop/Desktop", ComputerPlatformString=3, "LVDI", ComputerPlatformString=10, "PSI/VSI", ComputerPlatformString=11, "AppSphere", ComputerPlatformString=12, "AppSphere", ComputerPlatformString=13, "AppSphere", ComputerPlatformString=14, "WorkStation", ComputerPlatformString=15, "VSI", ComputerPlatformString=18, "VDesk Terminal", ComputerPlatformString=20, "VDesk")
| where ComputerPlatform="AppSphere" OR ComputerPlatform="LVDI" OR ComputerPlatform="Laptop/Desktop" OR ComputerPlatform="VDesk"
| bin _time span=1w
| stats values(*) AS * BY UniqueIdentifier _time
| eval ComputerPlatform = _time . "@" . ComputerPlatform
| chart dc(UniqueIdentifier) AS UniqueIdentifieCountr BY ComputerPlatform Reply
| rex field=ComputerPlatform "^(?<_time>\d+)@(?<ComputerPlatform>.*)$"
| eval SuccessRateByPlatformPCT = round((RESOLVED) / (RESOLVED + NOTRESOLVED)*100, 2) 
| eventstats sum(RESOLVED) AS TOTALRESOLVED , sum(NOTRESOLVED) AS TOTALNOTRESOLVED, sum(NORESPONSE) AS TOTALNORESPONSE 
| addtotals NORESPONSE NOTRESOLVED RESOLVED fieldname=TotalUses 
| addcoltotals NORESPONSE NOTRESOLVED RESOLVED TotalUses labelfield=ComputerPlatform label=Total 
| table _time ComputerPlatform NORESPONSE NOTRESOLVED RESOLVED SuccessRateByPlatformPCT TotalUses