Hello,
Working on a Splunk Dashboard to pull specific data in return and when exporting to an Excel sheet, it provides the needed data. A breakdown of Per user, per department, avg memory usage per week of the end-users. So when you Export the data to Excel, the information isn’t clean and doesn’t bring down everything, a better break down of Per User, Per Department and taking the avg of that user/machine for a week
Here is the code source so far:
<form>
<label>End User Computing - Desktop Dashboard Clone HJ</label>
<fieldset submitButton="false" autoRun="false">
<input type="dropdown" token="hosttok" searchWhenChanged="true">
<label>Host Selector</label>
<search>
<query>| metadata type=hosts index=perfmon sourcetype="Perfmon:Process" |table host Department |sort host</query>
<earliest>-24@h</earliest>
<latest>now</latest>
</search>
<fieldForLabel>host</fieldForLabel>
<fieldForValue>host</fieldForValue>
<prefix>"</prefix>
<suffix>"</suffix>
<choice value="*">All</choice>
</input>
<input type="time" token="TimerangePicker" searchWhenChanged="true">
<label></label>
<default>
<earliest>-4h@m</earliest>
<latest>now</latest>
</default>
</input>
</fieldset>
<row>
<panel>
<title>Computer Information -Click "HostName" to launch LANDesk remote control via browser</title>
<table>
<search>
<query>index=prd_win_domain_reports earliest=-24h sourcetype=DNS_Records_Info HostName="*"
|eval DeviceName=HostName
|join type=left DeviceName [search index=prd_dbx_ld_compsys sourcetype="dbmon:kv" earliest=-24h DeviceName="*"
|replace "saccap.int/Workstations/*" with "*" in ComputerLocation
|replace "CN=*" with "*" in PrimaryOwner
|replace "*,OU=Accounts,DC=saccap,DC=int" with "*" in PrimaryOwner
|rename ComputerLocation as OU
|eval LastUpdate=strftime(_time, "%Y-%m-%d %I:%M:%p")]
|rename DeviceName as host
|join type=left host [search index=* sourcetype="WMI:MemoryInfo" earliest=-24h host="*"
|dedup host DeviceLocator
|eval DIMM_GB=((Capacity/1024)/1024/1024)
|eventstats sum(DIMM_GB) AS Total by host
|dedup host
|eval Total=round(Total)." GB"
|fillnull value="-"
|eval DIMM_GB=if(DIMM_GB="-","-",DIMM_GB." GB")
|transaction mvlist=true host
|rename Total as Memory
| eval Date=strftime(_time, "%Y-%m-%d")
| eval Time=strftime(_time, "%I:%M:%S:%p")]
|join type=left host [search index=* sourcetype=WMI:ProcessorInfo earliest=-24h host="*"
|eval hostsCPU=host.DeviceID
|dedup hostsCPU
|rename NumberOfCores as Cores
|rename MaxClockSpeed as ClockSpeed
|rename Name as Processor]
|join type=left host [search index=* sourcetype=WMI:Version earliest=-24h host="*"
|dedup host
|eval LastBootUpTime=replace(LastBootUpTime, "^(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})(.*)", "\2-\3-\1 \4:\5:\6")
|rename Caption as OperatingSystem
|rename LastBootUpTime as LastReboot]
|join type=left host [search index=* sourcetype="WMI:ComputerSystem" earliest=-24h host="*"]
|search host=$hosttok$
|table
HostName
LoginName
PrimaryOwner
OU
IPAddress
Model
SerialNum
ClockSpeed
Cores
Processor
Memory
ADAPTERSTRING
MEMORY
OperatingSystem
Version
LastReboot
LastUpdInvSvr
VALastScanDate</query>
<earliest>@d</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">1</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</option>
<option name="percentagesRow">false</option>
<option name="refresh.display">progressbar</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">false</option>
<drilldown>
<link target="_blank">https://$click.value$:4343/index.html</link>
</drilldown>
</table>
</panel>
</row>
<row>
<panel>
<title>LANDesk Events</title>
<table>
<search>
<query>index=ivanti_EPM Changed_by="**" Type_of_Change="*" Message="***"
| eval Date=strftime(_time, "%Y-%m-%d")
| eval Time=strftime(_time, "%I:%M:%S:%p")
|rename Changed_on_machine as host
|search host=$hosttok$
| table
Date
Time
Changed_by
host
Type_of_Change
Item_name
|sort Date, Time</query>
<earliest>$TimerangePicker.earliest$</earliest>
<latest>$TimerangePicker.latest$</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">20</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">none</option>
<option name="percentagesRow">false</option>
<option name="refresh.display">progressbar</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
</table>
</panel>
</row>
<row>
<panel>
<title>CPU Usage</title>
<chart>
<search>
<query>index="perfmon" host=$hosttok$ object=Processor counter="% Processor Time" instance=_Total
| timechart bins=1000 minspan=1m avg(Value) as "% Processor Time _Total Avg per Min" by Host</query>
<earliest>$TimerangePicker.earliest$</earliest>
<latest>$TimerangePicker.latest$</latest>
<refresh>2m</refresh>
<refreshType>delay</refreshType>
</search>
<option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
<option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
<option name="charting.axisTitleX.visibility">visible</option>
<option name="charting.axisTitleY.visibility">collapsed</option>
<option name="charting.axisTitleY2.visibility">visible</option>
<option name="charting.axisX.scale">linear</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.axisY2.enabled">0</option>
<option name="charting.axisY2.scale">inherit</option>
<option name="charting.chart">line</option>
<option name="charting.chart.bubbleMaximumSize">50</option>
<option name="charting.chart.bubbleMinimumSize">10</option>
<option name="charting.chart.bubbleSizeBy">area</option>
<option name="charting.chart.nullValueMode">gaps</option>
<option name="charting.chart.showDataLabels">none</option>
<option name="charting.chart.sliceCollapsingThreshold">0.01</option>
<option name="charting.chart.stackMode">default</option>
<option name="charting.chart.style">shiny</option>
<option name="charting.drilldown">all</option>
<option name="charting.layout.splitSeries">0</option>
<option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
<option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
<option name="charting.legend.placement">right</option>
<option name="height">249</option>
<option name="refresh.display">progressbar</option>
</chart>
</panel>
<panel>
<title>Application Alerts</title>
<chart>
<search>
<query>index=prd_win_wrk_app host=* (EventCode=1002 OR EventCode=1000)
|eval Date=strftime(_time, "%Y-%m-%d")
|eval Time=strftime(_time, "%I:%M:%p")
|eval Faulting_application_path=lower(Faulting_application_path)
|replace "c:\users\*\phprod.exe" with "PHPROD" in Faulting_application_path
|replace "c:\program files*\outlook.exe" with "Outlook" in Faulting_application_path
|replace "c:\program files*\winword.exe" with "Word" in Faulting_application_path
|replace "c:\program files*\excel.exe" with "Excel" in Faulting_application_path
|replace "c:\program files*\symphony.exe" with "Symphony" in Faulting_application_path
|replace "c:\firmapps*\dashboard.exe" with "Ops Dashboard" in Faulting_application_path
|replace "c:\program files (x86)*\ssms.exe" with "SQL Management Studio" in Faulting_application_path
|replace "c:\program files*\ciscojabber.exe" with "Jabber" in Faulting_application_path
|replace "c:\blp*" with "Bloomberg" in Faulting_application_path
|replace "c:\*neovest.exe" with "Neovest" in Faulting_application_path
|replace "c:\users*\ciscocollabhost.exe" with "Cisco Spark" in Faulting_application_path
|fillnull NA
|eval Application_Path=lower(Application_Path)
|replace "c:\users\*\phprod.exe" with "PHPROD" in Application_Path
|replace "c:\program files*\outlook.exe" with "Outlook" in Application_Path
|replace "c:\program files*\winword.exe" with "Word" in Application_Path
|replace "c:\program files*\excel.exe" with "Excel" in Application_Path
|replace "c:\program files*\symphony.exe" with "Symphony" in Application_Path
|replace "c:\firmapps*\dashboard.exe" with "Ops Dashboard" in Application_Path
|replace "c:\program files (x86)*\ssms.exe" with "SQL Management Studio" in Application_Path
|replace "c:\program files*\ciscojabber.exe" with "Jabber" in Application_Path
|replace "c:\blp*" with "Bloomberg" in Application_Path
|replace "c:\*neovest.exe" with "Neovest" in Application_Path
|replace "c:\users*\ciscocollabhost.exe" with "Cisco Spark" in Application_Path
|fillnull NA
|search Application_Path=Excel OR Faulting_application_path=Excel
OR Application_Path=Outlook OR Faulting_application_path=Outlook
OR Application_Path=Word OR Faulting_application_path=Word
OR Application_Path=Bloomberg OR Faulting_application_path=Bloomberg
OR Application_Path="Ops Dashboard" OR Faulting_application_path="Ops Dashboard"
OR Application_Path=Neovest OR Faulting_application_path=Neovest
|sort _time, limit=0
| where isnotnull(user)
| fillnull NA
| fields *
| stats count(Faulting_application_path) AS count BY Faulting_application_path
| sort Faulting_application_path</query>
<earliest>@d</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
<refresh>5m</refresh>
<refreshType>delay</refreshType>
</search>
<option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
<option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
<option name="charting.axisTitleX.visibility">collapsed</option>
<option name="charting.axisTitleY.text">Count</option>
<option name="charting.axisTitleY.visibility">visible</option>
<option name="charting.axisTitleY2.visibility">visible</option>
<option name="charting.axisX.abbreviation">none</option>
<option name="charting.axisX.scale">linear</option>
<option name="charting.axisY.abbreviation">none</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.axisY2.abbreviation">none</option>
<option name="charting.axisY2.enabled">0</option>
<option name="charting.axisY2.scale">inherit</option>
<option name="charting.chart">pie</option>
<option name="charting.chart.bubbleMaximumSize">50</option>
<option name="charting.chart.bubbleMinimumSize">10</option>
<option name="charting.chart.bubbleSizeBy">area</option>
<option name="charting.chart.nullValueMode">gaps</option>
<option name="charting.chart.showDataLabels">all</option>
<option name="charting.chart.sliceCollapsingThreshold">0.01</option>
<option name="charting.chart.stackMode">stacked</option>
<option name="charting.chart.style">shiny</option>
<option name="charting.drilldown">all</option>
<option name="charting.layout.splitSeries">1</option>
<option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
<option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
<option name="charting.legend.mode">standard</option>
<option name="charting.legend.placement">right</option>
<option name="charting.lineWidth">2</option>
<option name="refresh.display">progressbar</option>
<option name="trellis.enabled">0</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
<drilldown>
<link target="_blank">https://support.office.com/en-us/article/fixes-or-workarounds-for-recent-issues-in-outlook-for-windows-ecf61305-f84f-4e13-bb73-95a214ac1230?ui=en-US&rs=en-US&ad=US</link>
</drilldown>
</chart>
</panel>
<panel>
<title>Memory Usage</title>
<chart>
<search>
<query>index="perfmon" host=$hosttok$ object=Memory counter="Available MBytes" instance=*| timechart bins=1000 minspan=1m avg(Value) as "Available MBytes Avg per Min" By Host</query>
<earliest>$TimerangePicker.earliest$</earliest>
<latest>$TimerangePicker.latest$</latest>
<refresh>2m</refresh>
<refreshType>delay</refreshType>
</search>
<option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
<option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
<option name="charting.axisTitleX.visibility">visible</option>
<option name="charting.axisTitleY.visibility">visible</option>
<option name="charting.axisTitleY2.visibility">visible</option>
<option name="charting.axisX.scale">linear</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.axisY2.enabled">0</option>
<option name="charting.axisY2.scale">inherit</option>
<option name="charting.chart">line</option>
<option name="charting.chart.bubbleMaximumSize">50</option>
<option name="charting.chart.bubbleMinimumSize">10</option>
<option name="charting.chart.bubbleSizeBy">area</option>
<option name="charting.chart.nullValueMode">connect</option>
<option name="charting.chart.showDataLabels">none</option>
<option name="charting.chart.sliceCollapsingThreshold">0.01</option>
<option name="charting.chart.stackMode">default</option>
<option name="charting.chart.style">shiny</option>
<option name="charting.drilldown">all</option>
<option name="charting.layout.splitSeries">0</option>
<option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
<option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
<option name="charting.legend.placement">right</option>
<option name="refresh.display">progressbar</option>
</chart>
</panel>
</row>
<row>
<panel>
<title>User Login Information - Last 7 days</title>
<table>
<search>
<query>sourcetype=WinEventLog:Security (EventCode=4624 (Logon_Type=2 OR Logon_Type=10 OR Logon_Type=7)) OR EventCode=4801 OR EventCode=4803 AND dest_nt_domain=SACCAP host=$hosttok$
| eval Date=strftime(_time, "%Y-%m-%d")
| eval Time=strftime(_time, "%I:%M:%S:%p")
| sort _time limit=0
| dedup Date, user
| lookup AD_Account_Info.csv LogonName as Account_Name OUTPUT Name, Department, Office, FirstName, LastName
| replace "*.saccap.int" with "*" in ComputerName
| replace "*.saccap.int" with "*" in dest_nt_host
| eval Source=dest_nt_domain
| eval Country=Office
| table
Date
Time
ComputerName
user
FirstName
LastName
Department
Office
| sort -Date limit=0</query>
<earliest>-7d@h</earliest>
Thank you!