Splunk Search

How do I edit my search to make columns display running totals?

blues1990
Explorer

My search is:

index=4_ip_sql source=CNVIP101 Priority=3 Quality=192 (Message="*full*" OR Message="*stop*" OR Message="*halt*" OR Message="*fault*") date_wday!="Saturday" date_wday!="Sunday"  | eval date_hour=strftime(_time,"%H") | eval AlarmType=case(like(Message,"%Full%"),"full",like(Message,"%Fault%"),"Fault",like(Message,"%Halt%"),"Halt",like(Message,"%Stop%"),"E-Stop") | dedup EventID   | rex field=SourceName "(?MCP[0-9A-Za-z]*)\s"   | eval Area=case( PLC="MCP04A",case(like(Message,"%Divert 8%"), "East 8")| transaction SourceName startswith="Active=1" endswith="Active=0" maxevents=2  | search duration>0 (date_hour>=8 date_hour<17 OR (date_hour=17 AND date_minute<45)) | eval endTime=_time+duration  | sort 0 Area AlarmType -_time  | delta _time as startToNextStart p=1  | delta endTime as endToNextEnd p=1  | fillnull startToNextStart value=duration  | eval endToNextEnd=if(endToNextEnd>0,1,-endToNextEnd)  | eval startToNextStart=if(startToNextStart>0,duration,-startToNextStart)  | eval duration=if(duration<=startToNextStart,duration,if(endToNextEnd>0,startToNextStart,startToNextStart-endToNextEnd)) | timechart span=15m sum(eval(duration/60)) as "MinutesInAlarm" by Area limit=0 usenull=f useother=f

I imagine I have to use streamstats, but I can't get it to work. Any help would be appreciated. Thanks!

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

index=4_ip_sql source=CNVIP101 Priority=3 Quality=192 (Message="*full*" OR Message="*stop*" OR Message="*halt*" OR Message="*fault*") date_wday!="Saturday" date_wday!="Sunday"  | dedup EventID
| eval date_hour=strftime(_time,"%H") | eval AlarmType=case(like(Message,"%Full%"),"full",like(Message,"%Fault%"),"Fault",like(Message,"%Halt%"),"Halt",like(Message,"%Stop%"),"E-Stop") | rex field=SourceName "(?MCP[0-9A-Za-z]*)\s"   | eval Area=case( PLC="MCP04A",case(like(Message,"%Divert 8%"), "East 8")
| transaction SourceName startswith="Active=1" endswith="Active=0" maxevents=2  | search duration>0 (date_hour>=8 date_hour<17 OR (date_hour=17 AND date_minute<45)) | eval endTime=_time+duration  | sort 0 Area AlarmType -_time  | delta _time as startToNextStart p=1  | delta endTime as endToNextEnd p=1  | fillnull startToNextStart value=duration  | eval endToNextEnd=if(endToNextEnd>0,1,-endToNextEnd)  | eval startToNextStart=if(startToNextStart>0,duration,-startToNextStart)  | eval duration=(if(duration<=startToNextStart,duration,if(endToNextEnd>0,startToNextStart,startToNextStart-endToNextEnd)))/60 | timechart span=15m sum(duration) as "MinutesInAlarm" by Area limit=0 usenull=f useother=f
| streamstats sum(*) as *

View solution in original post

somesoni2
Revered Legend

Give this a try

index=4_ip_sql source=CNVIP101 Priority=3 Quality=192 (Message="*full*" OR Message="*stop*" OR Message="*halt*" OR Message="*fault*") date_wday!="Saturday" date_wday!="Sunday"  | dedup EventID
| eval date_hour=strftime(_time,"%H") | eval AlarmType=case(like(Message,"%Full%"),"full",like(Message,"%Fault%"),"Fault",like(Message,"%Halt%"),"Halt",like(Message,"%Stop%"),"E-Stop") | rex field=SourceName "(?MCP[0-9A-Za-z]*)\s"   | eval Area=case( PLC="MCP04A",case(like(Message,"%Divert 8%"), "East 8")
| transaction SourceName startswith="Active=1" endswith="Active=0" maxevents=2  | search duration>0 (date_hour>=8 date_hour<17 OR (date_hour=17 AND date_minute<45)) | eval endTime=_time+duration  | sort 0 Area AlarmType -_time  | delta _time as startToNextStart p=1  | delta endTime as endToNextEnd p=1  | fillnull startToNextStart value=duration  | eval endToNextEnd=if(endToNextEnd>0,1,-endToNextEnd)  | eval startToNextStart=if(startToNextStart>0,duration,-startToNextStart)  | eval duration=(if(duration<=startToNextStart,duration,if(endToNextEnd>0,startToNextStart,startToNextStart-endToNextEnd)))/60 | timechart span=15m sum(duration) as "MinutesInAlarm" by Area limit=0 usenull=f useother=f
| streamstats sum(*) as *

View solution in original post

blues1990
Explorer

As always, you're the man.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.