LPS Tutorial: Batched Queries, Folders and Automation

Article
02/17/2015

One of the coolest features of LPS is batch queries and automating them. What could be cooler than arriving in the morning and seeing comprehensive reports of your CAS array, event logs etc. just waiting for you? This tutorial is an in-depth step-by-step tutorial on how this works and how to accomplish.

First, we need to cover how to create the list of files/folders and queries and save them to disk for later consumption. Even if you aren’t automating, if you have sets of queries and sets of logs that you query often, this will allow you to browse and select them only once saving lots of time.

Saving folders/logs to query as XML

- Open file manager.

- Choose "Add Files" or "Add Folder"

- Browse to the network path or type the UNC name of the machine to access for example: \\ComputerName\D$

- Browse the remaining UNC path to the logs and select the files or folder (if using Add Folder then just double click any log file in that folder).

- While still in File Manager choose File > Save and save as somefile.fld where somefile is a name that makes sense to you. This will save the folder\file list as an XML file.

Here is an example of what a FLD file looks like which is really just XML. I'll also explain its format so you can edit the file manually if you ever need to:

<?xml version="1.0" encoding="utf-8"?>

<Filename>\\bigmono\Dumps\MASPing\u_ex140404.log</Filename>

</LPLogFile>

<Filename>E:\Dumps\Corey2013\W3SVC1\W3SVC2\*.log</Filename>

</LPLogFile>

<Filename>E:\Dumps\Corey2013\W3SVC1\W3SVC2\u_ex140112.log</Filename>

</LPLogFile>

</ArrayOfLPLogFile>

The 1st, 2nd and last lines must always be present:

<?xml version="1.0" encoding="utf-8"?>

</ArrayOfLPLogFile>

The data that is in between is the list of files or folders. Here is a single file:

<Filename>\\bigmono\Dumps\MASPing\u_ex140404.log</Filename>

</LPLogFile>

Here is a folder:

<Filename>\\bigmono\Dumps\MASPing\*.log</Filename>

</LPLogFile>

Notice the only difference between a file and a folder is the wildcard. Also notice this little chunk of XML represents a log file. “isChecked” simply tells LPS whether to check the box in its list to make it active.

To recap, using File > Save or File > Load in LPS' file manager takes care of this for you. However, you can edit these manually if needed. The only caveat is XML is very strictly formatted and any mistakes may cause errors or worse.

Saving batches of queries as XML

- Create the queries you want to use and save them to the library, or choose the queries you want to use that are already in the library.

- Add them to a batch job:

-- Right-click one or more queries and choose "Add to batch"

-- To add multiple queries at once, CTRL+Click to highlight multiples then right-click > "Add to Batch".

- Once you have added all the queries you want to automate open the batch window (7th button from the left which is teal colored) or ALT+B.

- To run all queries immediately, click "Execute".

- To save these for later, choose File > Save Batch and give it a name that makes sense to you such as LPSBatch.XML.

- If you plan on using these for automation:

-- Save the batch file in the same directory as the FLD folder list file.

-- Remove the queries from the batch window if you wish by clicking Remove All.

A batch of queries is somewhat similar to a folder batch. In other words it uses XML too but the format is completely different. I don't generally recommend editing batch XML files due to that complexity and possible escape sequences contained within the query. However, here is what a batch file with a single query looks like:

<?xml version="1.0" encoding="utf-8"?>

<QueryName>ActiveSync: Count all errors</QueryName>

<QueryDescription>ActiveSync: Count all errors</QueryDescription>

/* Query to count number of errors by error message */

/* Note: For Exchange 2013 these must be executed against the MBX server logs! */

Select Count(*) AS Hits,

