Splunk Search
Highlighted

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

Explorer

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
0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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
0 Karma
Highlighted

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

Explorer

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.

0 Karma
Highlighted

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

Esteemed Legend

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
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.