TO_STRING(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Error:'), 0, '_')) As Error

FROM '[LOGFILEPATH]'

WHERE cs-uri-stem LIKE '%ActiveSync%'

AND cs-uri-query LIKE '%Error:%'

GROUP BY Error

ORDER BY Hits DESC

</QueryData>

<IsFavorite>false</IsFavorite>

</LPBatchQuery>

</ArrayOfLPBatchQuery>

Automating LPS with what we have learned

Requirements:

1. An existing batch file.

2. An existing folder file.

Command line syntax:

LPS.EXE BatchFile.XML FolderFile.FLD [silent]

- The batch file and folder file can be in any order. In other words it doesn't matter which is listed first.

- The silent switch is optional but needs to be the last argument in the command line if used.

- The silent switch will auto close LPS when the batch completes. For this reason, you probably want all batch queries to export data to CSV instead of the GUI so you don’t lose the results...

Examples:

Run LPS load folders.fld and batch.xml, run all queries then exit LPS:

lps.exe folders.fld batch.xml -silent

Same as above but do not exit LPS:

lps.exe folders.fld batch.xml

Modifying queries to export to CSV automatically

Many queries already send results to CSV but some do not. Any query that doesn't not already send to CSV can be easily **modified to do so by adding the INTO statement. Here is a query that does not export to CSV using a before after example.

BEFORE:

Select Count(*) AS Hits,

TO_STRING(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Error:'), 0, '_')) As Error

FROM '[LOGFILEPATH]'

WHERE cs-uri-stem LIKE '%ActiveSync%'

AND cs-uri-query LIKE '%Error:%'

GROUP BY Error

ORDER BY Hits DESC

AFTER:

Select Count(*) AS Hits,

TO_STRING(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Error:'), 0, '_')) As Error

INTO '[OUTFILEPATH]\Output.CSV'

FROM '[LOGFILEPATH]'

WHERE cs-uri-stem LIKE '%ActiveSync%'

AND cs-uri-query LIKE '%Error:%'

GROUP BY Error

ORDER BY Hits DESC

- Notice the added line just above the FROM statement. This line with the INTO statement must always be just above and before the FROM line.

- Output.CSV can be any name you desire. They can also be the same name across multiple queries because LPS always appends the exact time to the end of the file name.

- The resolution of the timestamp appended to the CSV filename is in milliseconds.

To make adding this line easier and prevent mistakes you can use the F8 key. Just type INTO + a single space then click F8 and you'll get the following:

INTO '[OUTFILEPATH]\Output.CSV'

- OUTFILEPATH is set in Options > Preferences > Default Output Path.

- This is easily accessed by clicking the OUT button (2nd button from the right and is green in color).

- You do not necessarily need to use OUTFILEPATH. You can hardcode the path in the queries but isn't generally recommended for consistencies’ sake:

INTO 'D:\LPSREPORTS\ActiveSyncErrors.CSV'

Summary

We can save both log file lists and query lists for later use.

We can automate LPS to use those lists:

LPS.EXE batchfile.xml folderfile.xml

We can tell LPS to auto close by using the silent switch

LPS.EXE batchfile.xml folderfile.xml silent

We can use the Window's Scheduled Tasks to run these a specified times. For example once all set up, we could have specific ActiveSync reports from all servers in a CAS array waiting for us when we arrive the next day.

** If you want to edit an existing query in the library, they are read-only by default. You can make them writeable by clicking the little orange lock just above the query window:

Comments

Anonymous
February 17, 2015
thanks
Anonymous
March 01, 2015
Thanks for sharing, it's an awesome tool! Keep up the great work!
Anonymous
March 11, 2015
I'm looking at IIS logs for a sharepoint foundation server and looking at user adoption rates. I'd like to run a query in LPS that gives me a column of users, a column of total Count and then a separate column for each log file. These last columns should show how many hits that user had in that day's log. I've modified a basic query to give me the first two columns, but don't know how to get those last two. Clicking on Documentation in Help brought me to your page, so I'm hoping you can give me some insight. I'm loving what I can do to look at these logs and appreciate the GUI.
Anonymous
March 20, 2015
Is there a way when sending the output to CVS file that we can append a date time stamp? ex: filename_20150320_015700.cvs
Anonymous
August 20, 2015
Any way to parse a SharePoint log file? What would you use for log type and header?
Anonymous
October 05, 2015
The comment has been removed

Share via

LPS Tutorial: Batched Queries, Folders and Automation

Comments

Additional resources